Derived table with an index

Question

Please see the TSQL below:

DECLARE @TestTable table (reference int identity, 
                          TestField varchar(10), 
                          primary key (reference))
INSERT INTO @TestTable VALUES ('Ian')

select * from @TestTable as TestTable
INNER JOIN LiveTable on LiveTable.Reference=TestTable.Reference

Is it possible to create an index on @Test.TestField? The following webpage suggests it is not. However, I read on another webpage that it is possible.

I know I could create a physical table instead (for @TestTable). However, I want to see if I can do this with a derived table first.

Answer 1

You can create an index on a table variable as described in the top voted answer on this question:

SQL Server : Creating an index on a table variable

Sample syntax from that post:

DECLARE @TEMPTABLE TABLE (
  [ID]   [INT] NOT NULL PRIMARY KEY,
  [Name] [NVARCHAR] (255) COLLATE DATABASE_DEFAULT NULL,
  UNIQUE NONCLUSTERED ([Name], [ID]) 
  ) 

Alternately, you may want to consider using a temp table, which will persist during the scope of the current operation, i.e. during execution of a stored procedure exactly like table variables. Temp tables will be structured and optimized just like regular tables, but they will be stored in tempDb, therefore they can be indexed in the same way as regular table.

Temp tables will generally offer better performance than table variables, but it's worth testing with your dataset.

More in depth details can be found here:

When should I use a table variable vs temporary table in sql server?

You can see a sample of creating a temp table with an index from:

SQL Server Planet - Create Index on Temp Table

One of the most valuable assets of a temp table (#temp) is the ability to add either a clustered or non clustered index. Additionally, #temp tables allow for the auto-generated statistics to be created against them. This can help the optimizer when determining cardinality. Below is an example of creating both a clustered and non-clustered index on a temp table.

Sample code from site:

CREATE TABLE #Users
(
    ID          INT IDENTITY(1,1),
    UserID      INT,
    UserName    VARCHAR(50)
)

INSERT INTO #Users
(
    UserID,
    UserName
)   
SELECT 
     UserID     = u.UserID
    ,UserName   = u.UserName
FROM dbo.Users u

CREATE CLUSTERED INDEX IDX_C_Users_UserID ON #Users(UserID)

CREATE INDEX IDX_Users_UserName ON #Users(UserName)