| Author |
Topic |
|
sunisha
Starting Member
6 Posts |
 Posted - 2006-04-03 : 10:13:31
|
| i have a query where I am retrieving the 1st row (using top 1) and I am ordering by a column which contains nulls ; I am order by DESC which forces the NULL values to the bottom...but I am wondering if my where clause returns rows with all NULL values for the order by column, is this not guaranteed to return the same row for the "top 1"? ; i mean if the table had a clustered index doesn't determine the order of the rows that gets returned since the order by null would order the rows together? thanks for your help!Sunish Abraham |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-03 : 10:16:38
|
| Post some sample data and the result you wantMadhivananFailing to plan is Planning to fail |
 |
|
|
sunisha
Starting Member
6 Posts |
Posted - 2006-04-03 : 10:39:11
|
| I am trying to determine if doing the order by on a column which contains all NULL values will return the same row because there is a CI on the table. I can't find anything in BOL to back this up or any articles on the Net talking about this.Below is my example...set nocount ongodrop table #test gocreate table #test (column1 int identity(1, 1), column2 int null)gocreate clustered index testCI on #test (column1)goinsert #test values (null)insert #test values (null)insert #test values (null)insert #test values (null)insert #test values (null)insert #test values (null)goselect top 1 * from #test order by column2 descgoset nocount offgo |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-04-03 : 15:18:37
|
| I would not rely on the clustered index for returning the same top 1 row ordered by a column (with nulls). I would include addition column(s) in the order by clause to explicitly set the order.Be One with the OptimizerTG |
|
|
|
sunisha
Starting Member
6 Posts |
Posted - 2006-04-03 : 16:44:25
|
| unfortunatley, I can't set additional columns on the order by. I am trying to prove whether the order by on a column containing only nulls is deterministic or non-deterministic (i.e. can return different results)? is there any "documentation" in BOL which states that using columns which contain NULL values when using order by can return non-deterministic results? thanks!Sunish |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-04-03 : 17:39:07
|
| >>I am trying to prove whether the order by on a column containing only nulls is deterministic or non-deterministicIn terms of Sql Server definition of determinism, a sql clause (order by) is not applicable. Determinism, as in Objectproperty(<id>, 'IsDeterministic'), only applies to Views and Functions. Are you trying to figure out why a view or function is not returning "1" for isDeterministic? so you can index a view or computed column?In terms of always getting the same top 1 row with an order by <columnWithAllNulls> I don't recall seeing specific documentation, but I'm certain it is not reliable to count on the clustered index of the table for the secondary order. With different execution plans you could end up with differing orders.Be One with the OptimizerTG |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2006-04-03 : 22:53:50
|
quote:
Originally posted by sunisha
unfortunatley, I can't set additional columns on the order by. I am trying to prove whether the order by on a column containing only nulls is deterministic or non-deterministic (i.e. can return different results)? is there any "documentation" in BOL which states that using columns which contain NULL values when using order by can return non-deterministic results? thanks!Sunish
Order by is only deterministic if you specify enough values to be unique to the rows you select. In other words, as soon as you reach equality on a part of your ORDER BY, the rows within that group can be in any order unless specified further by more columns in the ORDER BY clause. If you select more columns than ORDER BY columns you should not assume anything else. There is no inherent order in SQL, although on the surface it may appear so.The fact you have nulls means your data cannot be unique to the point you need it to be. |
|
|
|
sunisha
Starting Member
6 Posts |
Posted - 2006-04-03 : 23:44:38
|
| i understand that the "strict" definition of order by for "like" rows means that order is not guarnteed...but with the "practical" implementation in SQL Server in regards to execution plans and rows returned by an "index" order, wouldn't like rows be returned in the order of the index used? so if i modified the select clause to include the table's clustered index in the order by wouldn't I always get the rows in the same order?Sunish |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2006-04-04 : 20:02:35
|
quote:
Originally posted by sunisha
i understand that the "strict" definition of order by for "like" rows means that order is not guarnteed...but with the "practical" implementation in SQL Server in regards to execution plans and rows returned by an "index" order, wouldn't like rows be returned in the order of the index used? so if i modified the select clause to include the table's clustered index in the order by wouldn't I always get the rows in the same order?Sunish
If you specify the clustered index in the order by and it is unique you've already solved the problem. Otherwise you will probably get away with it. However I don't really see why it should be a given. Perhaps some of the blocks were already in the cache and were read out of order? Perhaps the optimizer decided not to use a different index to the one you were expecting, or no index at all. I've had real experience of running an identical query twice in a row and getting the results in a different order. Every time it was because someone didn't include enough columns in the order by.I suggest you update your model to include some kind of sequence number or timestamp. If you truly want something in a particular order you must ask for it. |
|
|
|
sunisha
Starting Member
6 Posts |
Posted - 2006-04-04 : 21:58:09
|
| Yeah I wish I could create a timestamp column which would have resolved my issues. I thought that if you order by column(s) and the order by clause has more than one row with the same order by values that the "order" of the rows is the order of the index used to access the data Sunish |
|
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-04-13 : 07:49:56
|
| What would be wrong with:select top 1 * from #test order by column2 desc, column1... or am I missing something ? |
|
|
|
sunisha
Starting Member
6 Posts |
Posted - 2006-04-13 : 22:45:41
|
| sorry, i forgot to mention that in my "real" case the clustered index is on a column which is not an identity column (it is "generated" by the application [which is third-party so I don't have control])...so if I order by "column 1" from my example, I am not guaranteed order |
|
|
|
|
order by column with NULL values