How SQL Server deal with Null value during the sort?

Let us make a experiment .

 

Create test table

 

CREATE TABLE TEST
(
  id INT NULL
)


INSERT INTO TEST VALUES (1)
INSERT INTO TEST VALUES (2)
INSERT INTO TEST VALUES (3)
INSERT INTO TEST VALUES (4)
INSERT INTO TEST VALUES (5)
INSERT INTO TEST VALUES (NULL)

INSERT INTO TEST VALUES (-5)

 

Case 1 : Without 'ORDER BY' - order sequence is base on data physical store position

 

SELECT * FROM TEST;

Result:

1
2
3
4
5
NULL
-5

 

Case 2 : With 'ORDER BY XX ASC' - NULL always on the top

 

SELECT * FROM TEST order by id asc;

Result:

NULL
-5
1
2
3
4
5

 

Case 3 : With 'ORDER BY XX DESC' - NULL always on the bottom

 

SELECT * FROM TEST order by id desc;

Result:

5
4
3
2
1
-5
NULL

 

-http://froebe.net/blog/2007/10/19/fw-microsoft-sql-server-sort-values-ascending-but-nulls-last/

This is a frequent request in newsgroups and fora. People want to sort the column in ascending order but don’t want the NULLS at the beginning. Oracle has this syntax:

ORDER BY ColumnName NULL S LAST ;

SQL Server does not have this. But there are 2 ways to do this. The first one is by using case and the second one by using COALESCE and the maximum value for the data type in the order by clause.