Columns from clustered index not necessary in non-clustered index?
Is this thought correct: It is not necessary to add columns to a nonclustered index, if that column is also part of the clustered index. This because the columns from a clustered index are always added to a nonclustered index. (and also if the column is an included column)
"It depends" .. I knew someone was saying that! :) I tried this: create table persoon(id int identity(1,1) primary key, naam varchar(32), postcode varchar(6), huisnummer varchar(16), geboortedatum date) go create index idx01 on dbo.persoon(postcode) create index idx11 on dbo.persoon(id, postcode) truncate table persoon go declare @i int set @i = 0 while @i < 5000 begin insert into persoon select replace(newid(), '-', ''), right('4567' + cast(abs(checksum(newid()))%10000 as varchar),4) + 'XX', abs(checksum(newid()))%100, dateadd(day, abs(checksum(newid()))%10000, '1960-01-01') set @i += 1 end select id from persoon where postcode = '6742XX' And after playing with several postcode values, the optimizer is always using idx01 to return the column [id]. It never used idx11 ...
Your search predicate is on postcode so the optimizer will use the index where that column is the first one defined. When you added ID to the index the optimizer *sees* that as a better match based on predicates and required results. They will produce the same results. When you use hints and force the index you will get a difference in performance. On SQL Server 2008 R2 idx01 - dbo.persoon(postcode) was 16% of the batch while idx11 - dbo.persoon(id, postcode) was 84%. With a third index of dbo.persoon(postcode, id) while it performs the same as idx01 the optimizer is more likely to choose it for this query. Having said all that, this is a relatively simple query... we may see different results on a composite clustered index or with mega-data, or (most likely) where a uniquifier was added by SQL Server to create the clustered index because of duplicates.
If I change the indexes to: create index idx01 on dbo.persoon(postcode) create index idx11 on dbo.persoon(postcode, id) and use the index hint: select id from persoon with (index = idx01) where postcode = '6929XX' select id from persoon with (index = idx11) where postcode = '6929XX' (postal code exists in table), the result for the execution plan is the same (index seek, same I/O and CPU cost, no lookup in clustered index). So where's the "it depends"?