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

Normally, a table-valued function only returns rows that are needed (hence the use of a *function*). There are probably better ways to optimize your code. — Gordon Linoff, Jul 23 '14 at 12:14
The function is GETTING result slow so the table scan may be on the function's source table, not in the returned table output.So try to bring in integrity constraints and indexes on the physical table — Recursive, Jul 23 '14 at 12:51
Does your table valued function have more than 1 statement? If so it is a multi statement table valued function. These can and will be far worse than even a scalar function. Table valued functions need to inline, which means a single select statement. Post the code for your function and we can take a look. — Sean Lange, Jul 23 '14 at 13:18

score 25 · Accepted Answer · edited May 23 '17 at 12:15

25

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

enter image description here

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.

edited May 23 '17 at 12:15

Community

1
1

answered Jul 23 '14 at 23:10

Martin Smith

438,706
87
741
845

1

Thanks. This helped me reduce execution time on a query from 13 seconds to 1 second even though I had to change my inline function to a multi-statement. – Gullbyrd Mar 02 '15 at 18:08
In my case adding clustered index to my TVF only increased query time by 1 sec. – Przemyslaw Remin Feb 10 '16 at 10:25
I shaved off 3 seconds from a query that used to take10. – Denny Jacob Mar 18 '19 at 23:50

score 0 · Answer 2 · answered Dec 02 '21 at 13:22

CREATE OR ALTER FUNCTION dbo.tvfExample()
RETURNS @Example TABLE 
(
    Field1_ID INT NOT NULL,
    Field2_ID INT NOT NULL,
    Field3_ID INT NOT NULL,
    PRIMARY KEY CLUSTERED (Field1_ID ASC, Field2_ID ASC, Field3_ID ASC)
) 
AS
BEGIN

    ...

    RETURN 

END

GO

Can We Create Index in Table Valued Function

2 Answers2