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

Just wanted to check, if we will be able to create indexes on User-defined Table variab开发者_高级运维les. 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?

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.

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.

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