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

Question

Using SQL Server 2008 R2

Consider a declared table variable like:

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

How do I CREATE NONCLUSTERED INDEX of any name ON @t including (Con1 ASC, Col2 ASC)

The index should not be limited to unique values.

For some reason I do not manage to figure this out...

You **cannot** index table variables. If you need indexes - use a temporary table (`CREATE TABLE #T` or `CREATE TABLE ##T`) — marc_s, Jul 05 '12 at 06:42

Martin Smith · Accepted Answer · 2022-11-29T17:00:05.473

On recent versions you can create inline indexes. As below

DECLARE @t TABLE (
PK int IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
Col1 int, 
Col2 int, 
index ix (Col1 ASC, Col2 ASC)
)

This is tagged SQL Server 2008 however. There 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

That however requires the two columns to be **unique** (as a pair) - that's not always the case! And the OP explicitly says it should **not** enforce uniqueness! — marc_s, Jul 05 '12 at 06:46
@marc_s - 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. — Martin Smith, Jul 05 '12 at 06:47

score 5 · Answer 2 · answered Jul 05 '12 at 06:45

You cannot. declare (<table_type_definition>):

Defines the table data type. The table declaration includes column definitions, names, data types, and constraints. The only constraint types allowed are PRIMARY KEY, UNIQUE, NULL, and CHECK.

Note, no mention of indexes.

If you want indexes, create a temporary table (CREATE TABLE #t (...).

score 2 · Answer 3 · answered Jul 05 '12 at 06:44

You cannot index table variables.

If you need indexes - use a temporary table (CREATE TABLE #T or CREATE TABLE ##T)

That's one of the main drawbacks of table variables - two others being:

table variables do not take part in transaction, which can be a good or a bad thing depending on your situation
table variables are always considered by the query optimizer to have exactly one row in them, which is fine as long as you have a handful of rows - but if you have that many rows that you feel the need for an index - then probably this point will also be an issue for query optimization

Table variables can have primary keys and unique indexes at declaration time, however. Also, using OPTION(RECOMPILE) on statements that use a table variable (in, say, a join) allows the optimizer to get a better estimate of rows. — DWright, Apr 15 '16 at 16:22

score 1 · Answer 4 · answered Jul 05 '12 at 06:43

1

You can't create a non-clustered, non-unique index on a table variable. It supports only unique indexes. See here.

You should look into a local temp table instead. Performs better for large datasets anyway.

answered Jul 05 '12 at 06:43

Marcel N.

13,726
5
47
72

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

4 Answers4

Linked