I have a table with many dates, ten years into the future and ten years history. (combined with customer id and more)
Most of the reads and writes from/to the database is for records with the current date.
How would sql server use an sorted unique index on the date field? Will it start from the top or bottom and scan half the index before it find the current date?
Or is there someway it wil start in the midle of the index and scan up or down based on the frist value in the index?
Best Answer
SQL Server non-clustered indexes are sorted in ascending order by default, unless you specify that they must be in descending order, when you define them.
(Also worth noting that if a table is a clustered index, that clustered index will be included in all non-clustered indexes as well.)
When it uses that index, it will usually do a seek into the index at the correct spot, using the index's B-tree structure, otherwise it will scan the entire index if the index doesn't satisfy the query.