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 «««910111213

Introduction to Indexes Expand / Collapse
Author
Message
Posted Wednesday, July 4, 2012 2:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 202, Visits: 229
GilaMonster (7/4/2012)

For seeking, for range scans, for supporting an order by.


Oh, I didn't have the ORDER BY clause in mind yet. Thanks, it's more clearly now.

So, if I my query returns the records in the descending order - it is better to create the index column also in the same order ("DESC"), right?
Post #1324849
Posted Wednesday, July 4, 2012 2:08 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 1,635, Visits: 5,592
GilaMonster (7/4/2012)
For seeking, for range scans, for supporting an order by. Not ever for returning rows in the index order without an order by specified. That was quoted a bit out of context (context being ordering of returned rows without an order by)


Would it potentially make a difference to the amount of fragmentation caused by adding new items to the index? e.g. if your index key is a value that will generally *decrease*, would you get a less fragmented index by declaring it DESC?
Post #1324851
Posted Wednesday, July 4, 2012 2:15 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 @ 2:38 PM
Points: 42,846, Visits: 35,975
Maybe. Will it be a big difference and will the reduction in fragmentation affect query performance? Depends on other factors (frequency of inserts, type of queries, etc)


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 #1324853
« Prev Topic | Next Topic »

Add to briefcase «««910111213

Permissions Expand / Collapse