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

Answer 1

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