ANSI_NULLS 和 QUOTED_IDENTIFIER 杀死了一些东西.它们是为了什么? [英] ANSI_NULLS and QUOTED_IDENTIFIER killed things. What are they for?

问题描述

注意:我检查了 了解 QUOTED_IDENTIFIER，但它没有回答我的问题.

NOTE: I checked Understanding QUOTED_IDENTIFIER and it does not answer my question.

我让 DBA 运行我在 Prod 服务器上创建的索引(他们查看并批准了它).

I got my DBAs to run an index I made on my Prod servers (they looked it over and approved it).

它像我想要的那样加快了我的查询速度.但是，我开始收到这样的错误:

It sped up my queries just like I wanted. However, I started getting errors like this:

作为开发人员，我通常会忽略这些设置.它从来都不重要.(超过 9 年).嗯，今天很重要.

As a developer I have usually ignored these settings. And it has never mattered. (For 9+ years). Well, today it matters.

我去查看了一个失败的 sproc，它在为 sproc 创建之前有这个:

I went and looked at one of the sprocs that are failing and it has this before the create for the sproc:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

谁能从应用程序开发人员的角度告诉我这些 set 语句的作用?(仅在我的 index create 语句之前添加上述代码并不能解决问题.)

Can anyone tell me from a application developer point of view what these set statements do? (Just adding the above code before my index create statements did not fix the problem.)

注意:以下是我的索引的示例:

NOTE: Here is an example of what my indexes looked like:

CREATE NONCLUSTERED INDEX [ix_ClientFilerTo0]
ON [ClientTable] ([Client])
INCLUDE ([ClientCol1],[ClientCol2],[ClientCol3] ... Many more columns)
WHERE Client = 0


CREATE NONCLUSTERED INDEX [IX_Client_Status]
ON [OrderTable] ([Client],[Status])
INCLUDE ([OrderCol1],[OrderCol2],[OrderCol3],[OrderCol4])
WHERE [Status] <= 7
GO

推荐答案

好的，从应用程序开发人员的角度来看，以下是这些设置的作用:

OK, from an application developer's point of view, here's what these settings do:

此设置控制 SQL 编译器如何解释引号 "..".当 QUOTED_IDENTIFIER 为 ON 时，引号被视为方括号 ([...])，可用于引用 SQL 对象名称，如表名、列名等.当它是关闭的(不推荐)，然后引号被视为撇号 ('..')，可用于在 SQL 命令中引用文本字符串.

This setting controls how quotation marks ".." are interpreted by the SQL compiler. When QUOTED_IDENTIFIER is ON then quotes are treated like brackets ([...]) and can be used to quote SQL object names like table names, column names, etc. When it is OFF (not recommended), then quotes are treated like apostrophes ('..') and can be used to quote text strings in SQL commands.

此设置控制当您尝试在 NULL 上使用除 IS 之外的任何比较运算符时会发生什么.当它为 ON 时，这些比较遵循标准，即与 NULL 比较总是失败(因为它不是一个值，它是一个标志)并返回 FALSE.当此设置关闭时(确实不推荐)您可以成功地将其视为一个值并使用 =, <> 等，并在适当时返回 TRUE.

This setting controls what happens when you try to use any comparison operator other than IS on NULL. When it is ON, these comparisons follow the standard which says that comparing to NULL always fails (because it isn't a value, it's a Flag) and returns FALSE. When this setting is OFF (really not recommended) you can sucessfully treat it like a value and use =, <>, etc. on it and get back TRUE as appropiate.

处理此问题的正确方法是改用 IS (ColumnValue IS NULL ..).

The proper way to handle this is to instead use the IS (ColumnValue IS NULL ..).

此设置控制字符串表达式中使用的 NULL 是否传播".当此设置为 ON 时，它遵循标准并且像 'some string' + NULL .. 这样的表达式总是返回 NULL.因此，在一系列字符串连接中，一个 NULL 可以导致整个表达式返回 NULL.关闭此选项(同样，不推荐)将导致 NULL 被视为空字符串，因此 'some string' + NULL 仅计算为 'some string'.

This setting controls whether NULLs "Propogate" whn used in string expressions. When this setting is ON, it follows the standard and an expression like 'some string' + NULL .. always returns NULL. Thus, in a series of string concatenations, one NULL can cause the whole expression to return NULL. Turning this OFF (also, not recommended) will cause the NULLs to be treated like empty strings instead, so 'some string' + NULL just evaluates to 'some string'.

处理这个问题的正确方法是使用 COALESCE(或 ISNULL)函数:'some string' + COALESCE(NULL, '') ...

The proper way to handle this is with the COALESCE (or ISNULL) function: 'some string' + COALESCE(NULL, '') ...

这篇关于ANSI_NULLS 和 QUOTED_IDENTIFIER 杀死了一些东西.它们是为了什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！