How to make use of NI with Table variable with SQL Server 2014

Non clustered index in table variable is a new feature that rolls out with SQL Server 2014, but how do we make use of it, let see with an example.

Here I am creating a table variable @IndexDemo with id as primary key and stateprovinceid as non clustered column. Lets query the table variable and see whether it is using non clustered index.

declare @IndexDemo table (

       id int identity primary key clustered (i),

       city varchar(60),

       stateprovinceid int,

       index ix_stateprovinceid nonclustered (stateprovinceid)

);

insert @IndexDemo (city,stateprovinceid)

select city, stateprovinceid

from person.address;

select city

from @IndexDemo

where stateprovinceid=1;

go

Now lets check the execution plan

Here we saw our non clustered index didn’t comes into play, instead clustered index scan takes play, very odd. Let’s dig into query to find more on it

select city

from @IndexDemo

where stateprovinceid=1;

go

So if we see our query, since our predicate is on non clustered index column i.e stateprovinceid, it should do a NI seek operation then a lookup to clustered index to get city field Values.

Lets do one thing, lets modify the query to select on stateprovinceid instead of city.

declare @IndexDemo table (

       i int identity primary key clustered (i),

       city varchar(60),

       stateprovinceid int,

       index ix_stateprovinceid nonclustered (stateprovinceid)

);

insert @IndexDemo (city,stateprovinceid)

select city, stateprovinceid

from person.address;

select stateprovinceid from @IndexDemo

where stateprovinceid=1;

go

Lets see the execution plan for the same

So here we can see from execution plan that non clustered index on sateprovinceid come into play, that means when you have key lookup for clustered index, the optimizer ignoring non clustered index seek and doing clustered index scan in case of @table variable indexing.

Summary: If you are thinking to make use of non clustered index on @table variable then don’t surround your select statement columns other then non clustered index columns.

Lets rejoice, doing codingJ

Post Reference: Vikram Aristocratic Elfin Share

Table Variable Non Clustered Index not keeping correct Statistics

With table variable, the optimizer fail to predict right number of rows returned from the query execution and fall in using wrong index selection. Lets try out with an example, first we will see the same with #temp table then will check the @table variable of SQL Server 2014.

Here we are creating a temporary table with clustered and non clustered index on it.

create table #IndexDemo  (

    id­ int identity primary key clustered,

    city varchar(60),

    stateprovinceid int,

    index ix_stateprovinceid nonclustered (stateprovinceid)

);

insert #IndexDemo (city,stateprovinceid)

select city, stateprovinceid

from person.address;

go

select city

from #IndexDemo

where stateprovinceid=1;

drop table #IndexDemo

Here in above script we have temp table with stateprovinceid field as non clustered key and id as clustered index.  Now if we see the execution plan for the query

select city

from #IndexDemo

where stateprovinceid=1;

 

Now looking at execution plan we can see it has predicted 25 rows, so to efficiently retrieve it use nonclustered index that’s fair enough. Then do the lookup to get the city column from clustered index.

Now lets implement the same using SQL Server 2014 table variable.

declare @IndexDemo table (

       i int identity primary key clustered (i),

       city varchar(60),

       stateprovinceid int,

       index ix_stateprovinceid nonclustered (stateprovinceid)

);

insert @IndexDemo (city,stateprovinceid)

select city, stateprovinceid

from person.address;

select city

from @IndexDemo

where stateprovinceid=1;

go

Now lets check the execution plan

 

Here, the estimated number of row count is showing 1 which is but obvious wrong in number, but to the count, it should use Non Clustered Index seek but oddly it uses Clustered Index scan. So to our example it fail to utilize non clustered index.

Note: Be aware before you decide to go for table variable if you thinking to make use of non clustered index feature.

Let my rhythm take me with you :)

Post Reference: Vikram Aristocratic Elfin Share

With SQL Server 2014, New way to create NONCLUSTERED Index on Persisted Table Object

With SQL Server 2014 we can create NONCLUSTERED Index at table level while creating table object., Lets see how we can do by doing a small practical.

In SQL Server 2014

We are creating a table and trying to define NONCLUSTERED index at table creation.

create table testDemo

(col1 int primary key,

col2 int index idx_col2_testDemo,

col3 int index idx_col3_testDemo)

Command(s) completed successfully.

Since command has successfully executed, lets see indexes created by querying Sys.indexes Catalog view.

select name, index_id, type_desc

from sys.indexes where object_id = OBJECT_ID('testDemo')

name                           index_id    type_desc

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

PK__testDemo__357D0D3EF2E23FAC 1           CLUSTERED

idx_col3_testDemo              2           NONCLUSTERED

idx_col2_testDemo              3           NONCLUSTERED

By querying Sys.indexes we are sure, we have three indexes on testDemo table. So this is how you can create NC index at table creation.

Just for our approval, lets see whether the same syntax is working in SQL Server 2008?

In SQL Server 2008 R2

create table testDemo

(col1 int primary key,

col2 int index idx_col2_testDemo,

col3 int index idx_col3_testDemo)

Msg 1018, Level 15, State 1, Line 3

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.

So here we saw in SQL Server 2008 R2 we don’t have option to created NC indexes at table creation.

The essence of code; it binds programmer in a non-fragile bond and never let his hand go without her!!!

Post Reference: Vikram Aristocratic Elfin Share

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

A new way to create Non Clustered composite index in SQL Server 2014

In Earlier version of SQL Serve i.e SQL Server 2008, SQL Server 2012, we were unable to create Composite NONCLUSTERED index on table level, i.e while creating table.

Creating NONCLUSTERED composite Index on SQL Server 2008R2

create table test1

(col1 int primary key clustered,

col2 int index idx_nc(col2, col2),

col3 int

)

Msg 1018, Level 15, State 1, Line 3

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.

Earlier version of SQL Server does not allow us to create Composite NONCLUSTERED index on table level. So to create Composite index, we were writing separate statement of Create INDEX, see below

First create table

create table test1

(col1 int primary key clustered,

col2 int,

col3 int

)

Command(s) completed successfully.

Then once the table is ready, create composite index on columns.

create nonclustered index idx_test1_col2_col3

on test1(col2,col3)

Command(s) completed successfully.

But with the advancement of SQL Server 2014, it allow us to create Composite NonClustered Index on table level, lets see how we can create Composite index in SQL Server 2014 at table level.

Creating NONCLUSTERED Composite Index at Table Level in SQL Server 2014

create table test1

(col1 int primary key clustered,

col2 int index idx_nc(col2, col3),

col3 int

)

Command(s) completed successfully.

Querying Sys.Indexes to check the indexes created.

select name, type_desc from sys.indexes

where object_id = OBJECT_ID('test1')

name                         type_desc

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

PK__test1__357D0D3EAC78055D  CLUSTERED

idx_nc                       NONCLUSTERED

Your code is so flexible, u can twist it, blend it and fit it to ur client wants, it has the potential of likeliness to patrons needs.

Post Reference: Vikram Aristocratic Elfin Share