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?

Solution 1

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.

Solution 2

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.

Comments

[u07ch]

There is a cost in creating both kinds of temp tables; and if you have so much data in that that you need an index it might be time to look at using a real table; that you set up to be transaction safe; filter by spid or user id and then clear it down at the end. Real tables v temp tables both have their ups and downs but if performance is an issue; try it with a real table too.

[bielawski]

A temp table 'IS' a real table, it just goes away when you are done. The real difference (other than it will go away automatically) is that it's in TempDB. This is actually huge when it comes to indexes and constraints because you could end up with name clashes, not only with other executions of your code but with code executing in other databases in your instance.

[Martin Smith]

@bielawski this is a table variable not a temp table. Table variables don’t allow explicitly named constraints, the system generated names are guarantee to be unique. They do allow named indexes from 2014 but that isn’t a problem as indexes only need to be uniquely named within an object not across objects.

[bielawski]

My point was 2 fold. 1) Other than using a variable to avoid transaction entanglement there is no material difference between a temp table and table variable. In V-2000 however there is no syntax for adding constraints, indexes... to a variable. 2) Given one can use a temp table instead, named table appendages like indexes WILL clash with simultaneously executing copies of the same SP if a static name is used! The mechanism below was developed explicitly because I traced SP failures to named indexes clashing during these exact circumstances. They MUST be unique.

[Martin Smith]

@bielawski - No index names don't need to be unique between objects - only constraint names do. this is trivial to test. Just execute CREATE TABLE #T1(X INT); CREATE TABLE #T2(X INT); CREATE INDEX IX ON #T1(X); CREATE INDEX IX ON #T2(X);

[Martin Smith]

And you can download 2000 BOL from microsoft.com/en-us/download/details.aspx?id=51958 - The CREATE INDEX topic states i.stack.imgur.com/xwhpc.png

[Martin Smith]

There is no problem with named indexes - indexes only have to be uniquely named within a table. The problem is with named constraints and the best solution generally is not to name them in temp tables - named constraints prevent temp table object caching.

[bielawski]

That must be true only for certain versions (if it's true for any version). I had to come up with this workaround specifically because I traced sp failures to the clash of named indexes during simultaneous executions.

[Andre Figueiredo]

Note: 2000-2012 solution works only if text column <= 900 bytes. ie. varchar(900), nvarchar(450)

[Martin Smith]

@AndreFigueiredo yep, that's the maximum size for an index key on permanent tables too in those versions.

[Jaxidian]

I just wanted to note that the SQL 2014 answer works well in Azure. Thanks Martin!

[Elaskanator]

@bielawski Are you using 2016? I am very curious about whether named indexes on temp tables are a risk for concurrent environments.

[Dan Def]

@Elaskanator yes, they are. We found contention in the SQL meta data tables when under load, removing the name of the index resolved the problem. This was SQL 2016.

[Boopathi.Indotnet]

create index after insert statement to avoid unnecessary sorting

[kpollard]

There actually are significant performance differences when you are dealing with table variables vs temp tables within stored procedures. Temp tables are subject to similar recompile thresholds to physical tables, while table variables offer a mechanism that is not impacted by recompile thresholds. In larger procedures, this can add up to huge differences.

[Geoff]

Note that this isn't possible if the table variable is in a function

[Don F]

This indexing stuff can be too much for what's supposed to be a declarative language. It's like low-level database programming. I'll just stick to long case statements.

[CashCow]

SQLServer 2018, declare \@DateTable table( \@WDate datetime index idx nonclustered ) gives error

[Martin Smith]

@CashCow there isn't a SQL Server 2018 and I'm not running an adhoc support channel in the comments so if you have a question ask it as a new question

[CashCow]

Maybe 2017 but management studio is 2018. Making it a primary key automatically created the index though and it let me do that. So worked for my purpose. It's useful however to point out whether or not answers posted work or not as this is primarily a question-solution site for developers at work.

[Martin Smith]

@CashCow - there is nothing incorrect in the answer. you have done something wrong your end or are connecting to a database version much older than you think you are (or with some old compatibility level) but I'm not going to troubleshoot it for you in the comments. There is an obvious issue in the code you posted that column names can't start with an @ unless quoted

[CashCow]

Yes I don't think there was one of those in my column name I just typed it incorrectly here.