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...
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
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 (...).
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
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.
CREATE TABLE #TorCREATE TABLE ##T)