Can I create an index on User-defined Table variables?

Question

Just wanted to check, if we will be able to create indexes on User-defined Table variables. I know that we can create PK on an UDT. Does it imply that PK creates an (clustered) index internally? If an index is possible on a column on UDT, where does the indexed data get stored?

• Damien_The_Unbeliever: Do you mean for User Defined Table types, or for Table variables? (It's not going to change the answer much, it's just that the right MSDN pages to link to are different :-|)

Answer 1

To define an index on a table variable use a primary key or unique constraint. You can nominate one as clustered.

• If you need an index on a non-unique field, simply add the unique key to the end of the index column list, to make it unique.

• If the table variable has not got a unique field, add a dummy unique field using an identity column.

Something like this:

declare @t table (
 dummy identity primary key nonclustered,
 val1 nvarchar(50),
 val2 nvarchar(50),
 unique clustered (val1, dummy)
) 

Now you have a table variable with a clustered index on non-unique field val1.

Answer 2

With table variables, you can define primary key and unique constraints, but you are unable to define any clustering behaviour. The indexes for these are stored alongside the actual data in the table variable - hopefully in memory within tempdb, but if necessary, spilled to disk, if memory pressure is high.

You're unable to define arbitrary indexes on such tables.

Answer 3

You can however define whatever indexes you want on temp tables.