Of course, a rather obvious answer presents itself the next day. After reviewing the actual environment that my colleague was working in, it popped quickly into mind.
What if the table has a compound primary key? See comments for a play-by-play.
drop table dbo.testclusteredinclude
go
create table dbo.testclusteredinclude
( id1 int not null
, id2 int not null
, id3 int not null
, text1 varchar(30) Not null
, constraint pk_testclusteredinclude
primary key (id1, id2, id3)
)
go
insert into dbo.testclusteredinclude (id1, id2, id3, text1) values (1,2,3,'test1'); --put in our seed row
--filler data of 10000 rows
with cte3pk (id1, id2, id3) as
(select id1=2,id2=3,id3=4
union all
select id1+1, id2+1, id3+1
from cte3pk
where id1 <= 10000
)
insert into dbo.testclusteredinclude (id1, id2, id3, text1)
select id1, id2, id3, 'test2' from cte3pk
OPTION (MAXRECURSION 10000);
go
alter index all on dbo.testclusteredinclude
rebuild
go
--turn on show actual exec plan
--Second key of the Clustered Key can
benefit, this easy to understand.
-- We're simply giving SQL a narrower set of data, via the nonclustered index.
select id2, text1 from dbo.testclusteredinclude
where
id2 = 1001
go
create nonclustered index idx_nc_testclusteredinclude_id2_text1
on dbo.testclusteredinclude (id2, text1)
go
select id2, text1 from dbo.testclusteredinclude
where
id2 = 1001
go
drop index idx_nc_testclusteredinclude_id2_text1 on dbo.testclusteredinclude
go
--Still, putting a subsequent key of a
compound clustered key in the include column doesn't help.
-- SQL can still do an index seek on
id2, even when the index doesn't contain it
(idx_nc_testclusteredinclude_text1).
select id2, text1 from dbo.testclusteredinclude
where
text1 = 'test2'
go
create nonclustered index idx_nc_testclusteredinclude_text1
on dbo.testclusteredinclude (text1)
go
select id2, text1 from dbo.testclusteredinclude
where
text1 = 'test2'
go
create nonclustered index idx_nc_testclusteredinclude_text1_inc_id2
on dbo.testclusteredinclude (text1) include (id2)
go
select id2, text1 from dbo.testclusteredinclude
where
text1 = 'test2'
go
drop index idx_nc_testclusteredinclude_text1 on dbo.testclusteredinclude
drop index idx_nc_testclusteredinclude_text1_inc_id2 on dbo.testclusteredinclude
go
One final note - none of the SELECT statements above generate any missing index suggestions in SQL 2012 SP1 or SQL 2008 SP2, even though without any nonclustered indexes they all generated Clustered Index Scans.
