Clustered or Non-Clustered Index

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Clustered or Non-Clustered Index

Author

Topic

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2011-08-15 : 09:37:10

Let's say Primary Key (PK) of table SalesOrderDetail is SalesOrderID and SalesOrderDetailID

This query will be benefit a lot by having clustered index on PK


SELECT *
FROM SalesOrderDetail
WHERE SalesOrderID = 43671
AND SalesOrderDetailID = 120

This query will probably not give us optimized respone since ProductId is not indexed.


SELECT *
FROM SalesOrderDetail
WHERE ProductID = 755
AND SalesOrderDetailID = 120

My question: Should I index ProductID as Non Clustered or Clustered?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-15 : 09:41:47

You can only have 1 clustered index on a table, so non-clustered would be the only option (unless you change your primary key).

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2011-08-15 : 09:50:24

Thanks robvolk!

If I index ProductID as Non Clustered is there any loss in performance if the query is mixed with both clustered and non-clustered columns? Like this one below. I am not sure if SQL finds it harder to decide how it should search when there are conditions in a query with clustered and non clustered columns..


SELECT *
FROM SalesOrderDetail
WHERE ProductID = 755
AND SalesOrderDetailID = 120

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-15 : 10:12:49

It depends on the selectivity of the values you're querying. For instance, if a 10,000 row table has 9000 rows of product ID=755, then the optimizer will ignore the index and scan the table, because it's more efficient. If there are only 10 rows, then it will likely use an index seek.

One thing to consider is SELECT *, this can force the optimizer to do table or clustered index scans. If you limit the SELECT list to just the columns you need, and those columns are indexed, it improves the chance the optimizer will do index seeks.

The only way to know if an index improves performance is to test the query with and without it, and examine the execution plan and statistics time.

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2011-08-15 : 10:32:13

Thanks a lot robvolk,

I am doing some test as we speak. Once again, thanks!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-15 : 10:53:58

I think you need to do a bit of research on indexes. The kind of questions you are asking point to some misunderstandings about them.

For example: When you declare a NONCLUSTERED index then that index is a btree over the column(s) involved with a pointer to the clustered index (or internal index if the table doesn't have one). This means that when you SELECT columns that aren't part of the nonclustered index then you need to do a CLUSTERED INDEX LOOKUP to get the data you are looking for. That's not terrible but it's not optimal either. This is another reason why SELECT * isn't generally good practice. You have a few options here though. you can INCLUDE the wanted columns in the index (which doesn't help you filter on those columns but it does let you retrieve the values without a CI lookup).

To follow up Robvolk's statement:

quote:

"You can only have 1 clustered index on a table, so non-clustered would be the only option (unless you change your primary key)."

The reason you can only have one is the CLUSTERED INDEX is the *organisation* of the data. The data is stored as leaf nodes in the clustered index tree over whatever column(s) you've declared the index on.

A CLUSTERED INDEX is no better at filtering than a NONCLUSTERED one on the columns they are over (I believe). It's only when you need to get values back that aren't in the nonclustered index that you see the performance benefit of the CI.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2011-08-15 : 11:08:21

Thanks for your response Charlie! I will have all your answers in mind.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources