CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Neeraj
Neeraj

Reputation: 4489

Can We Create Index in Table Valued Function

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

Upvotes: 21

Views: 29949

Answers (2)

Matthew Sidorick
Matthew Sidorick

Reputation: 71

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

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453028

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.

Upvotes: 27

Related Questions