SET ANSI_NULLS ON
来源:互联网 发布:php求数组最大值 编辑:程序博客网 时间:2024/04/30 00:43
指定在与 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)
- SET ANSI_NULLS ON
- SET ANSI_NULLS ON
- SET ANSI_NULLS ON
- SET ANSI_NULLS ON
- SET ANSI_NULLS ON
- Set ANSI_NULLS ; SET Quoted_Identifier ON
- SET ANSI_NULLS ON的作用
- set ansi_nulls on的用法
- SET ANSI_NULLS ON的作用
- SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON
- SET ANSI_NULLS ON与SET QUOTED_IDENTIFIER ON
- SET ANSI_NULLS ON 与 SET QUOTED_IDENTIFIER ON
- SET ANSI_NULLS ON,SET QUOTED_IDENTIFIER ON
- SET QUOTED_IDENTIFIER ON和SET ANSI_NULLS ON
- set ansi_nulls on or set quoted_identifier on
- SET ANSI_NULLS ON、SET NOCOUNT ON
- SET ANSI_NULLS ON和SET QUOTED_IDENTIFIER ON,SET ANSI_PADDING
- sql生成脚本里SET ANSI_NULLS ON
- 排序算法总结(三)插入排序
- 关于获取上传图片的长和宽
- 被高中生狂虐跟看完IT牛人榜后的随感
- toj1528 Least Common Multiple
- MySQL对于有大量重复数据表的处理方法
- SET ANSI_NULLS ON
- Maven 使用
- 谈谈找工作过程中的那些环节、注意点和经验
- MySql,SqlServer,Oracle链接数据库工具类
- java各常用集合类是否接受null值
- jQuery formValidator表单验证插件示例
- IE6 css fixed
- Regionals 2012, Asia - Hatyai 组队赛130912
- matlab基础编程(1)-----二维数组中不同的统计算法