A null value was encountered when SQL Server was sorted

This is a question that is often asked. In particular, when using Oracle, the client would have a problem with SQL Server, which is that SQL Server behaves differently from Oracle on top of NULL values.

In Oracle, a null value is considered to be an infinity value, so if you arrange it in ascending order, it will be ranked at the very end.

In SQL Server, in contrast, a null value is considered to be an infinitesimal value, so if you arrange it in ascending order, it will be in the front.

Such as

SELECT [ID]
　 FROM [demo].[dbo].[Orders] Order By ID

You will see the following effect

So is there any way to make this default mechanism for SQL Server like Oracle? The answer is: no

But we can think of some workarounds, such as writing code like the following

SELECT [ID]

from [Demo]. [dbo]. [Orders] Order BY case when ID is NULL THEN 1 ELSE 0 End

In this case, you can see the following effect

If the column has a CREATE index, you can see the following execution plan

However, there are still other solutions that can set the default value of the column to a large value, that is, if the user cannot supply the value of the column, the default is a large number instead. If it's int, this number can be 2147483647. In this case, if the sort of time of course there is no need to judge.