SQL SERVER中关于NULL的设定

/***************************************************  

          作者：herowang(让你望见影子的墙）

    日期：2009.12.19

          注：    转载请保留此信息

    更多内容，请访问我的博客：blog.csdn.net/herowang

****************************************************/

SQL SERVER2005中关于NULL的设定

NULL处理时SQL SERVER中一个比较麻烦的事情，有许多处理可能因为NULL而得到一些预想不到的结果。NULL在数据库中一般有三层含义：1、该属性值不适合该实体；2、该实体还没有改属性；3、该实体有该属性，但是现在还不知道。

对于NULL的使用，和数据库的选项和会话的设置是息息相关的，也就是说不同的数据库的选项和会话的设置，对NULL的处理方式是不一样的。对于影响NULL处理的设置有如下几种：

一、ANSI_NULL_DEFT_ON 及ANSI_NULL_DEFT_OFF。

当该选项为OFF时，通过create table和alter table创建的新列默认为not null，对应的数据库选项为ANSI NULL默认值为false。当该选项为On时，则默认值为null，对应的数据库选项为ANSI NULL默认值为TRUE。

【测试1】

SET ANSI_NULL_DFLT_ON OFF

go

create table test1(id int,col char(10))

查看该表的属性，可以看到该表的两列不为空；

【测试2】

SET ANSI_NULL_DFLT_ON ON

go

create table test2(id int,col char(10))

查看该表的属性，可以看到该表的两列为空；

对于ANSI_NULL_DEFT_ON 及ANSI_NULL_DEFT_OFF是互斥选项，用于指明是否覆盖数据库选项，当开启一个选项时，都会迫使相反的那个选项关闭；但是关闭某个选项时，不会使相反的那个选项打开，而只是不再开启该选项。如果两个选项都关闭，那么将会启用数据库选项。

【测试3】

SET ANSI_NULL_DFLT_ON OFF

SET ANSI_NULL_DFLT_Off OFF

Go

create table tb(id int,col char(10))—ANSI NULL默认为false

查看该表的属性，可以看到该表的两列不允许为空；

【测试4】

SET ANSI_NULL_DFLT_ON OFF

SET ANSI_NULL_DFLT_Off OFF

Go

create table tb(id int,col char(10))—ANSI NULL默认为true

查看该表的属性，可以看到该表的两列允许为空；

二、SET CONCAT_NULL_YIELDS_NULL

当开启SET CONCAT_NULL_YIELDS_NULL时，如果串联操作的两个操作数中任意一个为null，则结果也为null；当关闭时，空值将按空字符串对待，默认为假。

【重要提示】：

在 SQL Server 的未来版本中，CONCAT_NULL_YIELDS_NULL 将始终为 ON，而且将该选项显式设置为 OFF 的任何应用程序都将产生一个错误。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。

【测试5】

SET CONCAT_NULL_YIELDS_NULL on

go

select 'abc'+null

结果：

Null

【测试6】

SET CONCAT_NULL_YIELDS_NULL off

go

select 'abc'+null

结果：

Abc

三、SET ANSI_NULLS

当 SET ANSI_NULLS 为 ON 时，所有对空值的比较均取值为 UNKNOWN。当 SET ANSI_NULLS 为 OFF 时，如果数据值为 NULL，则所有数据对空值的比较将取值为 TRUE。如果未指定，则应用当前数据库的 ANSI nulls 选项的设置。另外，当该选项为真是，必须使用is null条件来判断一个列是否包含null值；当这个选项为假时，sql server中=null等价于is null，<>null 等价于is not null。

【测试7】

SET ANSI_NULLS off

go

select * from test where col=null

结果

id   col

3           NULL

【测试8】

SET ANSI_NULLS on

go

select * from test where col=null

结果

四、SET ANSI_DEFAULTS

   默认为ON，当启用 (ON) 时，该选项将启用下列设置：

SET ANSI_NULLS                 SET CURSOR_CLOSE_ON_COMMIT

SET ANSI_NULL_DFLT_ON            SET IMPLICIT_TRANSACTIONS

SET ANSI_PADDING                       SET QUOTED_IDENTIFIER

SET ANSI_WARNINGS                   

【测试9】（建立数据库采用默认设置，并且没有自己改变过用户选项）

set ANSI_DEFAULTS on

GO

DBCC USEROPTIONS

结果：

Set Option

Value

textsize

2147483647

language

简体中文

dateformat

ymd

datefirst

7

lock_timeout

-1

quoted_identifier

SET

arithabort

SET

ansi_null_dflt_on

SET

ansi_warnings

SET

ansi_padding

SET

ansi_nulls

SET

concat_null_yields_null

SET

isolation level

read committed

【测试10】

set ANSI_DEFAULTS off

GO

DBCC USEROPTIONS

结果：

Set Option

Value

textsize

2147483647

language

简体中文

dateformat

ymd

datefirst

7

lock_timeout

-1

arithabort

SET

concat_null_yields_null

SET

isolation level

read committed

五、注意事项

1、当进行修改数据库选项后，发现貌似不起作用，原因在于当进行查询的时候，会启动用户的选项，而该用户选项会覆盖数据库选项。用户选项可以使用DBCC USEROPTIONS

查看，或者在【查询/查询选项】中查看。可以设置ansi_defaults为off状态（可以关闭某些选项），或者在【查询/查询选项】关闭某些选项。

2、在建立表的时候，建立在create table中显示的指定该列允许为空或者不为空，避免因为环境的设置而造成不必要的误解。

3、进行数据库编程的时候，最好显式的指定涉及到null的选项，来覆盖默认数据库选项的影响，或者避免不同的用户选项所带来的影响。