可以在表值函数中创建索引吗

Can we create an index on a column in a table valued functions table in SQL Server 2008?

我们可以在SQL Server 2008中的表值函数表中的列上创建索引吗?

My function is getting slow results. When I look into the execution plan, it was under the table scan, hence I need to create index on function table column so that put where clause on that.

我的函数得到的结果很慢。当我查看执行计划时，它在表扫描下，因此我需要在函数表列上创建索引，以便在其上放置where子句。

Any help would be highly appreciated.

如有任何帮助，我们将不胜感激。

Thanks in advance

谢谢提前

1 个解决方案

#1

If the table valued function is of the inline variety you would create the index on the underlying table columns.

如果表值函数是内联类型，那么您将在底层表列上创建索引。

If it is a multi statement TVF in SQL Server 2008 (as tagged) you can only create the indexes associated with primary key or unique constraints.

如果它是SQL Server 2008中的一个多语句TVF，那么您只能创建与主键或唯一约束相关联的索引。

In SQL Server 2014+ it is possible to declare inline indexes not associated with any constraint.

在SQL Server 2014+中，可以声明与任何约束无关的内联索引。

Example

例子

CREATE FUNCTION F()
RETURNS @X TABLE
(
A INT PRIMARY KEY /*<-- Implicit clustered index*/
)
AS
BEGIN
INSERT INTO @X 
    VALUES(1),(2)
RETURN;
END

GO

SELECT *
FROM F()
WHERE A = 12

The above materializes the entire resultset up front into a table variable first, and creates an implicit index on it.

上面的内容将整个结果预先设置为一个表变量，并在其上创建一个隐式索引。

Generally inline TVFs are preferred to multi statement ones.

通常内联的TVFs比多语句的要好。