sql serverSET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON 什么意思 sql server 2005 2008

原文：http://www.cnblogs.com/ForFreeDom/archive/2009/10/16/1584680.html

在sqlserver2005或SQL2008数据库项目中，创建视图或存储过程的时候往往有以下开头语句：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ……………… ……………… GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO

　　

这些是 SQL-92 设置语句，使 SQL Server 2000/2005 遵从 SQL-92 规则。 
当 SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔。当 SET QUOTED_IDENTIFIER 为 OFF 时，标识符不可加引号，且必须符合所有 Transact-SQL 标识符规则。 
SQL-92 标准要求在对空值进行等于 (=) 或不等于 (<>) 比较时取值为 FALSE。当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。 
当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵从 SQL-92 标准。使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行。

SQL Server联机丛书关于ANSI_NULLS里有这样一个代码示例，给出了直观的效果，我添加了输出提示，如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58

-- Create table t1 and insert values. CREATE TABLE t1 (a INT NULL) INSERT INTO t1 values (NULL) INSERT INTO t1 values (0) INSERT INTO t1 values (1) 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        --结果为NULL 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        --结果为NULL 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     --结果为NULL SELECT * FROM t1 WHERE a <> @varname    --结果为0，1 SELECT * FROM t1 WHERE a IS NULL        --结果为NULL GO -- Drop table t1. DROP TABLE t1

　　从上面代码的运行效果来看：

①当SET ANSI_NULLS ON时， =null或 <> null的查询条件时，结果都为空；如果为 IS NULL 才能显示NULL的行。
②不对ANSI_NULLS进行设置时，其执行效果与SET ANSI_NULLS ON时的效果一致，此处与SQL-92的要求之一“SQL-92 标准要求对空值的等于 (=) 或不等于 (<>) 比较取值为 FALSE，是默认值。”冲突哦？！
又怎么理解这个问题呢？
SQL Server联机丛书里ANSI_NULLS的知识文档里有答案：“SQL Server ODBC 驱动程序和用于 SQL Server 的 Microsoft OLE DB 提供程序在连接时自动将 ANSI_NULLS 设置为 ON。该设置可以在 ODBC 数据源、ODBC 连接特性或 OLE DB 连接属性（它们在连接到 SQL Server 之前在应用程序中设置）中进行配置。”

PS：其实，实际使用过程中，我们并没有考虑ANSI_NULLS是被设置为ON还是False,我们对空值的处理都是IS NULL 或者 IS NOT NULL

分类: 疆

标签: sqlserver