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

sql-server, sql-server-2008, t-sql

Solution

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

Problem

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...

Original source