NONCLUSTERED Index on Table Variable in SQL Server 2014

There are always advantages of using table variable when working with small dataset. But the only flaw with Table Variable was its lack of ability to create NON Clustered index on it but  SQL Server 2014  has given support to create NONCLUSTERED index on table variable.

In SQL Server 2008 R2

Lets try to create NC on @table variable column.

declare @tab_variable table

     ( id int identity(1,1) primary key,

     f_name varchar(10) index idx_f_name,

     l_name varchar(10))

Msg 1018, Level 15, State 1, Line 4

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.

We saw SQL Server 2008 R2 has no support for NC on table variable.

In SQL Server 2014

Now let’s see how we can create non clustered index on table variable on SQL Server 2014

declare @tab_variable table

       ( id int identity(1,1) primary key,

       f_name varchar(10) index idx_f_name,

       l_name varchar(10))

insert into @tab_variable(f_name,l_name)

select top 1000000 SUBSTRING(name,0,9), SUBSTRING(name,0,9) from  sys.columns

select f_name, l_name from @tab_variable

where f_name like 'TSQL'

f_name     l_name

---------- ----------

TSQL       TSQL

(1 row(s) affected)

So here we say that Non Clustered index, we can create on @table variable column, it is an awesome feature added to SQL Server 2014, the gap of temp variable and table variable reduced to just scope difference J

Your code has potential to bow every head.  

 

Post Reference: Vikram Aristocratic Elfin Share