Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert on a clustered index - is column ordering (ASC or DESC) important ?


Insert on a clustered index - is column ordering (ASC or DESC) important ?

Author
Message
moris7
moris7
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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 !
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54051 Visits: 44628
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, 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


moris7
moris7
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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 ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54051 Visits: 44628
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, 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


moris7
moris7
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 36
Thanks a lot Gail, i will do a test case later on today !

Have a nice day !
moris7
moris7
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search