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