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

Question

Using SQL Server 2008 R2

Consider a table variable declared , for example:

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

Like CREATE NONCLUSTERED INDEX any ON @t name including (Con1 ASC, Col2 ASC)

The index should not be limited to unique values.

For some reason, I can't figure it 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 Col1, Col2 are Col1, Col2 be unique, then remove PK from the list of columns.

Although it appears at face value, as if it added an additional column in ( PK ), the index structure will be the same as creating a unique index only for Col1, Col2 in the #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-standard non-clustered index, SQL Server always adds the CI key to the NCI key anyway. It just shows it clearly.

See Kalen Delaney for more on nonclustered index keys.

Answer 2

You can not. declare ( <table_type_definition> ):

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

Please note: no instructions.

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

Answer 3

You cannot index table variables.

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

This is one of the main disadvantages of table variables - two others:

• table variables are not involved in the transaction, which may be good or bad depending on your situation.

• table variables are always taken into account by the query optimizer to have exactly one row in them, which is great if you have several rows, but if you have so many rows that you feel the need for index - then this point will probably also be a problem for query optimization

Answer 4

You cannot create a non-clustered, non-unique index for a table variable. It supports only unique indexes. See here .

Instead, you should look for a local tempo table. Either way, better suited for large data sets.