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