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
@VENKAT_VAR TABLE (ID
-- 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
INT PRIMARY KEY CLUSTERED )
-- We can query on the sys.indexes table to get the index details created on the
SELECT * FROM TEMPDB.SYS.indexes
A INNER JOIN
ON A.object_id=B.object_id ORDER BY
Venkatesan Prabu .J