NULL

1 NULL can be defined as absence of value, undefined, or the value which is unknown.

2 All datatypes can be defined with NULL constraint

3 Direct usage of arithmetic or logical operations on NULL will not work as expected

4 The system functions ISNULL, COALESE and NULLIF are used only to deal with NULL

5 NOT NULL constraint cannot be defined for a computed column until it is also PERSISTED

6 The only datatypes that will interpret NULL differently are rowversion and timestamp

Run this code and see the output

declare @rv rowversion , @ts timestamp
select @rv=null, @ts=null
select @rv as rowversion,@ts as timestamp

Output is
rowversion         timestamp
------------------ ------------------
0x                 0x

7 NULL=NULL will never be true unless SET ANSI_NULLS is OFF

While the first query returns nothing, the second will return 6
--Query 1
set ansi_nulls on
select 6 where null=null
--Query 2
set ansi_nulls off
select 6 where null=null
The condition WHERE col IS NULL will not be affected by the above setting

8 The default datatype of NULL is INT. Refer Default datatype of NULL for more information

9 Column with UNIQUE constraint will allow only one NULL value in SQL Server

10  NULL will make SQL Server to use short circuit logic in some cases

Consider this example
select 6/0/null
select null/6/0
While the first query throws an error the second query returns NULL

11 The value NULL is not equal to string value 'NULL'

12 By default NULL values come first when a column is ordered in ascending order and come last when ordered in descending order

13 If you dont use GROUP BY clause, the aggregate functions will always return single value (NULL) when the condition is false

select sum(col) as col from ( select 45 as col ) as t where 1=0
The above returns NULL

14 NULL values are by default omitted in all aggregate functions

Consider the following example

select sum(col) as col_cum,count(col) as col_count,avg(col*1.0) as col_avg from ( select 1 as col union all select null as col union all select 2 as col union all select 3 as col ) as t

The output is
col_cum     col_count   col_avg
----------- ----------- -----------
6           3           2.000000

15 Aggregate functions cannot be directly applied over NULL value

This code

select sum(null) as null_sum
returns the following error
Msg 8117, Level 16, State 1, Line 1
Operand data type NULL is invalid for sum operator.

16. If sub-query returns NULL value when NOT IN used to filter the records, main query returns no records.

This code

create table test(id int)
insert into test select 1 union all select 2 union all select null union all select 3
select * from test where id in (1,2,3,null)
select * from test where id not in (1,2,3,null)

where first query returns 3 values and second query returns no records

Reference : About NULL