SQL Server中NULL的正确使用与空间占用

我们常在sql server的使用或维护中遇上null，那么什么是null？如下是msdn给出的一段简短描述（见“null values”）：

• 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.

通俗的讲，null就是一个值，而且这个值是未知的（unknown）；null不能等价任何值，甚至都不等价它自己，即null不等于null。

为了清晰的理解上述的内容，我们创建一个测试表test_null，然后对表插入2条含有null值的记录，并进行相关验证操作：

--创建一张允许null值的表
create table test_null (
  num int not null primary key
  ,fname nvarchar(50) null
  ,lname nvarchar(50) null
)

--对表插入4条数据：最后2条记录含有null值
insert into test_null (num,fname,lname) values(1, 'tom','jane')
insert into test_null (num,fname,lname) values(2, 'dave','')
insert into test_null (num,fname) values(3, 'aaron')
insert into test_null (num,fname) values(4, 'betty')

为了验证null值是未知的，我们通过如下sql查询表test_null的记录，对lname字段进行=操作：

--若两个null是可以相等的，那么将输出4条记录。实际只输出2条记录

select
  *
from test_null tn 
left join test_null g
  on tn.num = g.num
where tn.lname = g.lname
------------------------------------------
1  tom jane  1  tom jane
2  dave    2  dave  

--查询lname为''的记录，即验证null不等于''
select
  *
from test_null tn
where tn.lname = ''
------------------------------------------
2  dave  

正确查询/使用sql server中的null

由于null是未知的，因此在sql server默认情况下我们不能使用=或<>去判断或查询一条null的记录（见上述），正确的方式是：使用is null或is not null去查询或过滤一条含有null的记录。

另外有函数isnull()，可判断并转换null为其他值。

--通过is null查询含有null的记录
select
  *
from test_null tn
where tn.lname is null
------------------------------------------
3  aaron  null
4  betty  null

--null不等于任何值，甚至null不等于null
--默认不能使用<>或=匹配null
select
  *
from test_null tn
where tn.lname <> null or tn.lname = null
------------------------------------------

但需注意：sql server仅是在默认情况下不能使用=或<>，当设置ansi_nulls为off后，即可使用=或<>查询null值

换言之，sql server默认是开启ansi_nulls选项的。

--设置ansi_nulls为off，并使用=null查询记录
set ansi_nulls off
select
  *
from test_null tn
where tn.lname = null
------------------------------------------
3  aaron  null
4  betty  null

插入或更新null值：

--插入1条含有null的新记录
insert into test_null (num,fname,lname) values(5, 'serena', null)

--更新某条记录的字段值为null
update test_null set fname = null
where num = 2

null的空间占用

通常的认识是：null在可变长类型（如nvarchar(50)，varchar(8)）中是不占用空间的，在固定长度的类型（如int）中会占用存储空间。

实际上，上述的认识不够严谨。真实情况是，null在可变长与固定长度的类型中均会占用空间

在sql server非sparse columns中，存储null的值需1个bit的null bitmap mask。

以上就是本文的全部内容，希望对大家的学习有所帮助。