Create non-clustered index on the table variable

Question

How do I create a non-clustered index on the table variable below? I tried but it's throwing an exception

Incorrect syntax near '@TBL'

My code:

DECLARE @TBL TABLE(ID INT PRIMARY KEY, FROMDATE DATETIME, TODATE DATETIME)

CREATE NONCLUSTERED INDEX IX_myindex 
ON @TBL (FROMDATE);

Thanks

You can look here https://stackoverflow.com/questions/4645210/sql-server-non-clustered-index-on-table-variable — MladenB, Feb 08 '19 at 12:50

Gordon Linoff · Accepted Answer · 2019-02-08T14:16:41.603

4

You have to declare indexes within the table definition. Although indexes are supported (since SQL Server 2014), the CREATE INDEX syntax does not support table variables. So:

DECLARE @TBL TABLE (
    ID INT PRIMARY KEY,
    FROMDATE DATETIME,
    TODATE DATETIME,
    INDEX idx_tbl_fromdate (FROMDATE)
);

Here is a db<>fiddle.

EDIT:

If you want a temporary table with an index, use a temporary table. If your version supports this syntax, you can do:

CREATE temp_table (
    ID INT PRIMARY KEY,
    FROMDATE DATETIME,
    TODATE DATETIME,
    INDEX idx_tbl_fromdate (FROMDATE)
);

Otherwise create the index separately.

edited Feb 08 '19 at 14:16

answered Feb 08 '19 at 12:51

Gordon Linoff

1,242,037
58
646
786

getting "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." – User Feb 08 '19 at 12:52
3

@user . . . As the answer specifies, explicit indexes in table variables are only supported since SQL Server 2014. You are probably using an older version, which does not support this functionality. In that case, just use a temporary table. – Gordon Linoff Feb 08 '19 at 12:55
as per the duplicate you can create a unique constraint for this. `DECLARE @TBL TABLE ( ID INT PRIMARY KEY, FROMDATE DATETIME, TODATE DATETIME, UNIQUE NONCLUSTERED (FROMDATE, [ID]) );` this is the same index as `CREATE NONCLUSTERED INDEX IX_myindex ON @TBL (FROMDATE); ` would create anyway if that syntax was allowed – Martin Smith Feb 08 '19 at 14:03

score 0 · Answer 2 · answered Feb 08 '19 at 12:55

0

You can define INDEX in table variable defination :

DECLARE @TBL TABLE (
     ID INT PRIMARY KEY, 
     FROMDATE DATETIME, 
     TODATE DATETIME
     INDEX FROMDATE NONCLUSTERED
)

answered Feb 08 '19 at 12:55

Yogesh Sharma

49,870
5
26
52

Create non-clustered index on the table variable

2 Answers2