ANSI_NULLS и QUOTED_IDENTIFIER убили вещи. Для чего они?

Ответ 1

ОК, с точки зрения разработчика приложений, вот что делают эти настройки:

QUOTED_IDENTIFIER

Этот параметр определяет, как кавычки ".." интерпретируются компилятором SQL. Когда QUOTED_IDENTIFIER ВКЛЮЧЕН, кавычки обрабатываются как скобки ([...]) и могут использоваться для цитирования имен объектов SQL, таких как имена таблиц, имена столбцов и т.д. Когда он выключен (не рекомендуется), тогда кавычки обрабатываются как апострофы ('..') и может использоваться для цитирования текстовых строк в командах SQL.

ANSI_NULLS

Этот параметр определяет, что происходит, когда вы пытаетесь использовать любой оператор сравнения, отличный от IS в NULL. Когда он включен, эти сравнения следуют стандарту, в котором говорится, что сравнение с NULL всегда терпит неудачу (потому что это не значение, это флаг) и возвращает FALSE. Если этот параметр выключен (рекомендуется не), вы можете обработать его как значение и использовать =, <> и т.д. На нем и вернуть TRUE в качестве подходящего.

Правильный способ справиться с этим - вместо этого использовать IS (ColumnValue IS NULL ..).

CONCAT_NULL_YIELDS_NULL

Этот параметр определяет, используются ли NULL "Проповеди", которые используются в строковых выражениях. Когда этот параметр включен, он соответствует стандарту, а выражение типа 'some string' + NULL .. всегда возвращает NULL. Таким образом, в серии конкатенаций строк один NULL может заставить целое выражение возвращать NULL. Выключение этого параметра (также, не рекомендуется) приведет к тому, что NULL будут обрабатываться как пустые строки, поэтому 'some string' + NULL просто оценивает 'some string'.

Правильный способ справиться с этим - с функцией COALESCE (или ISNULL): 'some string' + COALESCE(NULL, '') ...

Ответ 2

Я думаю, что при восстановлении индексов он отключился.

Проверьте параметры SET с их значениями параметров, необходимыми при работе с фильтрованным индексом

Вам нужно включить параметр ниже при работе с фильтрованным индексом:

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON

Вам нужно добавить, чтобы добавить

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

для всех моих хранимых процедур, редактирующих таблицу с вычисленным столбцом, чтобы избежать этой ошибки.

ANSI_NULLS:

Когда SET ANSI_NULLS ВКЛЮЧЕН, оператор SELECT, который использует WHERE column_name = NULL возвращает нулевые строки, даже если в column_name. Оператор SELECT, который использует WHERE column_name < > NULL возвращает нулевые строки, даже если в столбце есть ненулевые значения.

Когда SET ANSI_NULLS выключен, значения Equals (=) и Not Equal To (< > ) операторы сравнения не соответствуют стандарту ISO. ВЫБОР, который использует WHERE column_name = NULL, возвращает строки, которые имеют значения null в имени_ column_name. Оператор SELECT, который использует WHERE column_name < > NULL возвращает строки с ненулевыми значениями в колонка. Кроме того, оператор SELECT, который использует WHERE column_name < > XYZ_value возвращает все строки, которые не являются XYZ_value, и которые не являются NULL.

QUOTED_IDENTIFIER

Когда SET QUOTED_IDENTIFIER включен, идентификаторы можно разграничить двойные кавычки и литералы должны быть разделены одним кавычки. Когда SET QUOTED_IDENTIFIER выключен, идентификаторы не могут цитироваться и должны следовать всем правилам Transact-SQL для идентификаторов. Для дополнительную информацию см. в разделе Идентификаторы базы данных. Литералы можно разграничить либо одиночными, либо двойными кавычками.

Когда SET QUOTED_IDENTIFIER включен (по умолчанию), все строки, разделенные символом двойные кавычки интерпретируются как идентификаторы объектов. Поэтому цитируемые идентификаторы не должны следовать Transact-SQL правила для идентификаторов. Они могут быть зарезервированными ключевыми словами и могут включать символы, обычно не разрешенные в идентификаторах Transact-SQL. двойной кавычки не могут использоваться для разграничения литералов; одинарные кавычки должны использоваться, чтобы заключать литеральные строки. Если одинарная кавычка (') является частью литеральной строки, она может быть представленной двумя одиночными кавычками ("). SET QUOTED_IDENTIFIER должен быть включен, когда зарезервированные ключевые слова используются для имен объектов в базы данных.

CONCAT_NULL_YIELDS_NULL

Когда SET CONCAT_NULL_YIELDS_NULL включен, объединение нулевого значения с строкой дает результат NULL. Например, SELECT 'abc' + NULL дает NULL. Когда SET CONCAT_NULL_YIELDS_NULL выключено, объединение контура null значение со строкой приводит к самой строке (нулевое значение равно рассматривается как пустая строка). Например, SELECT 'abc' + NULL дает а.

Если SET CONCAT_NULL_YIELDS_NULL не указан, настройка Используется опция базы данных CONCAT_NULL_YIELDS_NULL.

Ответ 3

Я нахожу документацию, сообщения в блоге, fooobar.com/questions/542927/... бесполезно, объясняя, что означает QUOTED_IDENTIFIER.

Старые времена

Первоначально SQL Server разрешил использовать кавычки ("...") и апострофы ('...') вокруг строк взаимозаменяемо (например, Javascript):

• SELECT "Hello, world!" - знак-метка
• SELECT 'Hello, world!' --apostrophe

И если вам нужна таблица имен, просмотр, процедура, столбец и т.д. с чем-то, что в противном случае нарушало бы все правила именования объектов, вы могли бы заключить их в квадратные скобки ([, ]):

CREATE TABLE [The world most awful table name] (
   [Hello, world!] int
)

SELECT [Hello, world!] FROM [The world most awful table name]

И все это работало и имело смысл.

Затем появился ANSI

Затем ANSI пришел и имел другие идеи:

• если у вас есть фанковое имя, оберните его в кавычки ("...")
• используйте apostrophe ('...') для строк
• и мы даже не заботимся о ваших квадратных скобках

Это означает, что если вы хотите "процитировать" фанковый столбец или имя таблицы, вы должны использовать кавычки:

SELECT "Hello, world!" FROM "The world most awful table name"

Если вы знали SQL Server, вы знали, что кавычки уже используются для представления строк. Если вы слепо попытались выполнить этот ANSI-SQL, как если бы это был T-SQL: это вздор, и SQL Server сказал вам об этом:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'The world most awful table name'.

Вы должны выбрать новое поведение ANSI

Итак, Microsoft добавила функцию, позволяющую вам отказаться от SQL-кода SQL.

Оригинал

SELECT "Hello, world!" --valid
SELECT 'Hello, world!' --valid

SET QUOTED_IDENTIFIER ON

SELECT "Hello, world!" --INVALID
SELECT 'Hello, world!' --valid

В наши дни у всех есть SET QUOTED_IDENTIFIERS ON, что технически означает, что вы должны использовать quotes, а не square brackets вокруг идентификаторов:

T-SQL (плохо?) (например, SQL, сгенерированный Entity Framework)

UPDATE [dbo].[Customers]
SET [FirstName] = N'Ian'
WHERE [CustomerID] = 7

ANSI-SQL (хорошо?)

UPDATE "dbo"."Customers"
SET "FirstName" = N'Ian'
WHERE "CustomerID" = 7

Ответ 4

ANSI_NULLS ON делает любое двоичное логическое выражение с нулевым значением равным false. Используя следующий шаблон:

declare @varA, @varB int

if <binary boolean expression>
begin
    print 'true'
end
else
begin
    print 'false'
end


@varA: NULL; @varB: NULL; @varA = @varB evaluates to false
@varA: 1; @varB: NULL; @varA <> @varB evaluates to false

Правильный способ проверить значение null - использовать is [not] NULL

@varA: NULL; @varA is NULL evaluates to true
@varA: 1; @varA is not NULL evaluates to true

QUOTED_IDENTIFER ON просто позволяет использовать двойные кавычки для разграничения идентификаторов (плохая идея IMO, только квадратные скобки пользователя)

from tblA "a" -- ok when ON, not ok when OFF
from tblA [a] -- always ok