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 |
|
nr
SQLTeam MVY
12543 Posts |
 Posted - 2003-03-13 : 18:53:47
|
| I was looking in bol for a description of index usage in response to a question from samc and came across this under 'Creating Ascending and Descending Indexes'.CREATE NONCLUSTERED INDEX DescIdx ON ObjTable(ObjName ASC, ObjWeight DESC)the index defined previously for the ObjTable can completely eliminate the need for an ORDER BY clause such as:ORDER BY ObjName ASC, ObjWeight DESCThis seems like a weird thing to say even if it is true. It implies that the data will always be returned in this order whereas I assume it will depend on the where clause, other indexes, statistics.I guess the important bit is 'can' but it's not something I would rely on.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-03-13 : 19:10:21
|
quote:
I guess the important bit is 'can' but it's not something I would rely on.
I have enough trouble relying on clustered indexes returning the right order and so for a non-clustered index my apprehension grows greater. We all know that ordering has no place in relational theory and thus we should always specify the ORDER BY clause for guaranteed ordered results.Run 2 statements, one with the ORDER BY and one without and I think they should give almost identical execution plans. Then reverse the "direction" on the ORDER BY for each column and again the execution plans are almost identical, except for the "ORDERED BACKWARD" physical read.I must admit I am impressed with SQL Server ability to optimise ORDER BY results sets, but stress once more I would NOT rely on the underlying storage to provide the right order....EDIT: Some terrible spelling errors...DavidM"SQL-3 is an abomination.."Edited by - byrmol on 03/13/2003 19:11:41 |
 |
|
|
|
|
|
Using index instead of order by