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.