Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert on a clustered index - is column ordering (ASC or DESC) important ? Expand / Collapse
Author
Message
Posted Thursday, March 10, 2011 8:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 8:27 AM
Points: 4, Visits: 36
Let's say that i have a clustered index with a INT column for which I insert data with a ever growing number. That column is not an identity column because that number is sequenced in another table as an identity.

I want to know :

if i specify my column in my clustered index as DESC, does that help to lower fragmentation or not ?

What i mean is when i add data, is this data added on top or at the bottom of the index ? If it's added at then end, i'm guessing that my index will get fragmented fast ? Maybe SQL Server is aware of this and work accordingly ?

Thanks for you time and input !
Post #1076278
Posted Thursday, March 10, 2011 8:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 40,610, Visits: 37,074
Added at the end (past highest current value in index) is about the best for minimising fragmentation and page splits. unless you absolutely need it desc for some queries, leave it.

Added randomly throughout is what causes high page splits and fast fragmentation



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1076282
Posted Thursday, March 10, 2011 8:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 8:27 AM
Points: 4, Visits: 36
My goal of using DESC was to always have the most recent ID at the top. When the index is clean and not fragmented i would guess it would find the data for that ID sooner than if i used ASC ??

What you are saying is that usually i should leave it ASC because new data is added at the bottom and therefore if i have that column in the clustered index at DESC, it would fragment my index continually ?
Post #1076289
Posted Thursday, March 10, 2011 8:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 40,610, Visits: 37,074
moris7 (3/10/2011)
My goal of using DESC was to always have the most recent ID at the top. When the index is clean and not fragmented i would guess it would find the data for that ID sooner than if i used ASC ??


using DESC will have the newest data at the beginning of the index (not the top), using ASC will have it at the end (indexes are a b-tree structure)

There are few cases where there's a major speed difference. Feel free to test this situation out, but don't assume what the results would be without testing.

What you are saying is that usually i should leave it ASC because new data is added at the bottom and therefore if i have that column in the clustered index at DESC, it would fragment my index continually ?


End, not bottom. A clustered index on an ascending key minimises fragmentation. That's why it's one of the recommendations for a clustered index



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1076307
Posted Thursday, March 10, 2011 8:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 8:27 AM
Points: 4, Visits: 36
Thanks a lot Gail, i will do a test case later on today !

Have a nice day !
Post #1076319
Posted Thursday, March 10, 2011 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 8:27 AM
Points: 4, Visits: 36
That's indeed what i've found during my simple test. The only thing to check out now is the impact on queries of having that index changed.

Post #1076473
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse