Can We Create Index in Table Valued Function

Question

Can we create an index on a column in a table valued functions table in 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.

Any help would be highly appreciated.

Thanks in advance

Answer 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.

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

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.