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