InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 07:36:38 GMT20http://www.sqlservercentral.com/Forums/Topic1076278-360-1.aspxThat'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.Thu, 10 Mar 2011 11:49:10 GMTmoris7http://www.sqlservercentral.com/Forums/Topic1076278-360-1.aspxThanks a lot Gail, i will do a test case later on today !Have a nice day !Thu, 10 Mar 2011 08:51:53 GMTmoris7http://www.sqlservercentral.com/Forums/Topic1076278-360-1.aspx[quote][b]moris7 (3/10/2011)[/b][hr]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 ??[/quote]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.[quote]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 ?[/quote]End, not bottom. A clustered index on an ascending key minimises fragmentation. That's why it's one of the recommendations for a clustered indexThu, 10 Mar 2011 08:45:23 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1076278-360-1.aspxMy 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 ?Thu, 10 Mar 2011 08:29:35 GMTmoris7http://www.sqlservercentral.com/Forums/Topic1076278-360-1.aspxAdded 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 fragmentationThu, 10 Mar 2011 08:22:35 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1076278-360-1.aspxLet'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 ! Thu, 10 Mar 2011 08:18:31 GMTmoris7