SET ANSI_NULLS的用法

Set Ansi_nulls是在對空值(NULL)的等於(=)或不等於(<>)的比較的操作運算符之前是否設置此運算符有效
Set Ansi nulls on 是無效(也是默認值)
Set Ansi nulls off 是有效
例如：

drop table T1

CREATE TABLE t1 (a int null)
INSERT INTO t1 values (NULL)
INSERT INTO t1 values (0)
INSERT INTO t1 values (1)
select * from T1
GO
-- 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 --查詢a欄位為空白值的記錄集
GO
-- 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 --查詢a欄位為空白值的記錄集
GO
-- 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 --查詢a欄位為空白值的記錄集
SELECT * FROM t1 WHERE a <> @varname --查詢a欄位不為空白值的記錄集
SELECT * FROM t1 WHERE a IS NULL --查詢a欄位為空白值的記錄集
GO
-- Drop table t1.
DROP TABLE t1
GO