3

I was applying indexing to a fresh database(no records on most of the tables)

What approach should I use apart from considering filter criteria. Should I index records in descending order since any window/web application using data would be accessing newly created records most of the time.

Should I keep master data in ascending order?

Shantanu Gupta
  • 20,688
  • 54
  • 182
  • 286

1 Answers1

2

This depends on how you are going to be accessing your data.

If you are going to be sorting it by DESC more often than ASC then make your clustered index DESC. Think of an index like a quick reference - set it up as close to how you will most commonly query the table as possible for the best results.

John Mitchell
  • 9,653
  • 9
  • 57
  • 91
  • You are assuming the index can only be scanned in one direction? – Martin Smith Jun 23 '12 at 13:02
  • @MartinSmith not at all, its a clustered ordered index, so it doesn't mean that its always held in a uniform list. If its implimented using a paging response, you take the first page, then look at the end location of page one and work forwards, working backwards means finding the first page, working out the size then jumping to the last page. Unless metadata storage stores the index of the last page in memory/disc, without a detailed document showing Microsofts implementation its impossible to say how they do it. – John Mitchell Jun 23 '12 at 13:54
  • @JohnMitchell - It is well documented how they do it. Each level of the index is a linked list with pointers to the previous and next page at the same level so it is as easy to follow the pointers back as it is forward. – Martin Smith Jun 23 '12 at 17:28