Creating an index on a table variable

user_68654 0
Viewed 388739

Can you create an index on a table variable in SQL Server 2000?

i.e. 

DECLARE @TEMPTABLE TABLE (
     [ID] [int] NOT NULL PRIMARY KEY
    ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL 
)

Can I create an index on Name?

table-variable indexing tsql sql-server sql
3 Answers

If Table variable has large data, then instead of table variable(@table) create temp table (#table).table variable doesn't allow to create index after insert.

 CREATE TABLE #Table(C1 int,       
  C2 NVarchar(100) , C3 varchar(100)
  UNIQUE CLUSTERED (c1) 
 );

  1. Create table with unique clustered index

  2. Insert data into Temp "#Table" table

  3. Create non clustered indexes.

     CREATE NONCLUSTERED INDEX IX1  ON #Table (C2,C3);
user_17846120
Related