CODEWITHSUNDEEP
sqlsql-serverclustered-indexcomposite-primary-key
Liran Ben Yehuda
Liran Ben Yehuda

Reputation: 1588

Composite clustered index and non clustered index in sql server 2005

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) ?

Upvotes: 1

Views: 1710

Answers (3)

gbn
gbn

Reputation: 432722

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

Upvotes: 2

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

-- 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.

Upvotes: 3

Jim
Jim

Reputation: 2146

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.

Upvotes: 1

Related Questions