Indexing Table Variables in SQL Server
Table variables are a very nice handy tool to store the result set. The major advantage of table variables is that they will log very minimal information in the temp database. Initially, I thought the table variable would reside only in the memory and won't interact with any of the databases. But it's not the case. The data will reside in the temp database but will be logged very minimally. The 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