Nonclustered Index Seek Operator needs leftmost key in query

05 September,2013 by Jack Vamvas

Question: Can a query use a NonClustered Index Seek Operator with a complex key (multiple columns)  without including the  leftmost key as part of the WHERE predicate?

For example , there is a Nonclustered index with  key columns (X,Y,Z). If a query is executed with X=’x’ and Y=’y’ and Z=’z’ I’d expect the query to exploit the nonclustered index Seek operator.

But if I don’t include X=’x’  and only include Y=’y’ and Z=’z’ , is the  NonClutered Index Scan operator used?

Answer: In short , the answer is yes. If  the query does not include the leftmost part of the multiple key nonclustered index , the Index Scan operator will be used.  If the leftmost part of the key is used , the Index Seek operator will be employed

 A picture tells a thousand words , or in this case – code tells a thousand words. Run this t-sql code on a test database server – and study the execution plans after every query.

  

use MYDB

GO
--create table  
CREATE TABLE seekOperator
(ID INT IDENTITY(1,1),
X nvarchar(20),
Y nvarchar(20) ,
Z nvarchar(20)
)

GO
--populate table with some test data
declare @x INT;
SET @x = 0;
while @x < 1000
BEGIN
INSERT INTO seekOperator(X,Y,Z)VALUES('Jack' + convert(nvarchar(10),@x), @x, 'DBA' + convert(nvarchar(10),@x))

SET @x = @x + 1;
PRINT @x;
END

GO
--create nonclustered index with multiple columns 
GO
CREATE NONCLUSTERED INDEX [NC_Index1] ON [dbo].[seekOperator] 
(
	[X] ASC,
	[Y] ASC,
	[Z] ASC
)
GO
-- run statement 1 - with the leftmost key used in WHERE predicate

SELECT X,Y,Z FROM seekOperator WHERE X='Jack23' AND Y='23' AND Z='DBA23'

--check Exceution Plan - it will be an IndexSeek on NC_Index1
GO
-- run statement 2 - with the leftmost key not used in WHERE predicate
SELECT X,Y,Z FROM seekOperator WHERE  Y='23' AND Z='DBA23'

--check Exceution Plan - it will be an Index Scan on NC_Index1
GO
-- run statement 3 - with only the leftmost key used in WHERE predicate
SELECT Z FROM seekOperator WHERE  Z='DBA23' 
--check Exceution Plan - it will be an Index Scan on NC_Index1

 Read More

SQL Server – Find high impact queries with sys.dm_exec_query_stats

SQL Server execution plan as text - SQL Server DBA

SQL Server Query Optimizer and Statistics - SQL Server DBA

SQL Performance tuning - Asking the right question



Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Nonclustered Index Seek Operator needs leftmost key in query


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer