Adding the Clustered Key To Your Nonclustered Index? Part 2

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.