question

NoviceSQL avatar image
NoviceSQL asked

Index on table variable

declare @temptable table ( id int primary key clustered, name varchar(10) ) create nonclustered index IX_NC_name on @temptable (name asc) insert into @temptable values (2,'abc'), (1,'pqr'),(3,'mno'), (5,'jkl'), (4,'rst') select id, name from @temptable where name = 'pqr' Getting error when running this script Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '@temptable'. I checked syntax for create nonclustered index, looks to be ok. Anyone please help.
sql-server-2008indextable-variable
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
You cannot create non-unique, non-clustered indexes on table variables. If you really want an index on that column, then you would have to use temp tables instead: create table #temptable ( id int primary key clustered, name varchar(10) ) create nonclustered index IX_NC_name on #temptable (name asc)
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes you can define a PK or UNIQUE constraint which are implemented as indexes, but they have to be part of the table definition. You cannot add these later. BTW you can have non-clustered indexes in SQL2014 : http://www.brentozar.com/archive/2014/04/table-variables-good-temp-tables-sql-2014/
2 Likes 2 ·
See @Sules answer and comment, there are ways around this. Would add though, that the lack of statistics on table variables mean you need to be aware of this and determine if the solution fits your scenario. How many rows do you expect to be handling in this table variable? And will you be joining it to other tables?
1 Like 1 ·
The biggest possible point, while you may have an index, you don't have statistics. That means that index will not function in the same manner as other indexes on tables that do have statistics. At best a unique index will be recognized as such by the optimizer so that it will know for any given key value within the table, there will only be a single row. That can be a positive for some queries. But any other type of filtering normally associated with an index just won't work because there are zero statistics.
1 Like 1 ·
Oh. But clustered index can be created? I mean I am defining ID as primary key clustered. That works for me. How?
0 Likes 0 ·
Sule avatar image
Sule answered
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

True as long as the PK is clustered: declare @temptable table ( id int primary key clustered, name varchar(10), unique (name, id) --mimics the index IX_NC_name ) insert into @temptable values (2,'abc'), (1,'pqr'),(3,'mno'), (5,'jkl'), (4,'rst') select id, name from @temptable where name = 'pqr'
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.