create index clause on table variable

Question

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 .

http://stackoverflow.com/questions/886050/sql-server-creating-an-index-on-a-table-variable — Jaydip Jadhav, Jan 21 '16 at 08:24
Suggestion: If the table stores a resultset so large you require indexes to improve query performance, move to a temporary table. BTW, here Primary key can help you. — Deep Kalra, Jan 21 '16 at 08:30
@DeepanshuKalra Temporary tables are NOT allowed inside Table Valued function. — Rameshwar Pawale, Jan 21 '16 at 08:46

Andrey Korneyev · Accepted Answer · 2016-01-21T09:13:11.037

4

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.

edited Jan 21 '16 at 09:13

answered Jan 21 '16 at 08:23

Andrey Korneyev

26,353
15
70
71

Why non clustered? If you make it clustered it will cover the additional column of `COUNT` and be more useful. If the index is covering SQL Server should use it without any additional prompting for a seekable predicate on the index. The single row estimate can be avoided with `OPTION (RECOMPILE)` if it causes problems. – Martin Smith Jan 21 '16 at 20:33
@MartinSmith Usually I avoid creation of clustered index on columns other than autoincremented IDs since it defines order of data storage in table thus massive table re-sorting is possible on data inserts. – Andrey Korneyev Jan 22 '16 at 07:11
[The table isn't necessarily in CI order](http://goo.gl/IAB78u) It will never re-sort existing rows on an insert though they might get moved if on a page that is split . It might sort the set of data currently being inserted into CI key order but it might also do the same to maintain the non clustered index as well. (the NCI also has the exact same issue with page splits). If a sort is required the volume of data being sorted for your non covering NCI on a heap (AssetSk],[DateSk], ID, RID) will be as much as just making the clustered index covering in the first place. – Martin Smith Jan 22 '16 at 09:20

score 1 · Answer 2 · answered Jan 21 '16 at 08:24

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

Your suggestion is appreciated. However, I can't use it inside the Table Valued function. That's why I was focusing on User defined tables. — Rameshwar Pawale, Jan 21 '16 at 08:45

create index clause on table variable

2 Answers2