Is always clustered index better than non-clustered index ?

Question

Post reply

Is always clustered index better than non-clustered index ?

onlo

SSCertifiable

Points: 5080
More actions
July 13, 2010 at 4:24 am

#237590

Is always clustered index better than non-clustered index ?
Are there any cases or situation using non-clustered index is more situable ?
Some bodies say : for records INSERT, UPDATE, DELETE, using clustered index will use more time or resources to complete, is it true ?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

onlo (7/13/2010)
Is always clustered index better than non-clustered index ?

Are apples better than watermelons?

They're not the same thing, so it's pretty meaningless to ask if one is better than the other.

Are there any cases or situation using non-clustered index is more situable ?

Many.

Unless every single query filters the table by one condition, there's no way that just having a clustered index will be sufficient.

Some bodies say : for records INSERT, UPDATE, DELETE, using clustered index will use more time or resources to complete, is it true ?

Maybe, usually not though. Additional nonclustered indexes may slow those operations down.

Please take a read through this series on indexes.

http://www.sqlservercentral.com/articles/Indexing/68439/

http://www.sqlservercentral.com/articles/Indexing/68563/

http://www.sqlservercentral.com/articles/Indexing/68636/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 2

onlo (7/13/2010)
Some bodies say : for records INSERT, UPDATE, DELETE, using clustered index will use more time or resources to complete, is it true ?

To add to what Gail said...

If the INSERT is in a different logical order than the clustered index, the most likely. You could also suffer page splits so badly that your app will time out waiting for SQL Server to do the splits especially if multiple disparate inserts are possible.

Same goes for UPDATE if the update affects columns in the clustered index especially the first column of the clustered index.

DELETES aren't really affected by clustered indexes. Holes can be left in the clustered index but that doesn't take extra time or resources.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)