sys.indexes and table valued functions

Today, while lending a hand in the creation of a new operational reindexing job, we discovered the existence of primary keys on table valued functions. I had written a little script to gather all the clustered and non-clustered indexes with their necessary attributes from sys.indexes. I assumed that indexes are only present on physical tables, so I didn't made any checks on the type of object it was associated with. In our first test-run, that turned out to be a mistake.

In the definition of the return value of a table valued function, you can define the primary key for that table and although it isn't an actual table - just a table variable created at run-time - metadata about the index is kept similar to any user table.

USE tempdb
GO
CREATE FUNCTION dbo.udf_TableValuedFunction ()
RETURNS @Table TABLE ( id int PRIMARY KEY , comment varchar(200) )
AS
BEGIN
INSERT @Table VALUES ( 1, 'comment 1' )
INSERT @Table VALUES ( 2, 'comment 2' )
RETURN
END
GO
SELECT *
FROM sys.objects o
JOIN sys.indexes i ON i.object_id = o.object_id
WHERE o.name = 'udf_TableValuedFunction'
GO
DROP FUNCTION dbo.udf_TableValuedFunction
GO