CLUSTERED INDEX QUESTION

[sqlcasey]

Hi,

Using this as an example:

SELECT c1, c4 FROM myTable WHERE c1 = 1 and c2 = 2 and c3 = 3 ORDER BY c1

I created a clustered index on c1, c2, and c3 initially

Then I dropped that index and created a clustered index on c1 ONLY (c1 being the most selective column, but not unique)

and the execution plans were identical for both indexes

So, it seems that putting a clustered index on c1 is sufficient, since the clustered index contains all the data for the row, yes? No need to create a clustered index on c1, c2 and c3 - even though they are using in the where clauses.

Just wondering if my thinking is correct here.

Thanks

 

[mrdenny]

A clustered index doesn't hold all the data in the row. If C1 is the most selective then it would probably be enough. However as the table grows over time will this still be the case?

Have you tried a non-clustered index on c1, c2 and c3 as well?

If this was me I'd build the index on C1, C2, and C3.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sqlcasey]

Yes, I tried both versions - they provide idential results in the execution plans - clustered index seek or scan

to clarify:

c1 is pretty unique, and for multiple c1 results, c2 will be the same, for example:

c1 c2
12354 11
57683 11
15643 11

So in fact, adding c2 to the clustered index seems unnecessary, yes?

 

[Catadmin]

Maybe it is at the moment, but from time to time, you'll want to double check this.

The main problem is, the queries being used now won't always be the queries that are used. As users get used to new tables and new columns, they'll change what they want to look at and how they want to look at it. Hence part of the reason that Indices become more of a burden than a help. So, if this works for you right now, go ahead and stick with it. But don't assume it will always work as the table and the database grows & changes. Every few months, you'll want to run Profiler to see how everything is performing or do some DBCC commands to verify the state of the Index.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"