Kaye Cahs (3/11/2016)
In my opinion, the sort results should be unpredictable because NULL is an unknown/unspecified value, which implies it cannot be sorted.I'm not arguing that the answer is incorrect. Practically, I like that there is a predictable behavior, because it makes our jobs a lot easier. Philosophically, I just don't agree with the way SQL Server handles this. Is this in line with the ANSI standard?
Anyone want to join me to discuss the philosophy of NULL over a pitcher of beer? (:
From SQL-92 (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt) page 531 (Section 20.2 <direct select statement: multiple rows> General Rules):
5) If an <order by clause> is specified, then the ordering of rows
of the result is effectively determined by the <order by clause>
as follows:
a) Each <sort specification> specifies the sort direction for
the corresponding sort key Ki. ...
b) Let X and Y be distinct rows in the result table, and let
XVi and YVi be the values of Ki in these rows, respectively.
...Whether a sort key value that
is null is considered greater or less than a non-null value
is implementation-defined, but all sort key values that are
null shall either be considered greater than all non-null
values or be considered less than all non-null values (emphasis mine)...
So yes, it's in line with the standard. And it makes logical sense too:
1. All of the other values are known, so not sorting them would be against the wishes of the user for no good reason.
2. All of the nulls are known to be unspecified, so placing them within the list of specified values would be including them within a group to which they don't belong.
3. Placing them before or after the known values is arbitrary, but it should be done consistently; hence the standard allows implementations to choose one, so their placement is predictable per implementation.