Creating an index on a table variable

Question

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

User · Answer

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           Create table with unique clustered index Insert data into Temp   Table  table Create non clustered indexes    CREATE NONCLUSTERED INDEX IX1  ON  Table  C2 C3

User · Answer

It should be understood that from a performance standpoint there are no differences between  temp tables and  temp tables that favor variables   They reside in the same place  tempdb  and are implemented the same way   All the differences appear in additional features   See this amazingly complete writeup  https   dba stackexchange com questions 16385 whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server 16386 16386  Although there are cases where a temp table can t be used such as in table or scalar functions  for most other cases prior to v2016  where even filtered indexes can be added to a table variable  you can simply use a  temp table     The drawback to using named indexes  or constraints  in tempdb is that the names can then clash   Not just theoretically with other procedures but often quite easily with other instances of the procedure itself which would try to put the same index on its copy of the  temp table   To avoid name clashes  something like this usually works   declare  cmd varchar 500   CREATE NONCLUSTERED INDEX  ix temp  cast newid   as varchar 40      ON  temp  NonUniqueIndexNeeded     exec   cmd     This insures the name is always unique even between simultaneous executions of the same procedure

User · Answer

The question is tagged SQL Server 2000 but for the benefit of people developing on the latest version I ll address that first   SQL Server 2014  In addition to the methods of adding constraint based indexes discussed below SQL Server 2014 also allows non unique indexes to be specified directly with inline syntax on table variable declarations    Example syntax for that is below     SQL Server 2014  compatible inline index syntax   DECLARE  T TABLE   C1 INT INDEX IX1 CLUSTERED    Single column indexes can be declared next to the column   C2 INT INDEX IX2 NONCLUSTERED         INDEX IX3 NONCLUSTERED C1 C2    Example composite index        Filtered indexes and indexes with included columns can not currently be declared with this syntax however SQL Server 2016 relaxes this a bit further  From CTP 3 1 it is now possible to declare filtered indexes for table variables  By RTM it may be the case that included columns are also allowed but the current position is that they  will likely not make it into SQL16 due to resource constraints     SQL Server 2016 allows filtered indexes   DECLARE  T TABLE   c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL   Unique ignoring nulls         SQL Server 2000 - 2012     Can I create a index on Name    Short answer  Yes   DECLARE  TEMPTABLE TABLE      ID     INT  NOT NULL PRIMARY KEY     Name   NVARCHAR   255  COLLATE DATABASE DEFAULT NULL    UNIQUE NONCLUSTERED   Name    ID           A more detailed answer is below   Traditional tables in SQL Server can either have a clustered index or are structured as heaps    Clustered indexes can either be declared as unique to disallow duplicate key values or default to non unique  If not unique then SQL Server silently adds a uniqueifier to any duplicate keys to make them unique   Non clustered indexes can also be explicitly declared as unique  Otherwise for the non unique case SQL Server adds the row locator  clustered index key or RID for a heap  to all index keys  not just duplicates  this again ensures they are unique   In SQL Server 2000 - 2012 indexes on table variables can only be created implicitly by creating a UNIQUE or PRIMARY KEY constraint  The difference between these constraint types are that the primary key must be on non nullable column s   The columns participating in a unique constraint may be nullable   though SQL Server s implementation of unique constraints in the presence of NULLs is not per that specified in the SQL Standard   Also a table can only have one primary key but multiple unique constraints   Both of these logical constraints are physically implemented with a unique index  If not explicitly specified otherwise the PRIMARY KEY will become the clustered index and unique constraints non clustered but this behavior can be overridden by specifying CLUSTERED or NONCLUSTERED explicitly with the constraint declaration  Example syntax   DECLARE  T TABLE   A INT NULL UNIQUE CLUSTERED  B INT NOT NULL PRIMARY KEY NONCLUSTERED     As a result of the above the following indexes can be implicitly created on table variables in SQL Server 2000 - 2012    ------------------------------------- -------------------------------------                Index Type                Can be created on a table variable     ------------------------------------- -------------------------------------    Unique Clustered Index                Yes                                     Nonunique Clustered Index                                                     Unique NCI on a heap                  Yes                                     Non Unique NCI on a heap                                                      Unique NCI on a clustered index       Yes                                     Non Unique NCI on a clustered index   Yes                                    ------------------------------------- -------------------------------------    The last one requires a bit of explanation  In the table variable definition at the beginning of this answer the non unique non clustered index on Name is simulated by a unique index on Name Id  recall that SQL Server would silently add the clustered index key to the non unique NCI key anyway    A non unique clustered index can also be achieved by manually adding an IDENTITY column to act as a uniqueifier   DECLARE  T TABLE   A INT NULL  B INT NULL  C INT NULL  Uniqueifier INT NOT NULL IDENTITY 1 1   UNIQUE CLUSTERED  A Uniqueifier      But this is not an accurate simulation of how a non unique clustered index would normally actually be implemented in SQL Server as this adds the  Uniqueifier  to all rows  Not just those that require it

[sql] Creating an index on a table variable

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to indexing

Examples related to table-variable