Do you really understand null in T-SQL?

What does null mean?
Null does not represent an empty string, but does not represent 0.

What does null mean?
Null indicates that you do not know what it is, that is, the original meaning of null is that you do not know what it is, indicating that everything may be.

Null and? ComparisonLet's guess what will be returned by the above four rows of expressions ?, The answer is as follows:

Print (case when null = 'value' then 'true' else 'false' end)
Print (case when null! = 'Value' then' true 'else' false' end)
Print (case when null! = NULL then 'true' else 'false' end)
Print (case when null = NULL then 'true' else 'false' end)

 

 

Answer
False
False
False
False

Why is there such a result?
The answer is on msdn:
A value of null indicates that the value is unknown. A value of null is different from an empty or zero value. no two null values are equal. comparisons between two null values, or between a null and any other value, return unknown because the value of each null is unknown.
In this case, how should we compare it with the null value? The answer is to use the isnull function. The isnull function in T-SQL has two parameters, the first parameter is the expression to check whether the expression is null. The second parameter is the return value of the function when the expression is null.

Null and other valuesLet's guess what the above statement will print out?
The answer is nothing. All operations have the same result as print null, and the result is null.

Print null
Print 1 + NULL;
Print 'yukaizhao' + NULL;
Declare @ d datetime;
Set @ d = getdate ();
Print @ D + NULL;

 

 

Null Value and Index
If a column has a null value, you cannot create a unique index on this column. You can create a non-unique index. However, if a field has many rows with a null value, therefore, indexing on this field is ineffective. Therefore, we recommend that you do not create an index on a field with frequent null values (to be confirmed ).

Null and sorting
Null always exists as the minimum value during sorting, that is, when order by col ASC, the row where col is null is at the beginning, and vice versa.

Note: three functions related to null in T-SQL:
Isnull (check_expression, replacement_value)

The check_expression and replacement_value data types must be consistent.
If check_expression is null, replacement_value is returned.
If check_expression is not null, check_expression is returned.
 

Nullif is used to check two expressions. Syntax:
Nullif (expression, expression)

If two expressions are equal, null is returned, which is the data type of the first expression.
If two expressions are not equal, the first expression is returned.

The coalesce () function can accept a series of values. If all items in the list are null, only one value is used. Then, it returns the first non-null value. This technique describes two ways to use coalesce () functions in SQL Server.

From: http://www.cnblogs.com/yukaizhao/archive/2008/11/24/t_ SQL _null.html