SET ANSI_NULLS ON

指定在與 Null 值一起使用等于 (=) 和不等于 (<>) 比較運算符時采用符合 ISO 標準的行為。

當 SET ANSI_NULLS 為 ON 時，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 語句仍返回零行。即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 語句仍會返回零行。

當 SET ANSI_NULLS 為 OFF 時，等于 (=) 和不等于 (<>) 比較運算符不遵守 ISO 標準。使用 WHERE column_name = NULL 的 SELECT 語句返回 column_name 中包含空值的行。使用 WHERE column_name <> NULL 的 SELECT 語句返回列中包含非空值的行。

?

例子：

?-- Create table t1 and insert values.
CREATE TABLE t1 (a INT NULL,b VARCHAR(40))
INSERT INTO t1 values (NULL,'HH')
INSERT INTO t1 values (0,'LL')
INSERT INTO t1 values (1,'OO')
GO

1、

-- Print message and perform SELECT statements.
PRINT 'Testing default setting';
DECLARE @varname int;
SELECT @varname = NULL;
SELECT * FROM t1 WHERE a = @varname;
SELECT * FROM t1 WHERE a <> @varname;
SELECT * FROM t1 WHERE a IS NULL;
GO

輸出：

?

Testing default setting
a?????????? b
----------- ----------------------------------------
NULL??????? HH

(1 row(s) affected)

a?????????? b
----------- ----------------------------------------
0?????????? LL
1?????????? OO

(2 row(s) affected)

a?????????? b
----------- ----------------------------------------
NULL??????? HH

(1 row(s) affected)

?

2、

-- SET ANSI_NULLS to ON and test.
PRINT 'Testing ANSI_NULLS ON'
SET ANSI_NULLS ON
GO
DECLARE @varname int;
SELECT @varname = NULL;
SELECT * FROM t1 WHERE a = @varname;
SELECT * FROM t1 WHERE a <> @varname;
SELECT * FROM t1 WHERE a IS NULL;
GO

輸出：

?

Testing ANSI_NULLS ON
a?????????? b
----------- ----------------------------------------

(0 row(s) affected)

a?????????? b
----------- ----------------------------------------

(0 row(s) affected)

a?????????? b
----------- ----------------------------------------
NULL??????? HH

(1 row(s) affected)

?

3、

-- SET ANSI_NULLS to OFF and test.
PRINT 'Testing SET ANSI_NULLS OFF';
SET ANSI_NULLS OFF;
GO
DECLARE @varname int;
SELECT @varname = NULL;
SELECT * FROM t1 WHERE a = @varname;
SELECT * FROM t1 WHERE a <> @varname;
SELECT * FROM t1 WHERE a IS NULL;
GO

輸出：

?

Testing SET ANSI_NULLS OFF
a?????????? b
----------- ----------------------------------------
NULL??????? HH

(1 row(s) affected)

a?????????? b
----------- ----------------------------------------
0?????????? LL
1?????????? OO

(2 row(s) affected)

a?????????? b
----------- ----------------------------------------
NULL??????? HH

(1 row(s) affected)