Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2006-12-05 : 09:52:32
|
| If I want to doSELECT MAX(MyDateCol)and I create an index on MyDateCol to help the process, should the index be Ascending or Descending - or doesn't it matter?Kristen |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-05 : 09:57:46
|
| Depending on the amount of records, DESCENDING might be a better choice because the MAX(MyDateCol) will be found on first page in first extent.I imagine that creating an ASCENDING leads to some reading to get last extent and last page.Peter LarssonHelsingborg, Sweden |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-05 : 10:24:14
|
| That was my thinking too ...There are 5.5M rows in the table, I'll give it a go. Not sure how I can time it though?SET STATISTICS IO ON; SET STATISTICS TIME ONshowsTable 'MyTable'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.same values if I use a Descending indexKristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-05 : 13:36:26
|
| OK, supplemental question: what's the benefit of using ASC / DESC in an index? (Given that presumably SQL Server can "walk" the index in either direction)Kristen |
|
|
|
|
|
|
Max(MyDateCol) index Asc or Desc?