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
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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |