Creating an Index on a Table Variable

Creating an index on a table variable

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.

Create non-clustered index on the table variable

You have to declare indexes within the table definition. Although indexes are supported (since SQL Server 2014), the CREATE INDEX syntax does not support table variables. So:

DECLARE @TBL TABLE (
    ID INT PRIMARY KEY,
    FROMDATE DATETIME,
    TODATE DATETIME,
    INDEX idx_tbl_fromdate (FROMDATE)
);

Here is a db<>fiddle.

EDIT:

If you want a temporary table with an index, use a temporary table. If your version supports this syntax, you can do:

CREATE temp_table (
    ID INT PRIMARY KEY,
    FROMDATE DATETIME,
    TODATE DATETIME,
    INDEX idx_tbl_fromdate (FROMDATE)
);

Otherwise create the index separately.

Create INDEX on Table Variable

yes, you can create index directly in table variable's script like this:

DECLARE @RDTABLE TABLE (TLCDE VARCHAR(12), TLTYP VARCHAR(2), TLEFFDAT DATETIME, TLRATFCT DECIMAL(29,6), INDEX [index_name] [NONCLUSTERED/CLUSTERED](column_name))

considers if it's the case of using temporary tables instead table variable, because depending on the case could be better to handle the indexes

this features is available only from sqlserver 2014 version

i've found this article on the internet:

http://www.brentozar.com/archive/2014/04/table-variables-good-temp-tables-sql-2014/

create index clause on table variable

You can't build index other than unique key at table variable using SQL Server version prior to 2014.

However, you can do the trick: add one more colummn with autoincremented value and create unique index including columns you need and this new one.

DECLARE @Sample TABLE (
    [ID] bigint identity(1, 1),                           
    [AssetSk] [int] NOT NULL,                
    [DateSk] [int] NOT NULL,             
    [Count] [numeric](38, 2) NULL,
    UNIQUE NONCLUSTERED ([AssetSk],[DateSk], ID)       
    )

Update: In fact, creation of such an index on table variable can be useless. Normally SQL Server estimates that a table variable has a single row, thus it will not use this index with relatively high probability.

How to create an index on a column in a table variable

Creating an index on a table variable can be done implicitly within the declaration of the table variable by defining a primary key and creating unique constraints. The primary key will represent a clustered index, while the unique constraint a non clustered index.

DECLARE @Users TABLE
(
    UserID  INT PRIMARY KEY,
    UserName varchar(50),
    UNIQUE (UserName)
)

For more info, chech THIS article.

Can I create an index on User-defined Table variables?

With table variables, you can define primary key and unique constraints, but you are unable to define
any clustering behaviour. The indexes for these are stored alongside the actual data in the table variable - hopefully in memory within tempdb, but if necessary, spilled to disk, if memory pressure is high.

You're unable to define arbitrary indexes on such tables.

How to set and index on two columns of T-SQL declared table variable?

You can create a non clustered index as follows.

DECLARE @t TABLE (
  PK   INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  Col1 INT,
  Col2 INT,
  UNIQUE (Col1, Col2, PK)) 

If the intention is that Col1, Col2 are unique themselves then remove PK from the column list.

Though it appears at face value as though this has added an additional column in (PK) the index structure will be the same as creating a non unique index on just Col1, Col2 on a #temp table.

CREATE TABLE #T  (
  PK   INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  Col1 INT,
  Col2 INT) 

/*PK added in to end of key anyway*/  
CREATE NONCLUSTERED INDEX ix ON #T(Col1, Col2)

for a non unique non clustered index SQL Server always adds the CI key to the NCI key implicitly anyway. This just shows it explicitly.

See Kalen Delaney More About Nonclustered Index Keys

SQL SERVER NON-Clustered Index on table variable?

Well, it's probably too late for Priyanka, but someone else may find this useful.

There IS a way to create a non unique index on a temp table, using a small trick: add an identity column and make it the last field of your primary key.

DECLARE @MyTable TABLE (IXField1 int, IXFiled2 int, Field3 bit, HelperIX int IDENTITY (1,1), PRIMARY KEY/UNIQUE (IXField1, IXField2, HelperIX)

---

Related Topics