SQL Server : Creating an index on a table variable

Question

Can you create a index on a table variable in SQL Server 2000?

i.e.

DECLARE @TEMPTABLE TABLE (
[ID] [int] NOT NULL PRIMARY KEY
,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL
)

Can I create a index on Name?

Answer 1

The answer is no. You can only add PRIMARY KEY (as you did) and UNIQUE constraints in the definition of table variable.

Here you can find more information:

http://support.microsoft.com/default.aspx/kb/305977

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html