在SQL Server中，“设置ANSI_NULLS”是什么意思?

The definition says:

定义说:

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

当设置ANSI_NULLS为ON时，使用column_name = NULL的SELECT语句返回零行，即使column_name中有空值。使用column_name <> NULL的SELECT语句返回零行，即使column_name中有非空值。

Does this mean that no nulls will be included in this query?

这是否意味着在这个查询中不会包含null ?

SELECT EmployeeID, LastName, FirstName, Region FROM employees
WHERE Region=@region

Or do ANSI_NULLs concern only queries like this one (where the WHERE includes the specific word null)?

或者ANSI_NULLs只涉及像这样的查询(where中包含特定的词null)?

SELECT EmployeeID, LastName, FirstName, Region FROM employees
WHERE Region=null

7 个解决方案

#1

It means that no rows will be returned if @region is NULL, when used in your first example, even if there are rows in the table where Region is NULL.

这意味着，如果在第一个示例中使用@region为NULL，那么就不会返回任何行，即使表中区域为NULL的行也没有。

When ANSI_NULLS is on (which you should always set on anyway, since the option to not have it on is going to be removed in the future), any comparison operation where (at least) one of the operands is NULL produces the third logic value - UNKNOWN (as opposed to TRUE and FALSE).

当ANSI_NULLS(你应该设置在无论如何,自从选项没有它在未来将被删除),任何比较操作(至少)的一个操作数为空产生第三逻辑价值——未知的(而不是真假)。

UNKNOWN values propagate through any combining boolean operators if they're not already decided (e.g. AND with a FALSE operand or OR with a TRUE operand) or negations (NOT).

未知的值通过任何组合的布尔运算符传播，如果它们还没有决定(例如，使用错误的操作数或有一个真正的操作数)或否定(不是)。

The WHERE clause is used to filter the result set produced by the FROM clause, such that the overall value of the WHERE clause must be TRUE for the row to not be filtered out. So, if an UNKNOWN is produced by any comparison, it will cause the row to be filtered out.

WHERE子句用于过滤FROM子句产生的结果集，这样，WHERE子句的总值必须为TRUE，以便不被过滤掉。因此，如果通过任何比较产生一个未知，它将导致行被过滤掉。

---

@user1227804's answer includes this quote:

@user1227804的回答包括以下引用:

If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

如果比较的两边都是列或复合表达式，则设置不影响比较。

from SET ANSI_NULLS^*

从设置ANSI_NULLS *

However, I'm not sure what point it's trying to make, since if two NULL columns are compared (e.g. in a JOIN), the comparison still fails:

但是，我不确定它的目的是什么，因为如果比较两个空列(例如在连接中)，比较仍然失败:

create table #T1 (
    ID int not null,
    Val1 varchar(10) null
)
insert into #T1(ID,Val1) select 1,null

create table #T2 (
    ID int not null,
    Val1 varchar(10) null
)
insert into #T2(ID,Val1) select 1,null

select * from #T1 t1 inner join #T2 t2 on t1.ID = t2.ID and t1.Val1 = t2.Val1

The above query returns 0 rows, whereas:

上面的查询返回0行，而:

select * from #T1 t1 inner join #T2 t2 on t1.ID = t2.ID and (t1.Val1 = t2.Val1 or t1.Val1 is null and t2.Val1 is null)

Returns one row. So even when both operands are columns, NULL does not equal NULL. And the documentation for = doesn't have anything to say about the operands:

返回一行。所以即使两个操作数都是列，NULL也不等于NULL。而=的文档中没有任何关于操作数的内容:

When you compare two NULL expressions, the result depends on the ANSI_NULLS setting:

当比较两个空表达式时，结果取决于ANSI_NULLS设置:

If ANSI_NULLS is set to ON, the result is NULL¹, following the ANSI convention that a NULL (or unknown) value is not equal to another NULL or unknown value.

如果ANSI_NULLS设置为ON，则结果为NULL1，遵循ANSI约定，NULL(或未知)值不等于另一个NULL或未知值。

If ANSI_NULLS is set to OFF, the result of NULL compared to NULL is TRUE.

如果ANSI_NULLS被设置为OFF，那么与NULL比较的NULL的结果为TRUE。

Comparing NULL to a non-NULL value always results in FALSE².

将NULL值与非空值进行比较总是会得到FALSE2。

However, both ¹ and ² are incorrect - the result of both comparisons is UNKNOWN.

然而，1和2都是不正确的-这两个比较的结果是未知的。

---

^*The cryptic meaning of this text was finally discovered years later. What it actually means is that, for those comparisons, the setting has no effect and it always acts as if the setting were ON. Would have been clearer if it had stated that SET ANSI_NULLS OFF was the setting that had no affect.

*这段文字的神秘含义最终在数年后被发现。它实际上的意思是，对于这些比较来说，设置没有影响，它的作用就好像设置是打开的一样。如果它声明设置ANSI_NULLS为无效的设置，那就更清楚了。

#2

SET QUOTED_IDENTIFIER ON/OFF

设置QUOTED_IDENTIFIER开/关

It specifies how SQL Server treats the data that is defined in Single Quotes and Double Quotes.

它指定SQL Server如何处理用单引号和双引号定义的数据。

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)

当它被设置为在双引号中定义的任何字符集”“被当作一个t - sql标识符(表名、Proc名列名....等)

When any character set that is defined in the single quotes ‘’ is treated as a literal.

当在单引号中定义的任何字符集都被视为文字。

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

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
CREATE TABLE "SELECT"(“TABLE” int) -- FAIL
GO

SET QUOTED_IDENTIFIER OFF
SELECT "sometext" AS Value    -- SUCCESS as “sometext” is treated litral

--The default behavior is ON in any database.

SET ANSI_NULLS ON/OFF:

设置ANSI_NULLS开/关:

The ANSI_NULLS option specifies that how SQL Server handles the comparison operations with NULL values.

ANSI_NULLS选项指定SQL Server如何处理带有空值的比较操作。

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.

当它被设置为使用=和<>与NULL进行比较时，就会产生false值。这就是ISO定义的标准行为。为了和我们需要使用的空值进行比较，它是空的而且不是空的。

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.

当它被设置为使用=和<>进行任何与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.

#3

If @Region is not a null value (lets say @Region = 'South') it will not return rows where the Region field is null, regardless of the value of ANSI_NULLS.

如果@Region不是空值(假设@Region = 'South')，它将不会返回区域字段为空的行，无论ANSI_NULLS的值是多少。

ANSI_NULLS will only make a difference when the value of @Region is null, i.e. when your first query essentially becomes the second one.

ANSI_NULLS只会在@Region的值为null时产生影响，也就是说，当您的第一个查询实质上成为第二个查询时。

In that case, ANSI_NULLS ON will not return any rows (because null = null will yield an unknown boolean value (a.k.a. null)) and ANSI_NULLS OFF will return any rows where the Region field is null (because null = null will yield true)

在这种情况下，ANSI_NULLS将不会返回任何行(因为null = null将产生一个未知的布尔值(a.k.a null))，而ANSI_NULLS将返回区域字段为null的任何行(因为null = null将返回true)

#4

Set ANSI NULLS OFF will make NULL = NULL comparision return true. EG :

设置ANSI NULLS将使NULL = NULL comparision返回true。例如:

        SET ANSI_NULLS OFF
        select * from sys.tables
        where principal_id = Null

will return some result as displayed below: zcwInvoiceDeliveryType 744547 NULL zcExpenseRptStatusTrack 2099048 NULL ZCVendorPermissions 2840564 NULL ZCWOrgLevelClientFee 4322525 NULL

将返回如下所示的一些结果:zcwInvoiceDeliveryType 744547 NULL zcexpenserptcontrollstrack 2099048 NULL ZCVendorPermissions 2840564 NULL ZCWOrgLevelClientFee 4322525无效

While this query will not return any results:

虽然该查询不会返回任何结果:

        SET ANSI_NULLS ON 
        select * from sys.tables
        where principal_id = Null

#5

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

当设置ANSI_NULLS为ON时，使用column_name = NULL的SELECT语句返回零行，即使column_name中有空值。使用column_name <> NULL的SELECT语句返回零行，即使column_name中有非空值。

For e.g

对如

DECLARE @TempVariable VARCHAR(10)
SET @TempVariable = NULL

SET ANSI_NULLS ON
SELECT 'NO ROWS IF SET ANSI_NULLS ON' where    @TempVariable = NULL
-- IF ANSI_NULLS ON , RETURNS ZERO ROWS


SET ANSI_NULLS OFF
SELECT 'THERE WILL BE A ROW IF ANSI_NULLS OFF' where    @TempVariable =NULL
-- IF ANSI_NULLS OFF , THERE WILL BE ROW !

#6

SET ANSI_NULLS ON

设置ANSI_NULLS

IT Returns all values including null values in the table

它返回所有值，包括表中的空值。

SET ANSI_NULLS off

使ANSI_NULLS

it Ends when columns contains null values

当列包含空值时结束

#7

If ANSI_NULLS is set to "ON" and if we apply = , <> on NULL column value while writing select statement then it will not return any result .

如果ANSI_NULLS被设置为“ON”，如果我们在写入select语句时对空列值应用=，<>，那么它将不会返回任何结果。

Example

例子

create table #tempTable (sn int, ename varchar(50))

创建表#tempTable (sn int, ename varchar(50))

insert into #tempTable

插入#易被诱惑的

select 1, 'Manoj'

选择1,“Manoj”

UNION ALL

UNION ALL

select 2, 'Pankaj'

选择2,“Pankaj”

UNION ALL

UNION ALL

select 3, NULL

选择3,零

UNION ALL

UNION ALL

select 4, 'Lokesh'

选择4,“Lokesh”

UNION ALL

UNION ALL

select 5, 'Gopal'

选择5,“·”

SET ANSI_NULLS ON

设置ANSI_NULLS

select * from #tempTable where ename is NULL -- (1 row(s) affected)

从ename为NULL的#tempTable中选择*(受影响的一行)

select * from #tempTable where ename = NULL -- (0 row(s) affected)

从#tempTable中选择*，其中ename = NULL -(0行(s)受影响)

select * from #tempTable where ename <> NULL -- (0 row(s) affected)

从#tempTable中选择*，其中ename <> NULL——(0行(s)受影响)

SET ANSI_NULLS OFF

使ANSI_NULLS

select * from #tempTable where ename is NULL -- (1 row(s) affected)

从ename为NULL的#tempTable中选择*(受影响的一行)

select * from #tempTable where ename = NULL -- (1 row(s) affected)

从#tempTable中选择*，其中ename = NULL -(受影响的一行)

select * from #tempTable where ename is not NULL -- (4 row(s) affected)

从ename不为空的#tempTable中选择*—(受影响的4行)

select * from #tempTable where ename <> NULL -- (4 row(s) affected)

从#tempTable中选择*，其中ename <> NULL——(影响4行)