Composite clustered index and non clustered index in sql server 2005

Question

I created a new table with composite primary key, Lets say PrmID and Type, therefor a new composite clustered index has created (PrmID is first).I add another non clustered index for Type.

My question is - when I generate a queries that perform any statement on Type (such as GROUP BY), is the SQL engine using the non clustered index table or the PK clustered index (which more expensive for that kind of queries) ?

If your PK is on (PrimID,Type), then something using just Type (a WHERE clause, or a GROUP BY) cannot use that index - it's useless. So that query will definitely use the NC index — marc_s
– marc_s, Commented Apr 17, 2011 at 13:27

Michael Fredrickson · Accepted Answer · 2011-04-17 13:31:06Z

3

-- EDIT --

Thanks marc, I missed that...

-- END EDIT --

Second, is PrimID really not unique in your table, and is only unique in combination with Type? If PrimID is not duplicated in the table, maybe reconsider making it a composite PK.

Third, the best way to answer these types of questions is to look at the execution plan for your query. We can give you an answer on how we think SQL should handle the plan, but SQL Server will change the execution plan for various circumstances based on the data in your database, your hardware, etc...

Here's what the execution plan will look like: enter image description here

You can see that it tells you excatly which idexes are used, and how they are used... here's an article that gives a good introduction into understanding the execution plans.

edited Apr 17, 2011 at 13:31

answered Apr 17, 2011 at 13:08

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

marc_s Over a year ago

OK, but if the index is on (PrimID, Type), and your query only uses WHERE Type = ABC, then that index is useless and won't be used. A index will only be used if any number of its columns, starting from the first one, are used - it will be used if the first column is used in the WHERE, when the first and second are used and so on - but NOT if you only use the second column defined in the index

gbn · Accepted Answer · 2011-04-17 13:33:59Z

2

It depends on what the query look like, what columns are accessed, is it covering etc

A simple GROUP BY on type will most likely use the NC index. If you use other columns you may get a bookmap/key lookup or the index will be ignored and you'll have an inefficient PK scan

edited Apr 17, 2011 at 13:33

answered Apr 17, 2011 at 12:57

gbn

434k84 gold badges602 silver badges690 bronze badges

Comments

Jim · Accepted Answer · 2011-04-17 12:59:39Z

1

My understanding is that when you have a multi-field index and your query is not using the first field in the index, then that index will not be used (too difficult, not efficient, etc.) It will then use the other index.

But, to know this definitely, run your query with an execution plan, or even just an estimated execution plan. If you are using SSMS this is easy as pressing a tool bar button. It is the one that looks like three little blue and green boxes in an inverted "L" pattern. This will tell you exactly what index is being used. And while execution plans can change over the life of a query (as the data changes), it should not for this answer.

answered Apr 17, 2011 at 12:59

Jim

2,1461 gold badge23 silver badges46 bronze badges

Collectives™ on Stack Overflow

Composite clustered index and non clustered index in sql server 2005

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related