Indexing Table Variables in SQL Server

Table variables is a very nice handy tool to store the result set. The major advantage of table variable is that, it will log a very minimal information in the temp database. Initially, I had a thoughts that table variable will reside only in the memory and it won't interact with any of the database. But, it's not the case. The data will reside in the temp database but logged very minimally. Index created on the table variables will reside in the tempdb.

Let's see, how to create indexes on the table variable.

-- Temp table created  
DECLARE @VENKAT_VAR TABLE (ID INT)
 
-- Trying to create an index on the column. It's throwing error.
CREATE CLUSTERED INDEX IDX_VENKAT_VAR ON @VENKAT_VAR(ID)
 
 
-- Trying to alter the table variable. It's throwing error.
ALTER TABLE @VENKAT_VAR ADD CONSTRAINT CON_VENKAT_VAR PRIMARY KEY (ID)
 
 
-- Here is an option to create the clustered index or primary key on the temp variable.Index on the table variable can be created  during table variable creation itself.
 
 DECLARE @VENKAT_VAR TABLE
            (ID INT PRIMARY KEY CLUSTERED )
 
-- We can query on the sys.indexes table to get the index details created on the table variable.
           
SELECT * FROM TEMPDB.SYS.indexes A INNER JOIN TEMPDB.SYS.tables B
            ON A.object_id=B.object_id ORDER BY create_date DESC

Cheers,

Venkatesan Prabu .J