Index ordering

[AndrewBaines]

Probably a dumb question, but this has been bugging me.

When you create an index, you get the option to set the Sort Order. I'm happy with how this affects the creation of the index.
Does this have any implication for performance, and if so, in what situations? In fact, does it have any implication for anything?

Andrew Baines

http://www.pursuittechnology.co.uk

 

[mrdenny]

It can have some impact on performance specifically if you order the index in the opposite order than the data is entered.

For example if you crease an index on the identity column where the column is setup as IDENTITY(1,1) and you create the index in descending order you could end up putting the data in the pages in the wrong order which could then end up causing more reads to the disk when you read through the index.

Also if you typically read from the end of the index, creating the index in descending order could improve a scan of the index as it'll be finding the data that it wants from the beginning of the index.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog