CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012sql-server-2014
Rameshwar Pawale
Rameshwar Pawale

Reputation: 682

create index clause on table variable

I need to create an Index on two columns (within a table variable) which do not form unique key.

Table structure is shown below - 

DECLARE @Sample TABLE (                           
    [AssetSk] [int] NOT NULL,                
    [DateSk] [int] NOT NULL,             
    [Count] [numeric](38, 2) NULL         
    )

I am trying to add Index as shown below - 

INDEX AD1 CLUSTERED([AssetSk],[DateSk])

However it gives me the following error while running it on SQL Server 2012
" Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax."

However, this runs perfectly on SQL Server 2014 . Is there any way that I could run it on SQL Server 2012 .

Upvotes: 1

Views: 11094

Answers (2)

Andrey Korneyev
Andrey Korneyev

Reputation: 26876

You can't build index other than unique key at table variable using SQL Server version prior to 2014.

However, you can do the trick: add one more colummn with autoincremented value and create unique index including columns you need and this new one.

DECLARE @Sample TABLE (
    [ID] bigint identity(1, 1),                           
    [AssetSk] [int] NOT NULL,                
    [DateSk] [int] NOT NULL,             
    [Count] [numeric](38, 2) NULL,
    UNIQUE NONCLUSTERED ([AssetSk],[DateSk], ID)       
    )

Update: In fact, creation of such an index on table variable can be useless. Normally SQL Server estimates that a table variable has a single row, thus it will not use this index with relatively high probability.

Upvotes: 4

Mihail Stancescu
Mihail Stancescu

Reputation: 4138

As far as I know in SQL Server 2012 and below you can not add indexes to table variables. To add an index you must declare the table like this:

CREATE TABLE #Sample (                           
[AssetSk] [int] NOT NULL,                
[DateSk] [int] NOT NULL,             
[Count] [numeric](38, 2) NULL         
)

And after you can create the index you need like this

CREATE CLUSTERED INDEX IX_MyIndex
 ON #Sample ([AssetSk],[DateSk])

Of course, after you're done with the table in four function you can call 

DROP TABLE #Sample

Upvotes: 1

Related Questions