SQL Server Index

Question

SQL Server 2005 SP3 on Windows Server 2008 R2.

I ran a server side trace, and use DTA (database engine tuning advisor) On one table DTA is suggesting me to create a nonclustered index with clustering key. I mean per DTA I need to create a composite non clustred index with explicit clustred index key as part of non clustered index.

I thought at leaf level non clustred index already includes clustering key.

MeasurementDataID is a primay key and has clustered index on it...

CREAT INDEX IX_NAME(
[MeasurementID] ASC,
[SampleName] ASC,
[MeasurementDataID] ASC )

Answer 1

At leaf level it does include the clustering key so the index you have will cover the query.

But if your query is

WHERE MeasurementID = 1 AND SampleName = 'foo' AND MeasurementDataID=10

Without the inclusion of MeasurementDataID it would still need to scan all records in the index matching the WHERE MeasurementID = 1 AND SampleName = 'foo' part rather than satisfy the whole thing with a seek so dependant upon how selective the first two are it might still be useful.

It's basically the same decision you need to make when considering to add a column to an index as an included column or add it to the key.