By letting you create an index in descending order, SQL Server 2000 enhances the ability of clustered indexes to support sort operations. You can use the DESC keyword in the CREATE INDEX statement or in a constraint definition for a primary key or unique constraint. But SQL Server already had the ability to traverse a clustered index in either ascending or descending order, so why would you need to store the index in descending order? You wouldn't, at least for a single-column index.
The usefulness of controlling the order of the stored index keys is more evident when you need to sort by multiple columns and you need a different order for each of the sort columns. For example, suppose you have a table containing the names of all your salespeople and the total revenue each generated during the previous year. If you want to list your salespeople by state, with the states in alphabetical order, and within each state you want to list the top-earning salespeople first and the lowest-earning last, your query might look like Query 4:
--Query 4:
SELECT Name, Revenue, State
FROM SalesRevenue
ORDER BY State, Revenue DESC
(Note that you can't execute Query 4 because it refers to the nonexistent SalesRevenue table.)
A clustered index on State and Revenue in SQL Server 7.0 or 6.5 would store all the data in ascending order and could be traversed in either ascending or descending order. With such a clustered index, SQL Server could easily find all the rows in state order by following the clustered-index pages. But then, returning the rows for that state in a different order than they're stored in—listing the highest revenue within each state first—wouldn't be easy. In fact, SQL Server would have to completely re-sort the data in the desired order. For example, in the Northwind database's Order Details table, the clustered index is on the OrderID and ProductID columns. Query 5 asks for the data to be sorted by the clustered-index columns, with the first column ascending and the second column descending:
--Query 5:
SELECT * FROM [Order Details]
ORDER BY OrderID, ProductID DESC
The SHOWPLAN output shows that SQL Server has to sort the data even though it scans the clustered index:
StmtText
--------------------------------------------
|--Sort(ORDER BY:([Order Details].[OrderID] ASC,
[Order Details].[ProductID] DESC))
|--Clustered Index Scan(OBJECT:([Northwind]
.[dbo].[Order Details].[PK_Order_Details]))
As a comparison, you can run Query 5 without the DESC keyword so that the resulting order is the order in which all the data is stored. Note that the plan for the query without DESC indicates that no sorting is necessary. Although you can create a SQL Server 2000 index in descending order, the only real reason for doing so is if you know that you frequently need to return data sorted by multiple keys, and the sort direction isn't the same for all the sort keys.
How Fast Is Fast?
In clustered indexes, SQL Server keeps the data rows in logically sorted order. For nonclustered indexes, SQL Server stores the index keys in the leaf level in logically sorted order, along with bookmarks to the actual data rows. Why can't SQL Server take advantage of that sorting in the nonclustered index with queries in which you're sorting by a nonclustered-index key? By default, SQL Server doesn't take a nonclustered index into account when sorting by a column with a nonclustered index. (The exception is the case in which the nonclustered index completely covers the query, so SQL Server doesn't need to follow any bookmarks or access the data pages.)
To demonstrate that SQL Server won't use a nonclustered index for sorting, you can build a nonclustered index on the Order Details table's Discount column:
CREATE INDEX DiscountIndex ON [Order Details]
(Discount)
Then, execute Query 6:
--Query 6:
SELECT * FROM [Order Details]
ORDER BY Discount
The query plan
StmtText
--------------------------------------------
|--Sort(ORDER BY:([Order Details].[Discount] ASC))
|--Clustered Index Scan(OBJECT:([Northwind]
.[dbo].[Order Details].[PK_Order_Details]))
reveals that SQL Server scans the clustered index (the data itself), then performs a sort. SQL Server's sorting algorithms are so fast that the optimal overall performance comes from retrieving all the data, then performing a complete sort, rather than following any bookmarks from the nonclustered index. Although this efficient sorting lets SQL Server return the complete resultset in minimal time, no data can be returned until all the data is sorted. Consider: The Order Details table has only 2155 rows. If I performed the same kind of sort on a table with hundreds of thousands or even millions of rows from a client application that needs to use the sorted data to fill a drop-down list, I'd be waiting a long time to see results.
Prev. page
1
[2]
3
next page 
