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