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