About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Saturday, November 22, 2014

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


No comments:

Post a Comment