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.
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)
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/
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?
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.
it's wrong syntax... Read this article for help [
http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/][1] [1]:
http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/
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'
16 People are following this question.
