Understanding SET QUOTED_IDENTIFIER ON/OFF and SET ANSI_NULLS ON/OFF

Written by Ranjith in SQL Server

These are the two widely used SET options in SQL Server. Most developers explicitly set these options while creating Stored Procedures, Triggers and User Defined Functions but many are unclear on why we need to explicitly SET them? And why they are special compared to other options?

这两个SET 选项是在SQL SERVER中被广泛使用的选项。大多数的开发人员都会在创建存储过程，触发器，和自定义函数时，明确设置这些选项，但是还是有很多人不清楚我们为什么要明确设置这些选项？并且也不清楚这些选择和其他的选项比，有什么特别之处？

Below is the typical usage of these options.

下面是这些选项的典型用法

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE SampleProcedure
AS
BEGIN
 -- select employees
 SELECT * FROM HumanResources.Employee
END

Lets first understand what they exactly mean to SQL Server and then we will move on to why they are special.

首先让我们理解这些选项在SQL SERVER中准确的含义，然后我们再看下这些选项的特别之处。

SET QUOTED_IDENTIFIER ON/OFF:

It specifies how SQL Server treats the data that is defined in Single Quotes and Double Quotes. When it is set to ON any character set that is defined in the double quotes “” is treated as a T-SQL Identifier (Table Name, Proc Name, Column Name….etc) and the T-SQL rules for naming identifiers will not be applicable to it. And any character set that is defined in the Single Quotes ‘’ is treated as a literal.

SET QUOTED_IDENTIFIER选项，指明了SQL SERVER应该如果对待定义在单引号中间，和双引号中间的数据；

当这个选项被设置成ON时，任何被定义在双引号中间的字符都被当做T-SQL的标识符（如：表名，过程名，列名...等），

在使用了双引号括起来的标识符不会受到T-SQL的标识符命名的限制。

在SET QUOTED_IDENTIFIER为ON时，任何被定义单单引号中间的字符，都会被当做一个字符串。

 

SET QUOTED_IDENTIFIER ON CREATE TABLE "SELECT" ("TABLE" int) -- SUCCESS GO（可以成功执行）

SET QUOTED_IDENTIFIER ON SELECT "sometext" AS Value -- FAIL because “sometext” is not a literal（这个执行会失败）

 

Though the “SELECT” and “TABLE” are reserved keywords  we are able to create the table because they are now treated as identifiers and the T SQL rules for identifier names are ignored.

尽管SELECT 和TABLE是保留的关键字，但是我们是可以创建这样的表的，因为它们现在是被当做标识符（如表名，过程名等），并且这时T-SQL的标识符命名规则是被忽略的（即，执行时不会报标识符不合法的错误）。

When it is set to OFF any character set that is defined either in Single Quotes or in Double Quotes is treated as a literal.

当将SET QUOTED_IDENTIFIER选项设置为OFF时，任何定义在单引号或者是双引号中间的字符都会被当做字符串看待。

 

SET QUOTED_IDENTIFIER OFF CREATE TABLE "SELECT"(“TABLE” int) -- FAIL GO（这个执行失败）

SET QUOTED_IDENTIFIER OFF SELECT "sometext" AS Value -- SUCCESS as “sometext” is treated as a literal（这个执行成功）

 

You can clearly see the difference in CREATE TABLE and SELECT query. Here the CREATE TABLE fails because “SELECT” is a reserved keyword and it is considered as a literal. The default behavior is ON in any database.

你能清楚看出CREATE TABLE和SELECT 查询之间的不同，这里CREATE TABLE失败是因为SELECT 是一个保留关键字，并且他也被当做是一个字符串。默认在任何数据库上这个选项默认都是ON的（这个实践中未必是这样，这里说的不对）。

SET ANSI_NULLS ON/OFF:

The ANSI_NULLS option specifies that how SQL Server handles the comparison operations with NULL values. When it is set to ON any comparison with NULL using = and <> will yield to false value. And it is the ISO defined standard behavior. So to do the comparison with NULL values we need to use IS NULL and IS NOT NULL. And when it is set to OFF any comparison with NULL using = and <> will work as usual i.e. NULL = NULL returns true and 1= NULL returns false.

ANSI_NULLS选项指明了SQL SERVER如何处理和NULL值的比对操作。

当这个选项被设置为ON时，任何用“=”“<>”操作符和NULL值做比对，都会返回false值。这个行为是ISO定义的标准行为。所以如果我们想要是不是NULL，需要使用IS NULL或者IS NOT NULL进行。

当ANSI_NULLS选项被设置成OFF时，所有用=，<>操作符和NULL值做比较时，就和一般的情形一致，即：NULL=NULL返回true,而1=NULL返回false.

示例代码如下：

SET ANSI_NULLS ON
IF NULL = NULL
 PRINT 'same'
ELSE
 PRINT 'different'
--result:  different

SET ANSI_NULLS ON
IF NULL IS NULL
 PRINT 'same'
ELSE
 PRINT 'different'
-- result: same

SET ANSI_NULLS OFF
IF NULL = NULL
 PRINT 'same'
ELSE
 PRINT 'different'
--result:  same (now NULL = NULL works as 1=1)

The default behavior is ON in any database. As per BOL 2008 this option will always be set to ON in the future releases of SQL Server and any explicit SET to OFF will result an error. So avoid explicitly setting this option in future development work.

在任何数据库上这个选项默认为ON（这里说的未必正确，具体以实践为准），按照SQL SERVER2008的在线书籍（BOL =Book Online）这个选项将来会只允许被设置为ON，如果有任何通过SET尝试设置为OFF的操作，都会导致一个错误。

所以，在未来的开发工作中要避免明确设置这个选项的值。

 

Why are these two options Special?

为什么这两个选项是特别的？

These two SET options are special because whenever a stored procedure or a Trigger or a User Defined Function is created or modified with these options explicitly SET; SQL Server remembers those settings in the associated object metadata. And every time the object (stored procedure,Trigger..etc.) is executed SQL server uses the stored settings irrespective of what the current user session settings are. So the behavior of the stored procedure is not altered by the calling session settings and the usage of the SET option behavior inside the SP is always guaranteed.

这两个选项会在存储过程，触发器，自定义函数中记录这些选项的值；从而忽略数据库会话中其他的设置值；

所以如果一个存储过程，触发器，或者函数如果定义了选项值，那么即便在会话的其他地方设置了一个不同的值，那么在

执行这些存储过程，触发器，函数的时候也不会使用这些不同值，依然会使用它们自身存储的值。

You can get any procedure or trigger or function settings for these options from the sys..sql_modules metadata table.

你可以通过下面的语句查询出某个过程，触发器，或者函数存储的两个选项的值。

SELECT uses_ansi_nulls, uses_quoted_identifier
 FROM sys.sql_modules WHERE object_id = object_id('SampleProcedure')

And if you need to guarantee the behavior for other SET options like SET ARITHABORT inside the SP then you need to SET them inside the procedure. The scope of the options specified inside the procedure are only applicable until the procedure completes its execution.

然而，如果你想要保证其他的SET选项在你SP中的值，那么只需要在这个SP的里面设置这个选项就可以了，比如：ARITHABORT选项，一旦这个过程执行完了，这些选项就会恢复为默认值。

Hope it helps.

– Ranjith