If you need to create an index in order to improve performance of the queries running against table variable (@temptable) In MS SQL Server you can implicitly create a clustered index on a table variable by defining a primary key or unique constraint, however, it is generally more efficient to use a temporary table. Anyway let`s move on with an option of creating index on a table variable. For example, you need create simple table variable:
DECLARE @temptable TABLE (
ID int NOT NULL,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
SSN nvarchar(50) NOT NULL,
CreatedDate datetime )
Now you can select rows from temporary table using the command:
SELECT * FROM @temptable WHERE ID > 0
If you turn on the option ‘Include Actual Execution Plan’, you will see next execution plan:
To create a clustered index on a table variable (@temptable) you should define a primary key on the ID column as shown below:
DECLARE @temptable TABLE (
ID int NOT NULL PRIMARY KEY,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
SSN nvarchar(50) NOT NULL,
CreatedDate datetime)
Then you can check a new execution plan:
Now your queries ran against table variable will be faster and more efficient due using indexes.
Please don`t hesitate to ask question and put comments below.