Set quoted identifier on что это в sql

SET QUOTED_IDENTIFIER (Transact-SQL)

Приводит к тому, что SQL Server следует правилам ISO относительно идентификаторов с разделителями кавычек и строк литерала. Идентификаторы, заключенные в двойные кавычки, могут быть либо зарезервированными ключевыми словами Transact-SQL, либо могут содержать символы, обычно запрещенные правилами синтаксиса Transact-SQL для идентификаторов.

Синтаксис

-- Syntax for SQL Server, Azure SQL Database, serverless SQL pool in Azure Synapse Analytics, and Microsoft Fabric SET QUOTED_IDENTIFIER

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse SET QUOTED_IDENTIFIER ON

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Замечания

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

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

QUOTED_IDENTIFIER не влияет на идентификаторы с разделителем, заключенные в квадратные скобки ([ ]).

Параметр SET QUOTED_IDENTIFIER должен иметь значение ON при создании или изменении индексов по вычисляемым столбцам или индексированным представлениям. Если для параметра SET QUOTED_IDENTIFIER установлено значение OFF, то выполнение инструкций CREATE, UPDATE, INSERT и DELETE для таблиц с индексами, основанными на вычисляемых столбцах или индексированных представлениях, будет завершаться ошибкой. Дополнительные сведения о настройке параметров SET с индексированными представлениями и индексами в вычисляемых столбцах см. в разделе Анализ использования инструкций SET.

Параметр SET QUOTED_IDENTIFIER должен иметь значение ON при создании отфильтрованного индекса.

Параметр SET QUOTED_IDENTIFIER должен иметь значение ON при вызове методов типа данных XML.

Драйвер ODBC собственного клиента SQL Server и поставщик OLE DB собственного клиента SQL Server для SQL Server автоматически устанавливают QUOTED_IDENTIFIER значение ON при подключении. Это может быть настроено в источниках данных ODBC, в атрибутах соединения ODBC или свойствах соединения OLE DB. По умолчанию параметр SET QUOTED_IDENTIFIER имеет значение OFF для соединений из приложений DB-Library.

После создания таблицы параметр QUOTED IDENTIFIER всегда записывается в метаданные таблицы со значением ON, даже если он был установлен в OFF при создании таблицы.

Когда создается хранимая процедура, параметры SET QUOTED_IDENTIFIER и SET ANSI_NULLS фиксируются и используются для последующих вызовов этой хранимой процедуры.

При выполнении операций внутри хранимой процедуры значение SET QUOTED_IDENTIFIER не меняется.

Если для SET ANSI_DEFAULTS задано значение ON, для QUOTED_IDENTIFIER также задано значение ON.

Также параметр SET QUOTED_IDENTIFIER связан с параметром QUOTED_IDENTIFIER инструкции ALTER DATABASE.

SET QUOTED_IDENTIFIER применяется во время синтаксического анализа Transact-SQL и влияет только на анализ, а не на выполнение или оптимизацию запроса.

Для синтаксического анализа нерегламентированного пакета верхнего уровня начинается использование текущего параметра сеанса для QUOTED_IDENTIFIER. При анализе пакета все вхождения SET QUOTED_IDENTIFIER приведут к изменению поведения анализа с этой точки и сохранят этот параметр для сеанса. Поэтому после анализа и выполнения пакета параметр QUOTED_IDENTIFER сеанса будет задан в соответствии с последним вхождением SET QUOTED_IDENTIFIER в пакете.

Статический Transact-SQL в хранимой процедуре анализируется с использованием параметра QUOTED_IDENTIFIER, действующего для пакета, создавшего или изменившего хранимую процедуру. SET QUOTED_IDENTIFIER не работает, когда появляется в тексте хранимой процедуры в виде статического Transact-SQL.

Анализ вложенного пакета с процедурой sp_executesql или exec() начинается с использованием параметра QUOTED_IDENTIFIER сеанса. Если вложенный пакет находится внутри хранимой процедуры, анализ начинается с использованием параметра QUOTED_IDENTIFIER хранимой процедуры. При анализе пакета все вхождения SET QUOTED_IDENTIFIER приведут к изменению поведения анализа с этой точки, но параметр QUOTED_IDENTIFIER сеанса останется без изменений.

Чтобы просмотреть текущее значение для этого параметра, выполните следующий запрос:

DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF'; IF ( (256 & @@OPTIONS) = 256 ) BEGIN SET @QUOTED_IDENTIFIER = 'ON'; END SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER;

Разрешения

Необходимо членство в роли PUBLIC .

Примеры

А. Использование режима заключенных в кавычки идентификаторов и имен объектов, состоящих из зарезервированных слов

В следующем примере показано, что параметр SET QUOTED_IDENTIFIER должен иметь значение ON , а ключевые слова в именах таблиц должны быть заключены в двойные кавычки, чтобы создать и использовать объекты, содержащие в именах зарезервированные ключевые слова.

SET QUOTED_IDENTIFIER OFF GO -- Create statement fails. CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL); GO SET QUOTED_IDENTIFIER ON; GO -- Create statement succeeds. CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL); GO SELECT "identity","order" FROM "select" ORDER BY "order"; GO DROP TABLE "SELECT"; GO SET QUOTED_IDENTIFIER OFF; GO

B. Использование настроек заключенного в кавычки идентификатора с двойными и одинарными кавычками

В этом примере показано, как используются двойные и одинарные кавычки в строковых выражениях, если параметру SET QUOTED_IDENTIFIER присвоено значение ON и значение OFF .

SET QUOTED_IDENTIFIER OFF; GO USE AdventureWorks2022; IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Test') DROP TABLE dbo.Test; GO USE AdventureWorks2022; CREATE TABLE dbo.Test (ID INT, String VARCHAR(30)) ; GO -- Literal strings can be in single or double quotation marks. INSERT INTO dbo.Test VALUES (1, "'Text in single quotes'"); INSERT INTO dbo.Test VALUES (2, '''Text in single quotes'''); INSERT INTO dbo.Test VALUES (3, 'Text with 2 '''' single quotes'); INSERT INTO dbo.Test VALUES (4, '"Text in double quotes"'); INSERT INTO dbo.Test VALUES (5, """Text in double quotes"""); INSERT INTO dbo.Test VALUES (6, "Text with 2 """" double quotes"); GO SET QUOTED_IDENTIFIER ON; GO -- Strings inside double quotation marks are now treated -- as object names, so they cannot be used for literals. INSERT INTO dbo."Test" VALUES (7, 'Text with a single '' quote'); GO -- Object identifiers do not have to be in double quotation marks -- if they are not reserved keywords. SELECT ID, String FROM dbo.Test; GO DROP TABLE dbo.Test; GO SET QUOTED_IDENTIFIER OFF; GO

 ID String ----------- ------------------------------ 1 'Text in single quotes' 2 'Text in single quotes' 3 Text with 2 '' single quotes 4 "Text in double quotes" 5 "Text in double quotes" 6 Text with 2 "" double quotes 7 Text with a single ' quote

Использование SET ANSI_NULLS в SQL

При обработке массивов данных, в процессе создания хранимых процедур, возникает необходимость сравнения нулевых значений. Многие разработчики путаются в обработке нулевых значений. Когда говорится о значении, равном NULL, то на самом деле имеется в виду, что оно не имеет никакого значения. Это сильно отличается от числа равного нулю, или строки, являющейся пустой (или нулевой длинны). Именно поэтому нет возможности сравнить значение с NULL используя базовые операторы сравнения IS NULL или IS NOT NULL. Значение NULL – это состояние, в котором находится переменная или столбец, а не значение, которое они содержат.

Решение данной задачи возможно осуществить с помощью встроенного функционала настройки драйвера ODBC и поставщика OLE DB клиента SQL Server, для этого воспользуюсь инструкцией SET ANSI_NULLS, которая позволяет настроить SQL Server для использования операторов сравнения со значениями NULL, установив параметры SET ANSI_NULLS ON или SET ANSI_NULLS OFF.

Рассмотрю на примерах функциональные возможности использования данных настроек.

Первоначально для хранимых процедур SQL Server использует значение настройки SET ANSI_NULLS, которое действовало в момент создания процедуры. Драйвер ODBC и поставщик OLE DB собственного клиента SQL Server при соединении автоматически устанавливают параметр базы данных ANSI_NULLS значение ON.

Например, при значении параметра инструкции ANSI_NULLS равном ON в инструкции SELECT с условием WHERE столбец = NULL не вернет ни одной строки, если в столбце имеются значения NULL. Инструкция SELECT с условием WHERE столбец <> NULL не вернет ни одной строки, даже если в столбце есть значения, отличные от NULL. При значении параметра ANSI_NULLS равном OFF операторы равенства (=) и неравенства (<>) не следуют стандарту ISO. SELECT с условием WHERE столбец = NULL вернет строки со значениями NULL в столбце. Инструкция SELECT с условием WHERE столбец <> NULL возвращает строки содержащие значения, отличные от NULL, в столбце. Также любая инструкция SELECT с условием WHERE столбец <>ABC_value, вернет все строки содержащими значения, не равными ABC_value и не равными NULL. Если параметр ANSI_NULLS имеет значение ON, все сравнения со значением NULL вернут значение UNKNOWN, а когда ANSI_NULLS имеет значение OFF, сравнение любых значений с NULL вернет TRUE только в том случае, если сравниваемое значение тоже NULL. Если параметр ANSI_NULLS не указан, применяется значение ANSI_NULLS текущей базы данных.

Значение ANSI_NULLS определяется во время исполнения, а не в ходе работы синтаксического анализа.

В таблице ниже показано влияние значения параметра ANSI_NULLS на результаты нескольких логических выражений с использованием значений NULL и значений отличных от NULL:

Логическое выражение	Параметр SET ANSI_NULS ON	Параметр SET ANSI_NULS OFF
NULL=NULL	UNKNOWN	TRUE
1=NULL	UNKNOWN	FALSE
NULL<>NULL	UNKNOWN	FALSE
1<>NULL	UNKNOWN	TRUE
NULL>NULL	UNKNOWN	UNKNOWN
1>NULL	UNKNOWN	UNKNOWN
NULL IS NULL	TRUE	TRUE
1 IS NULL	FALSE	FALSE
NULL IS NOT NULL	FALSE	FALSE
1 IS NOT NULL	TRUE	TRUE

Инструкция SET ANSI_NULLS ON воздействует только на сравнения, в которых в качестве одного из объектов операции используется NULL в виде переменной или точной постоянной величины. Если оба аргумента операции представляют собой столбцы или составные выражения, эта настройка не влияет на результат сравнения.

Чтобы скрипт работал в соответствии с первоначальным замыслом, вне зависимости от параметра базы данных ANSI_NULLS или настроек SET ANSI_NULLS, в сравнениях, которые могут содержать значения NULL, следует использовать выражения IS NULL и IS NOT NULL. Значение SET ANSI_NULLS должно быть равно ON при выполнении распределенных запросов, а также при создании или изменении индексов вычисляемых столбцов или индексированных представлений.

Если SET ANSI_NULLS имеет значение OFF, то при работе с таблицами, содержащими индексы вычисляемых столбцов, а также при работе с индексированными представлениями инструкций CREATE, UPDATE, INSERT, DELETE завершатся неудачно. SQL Server возвращает сообщение об ошибке с перечислением всех недопустимых аргументов инструкции SET. Кроме того, при выполнении инструкции SELECT, в случае если значение SET ANSI_NULLS равно OFF, SQL Server игнорирует значение индексов в вычисляемых столбцах или представлениях и разрешает операцию выбора так, как если бы в таблицах или представлениях отсутствовали индексы.

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

При работе с вычисляемыми столбцами или индексированными представлениями значение ANSI_NULLS является одним из обязательных параметров директивы SET, которые должны быть настроены следующим образом, параметры ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL должны иметь значение ON, а параметр NUMERICROUNDABORT значение OFF. Данные настройки обусловлены тем, что SQL Server возвратит сообщение об ошибке при обнаружении ошибок деления на ноль и переполнения. При отсутствии вышеупомянутых настроек данные ошибки будут игнорироваться.

Чтобы узнать текущее значение параметра ANSI_NULLS, выполню следующий запрос:

SELECT SESSIONPROPERTY ('ANSI_NULLS') AS 'CURRENT VALUE OF SET ANSI_NULLS'

В следующем примере операторы сравнения Equals (=), Not Equals To (<>) используются для сравнения со значениями в таблице, которые равны или не равны NULL. Этот пример также показывает, что использование конструкции IS NULL не зависит от значения параметра SET ANSI_NULLS.

PRINT ’Создаю тестовую таблицу’ CREATE TABLE DB_TEST_ANSI_NULLS ([Value_field] int NULL) INSERT INTO DB_TEST_ANSI_NULLS values (NULL),(0),(1) GO

Следующий этап

Использую стандартный запрос на выборку с параметром ANSI_NULLS по умолчанию (значение ON) и при значении ANSI_NULLS OFF (SET ANSI_NULLS OFF)

DECLARE @varname int; SET @varname = NULL; SELECT [Value_field] FROM [DB_TEST_ANSI_NULLS] WHERE [Value_field] = @varname; SELECT [Value_field] FROM [DB_TEST_ANSI_NULLS] WHERE [Value_field] <> @varname; SELECT [Value_field] FROM [DB_TEST_ANSI_NULLS] WHERE [Value_field] IS NULL; SELECT [Value_field] FROM [DB_TEST_ANSI_NULLS] WHERE [Value_field] IS NOT NULL; GO

Получается следующий результат:

 ANSI_NULLS ON ANSI_NULLS OFF

Большая эффективность использования данной процедуры возникает при создании сложных конструкций, при JOINах т.д.

Но, резонно можно заявить, что в SQL есть процедура IS NULL(). Да, есть, но, к сожалению, данная процедура обрабатывает значения равные NULL и не воспринимает пустые ячейки, что приводит к потерям при обработке данных. Так что, во избежание потери данных при их фильтрации, рекомендую использовать ANSI_NULLS.

Читать:

Как сделать текст меньше в html

Спасибо за уделенное время, надеюсь, что мой опыт позволит исключить потери, повысить качество и полноту обработки данных!

Повышаем масштабируемость с DIRECTUM 5.4

С новой версией системы DIRECTUM администраторы могут включить принудительную параметризацию и фильтрованные индексы в SQL Server. Зачем использовать эти возможности SQL? Почему администратору не обойтись без мнения разработчика? Обо всем по порядку в статье.

Принудительная параметризация и фильтрованные индексы

На обработку параметризованных запросов SQL Server тратит меньше времени и ресурсов, чем на непараметризованные. Это достигается за счет использования кэшированного плана запроса. Включение принудительной параметризации позволяет SQL Server все непараметризованные запросы разом «превратить» в параметризованные.

Фильтрованный индекс – индекс, который содержит не все записи таблицы, а только те, которые соответствуют указанным в WHERE критериям. В результате индекс:

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

Очевидным местом применения фильтрованных индексов в DIRECTUM являются таблицы, хранящие разнородные данные: справочники и типы карточек документов. Можно установить фильтр по типу справочника или карточки.

Параметры соединения

Использование перечисленных возможностей SQL Server в DIRECTUM стало возможным благодаря изменению значений параметров соединения с SQL:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET CONCAT_NULL_YIELDS_NULL ON.

Все 3 параметра необходимы для фильтрованных индексов, ANSI_NULLS ON – для принудительной параметризации.

В будущих версиях SQL Server компания Microsoft откажется от значения OFF для ANSI_NULLS и CONCAT_NULL_YIELDS_NULL. Использование OFF в будущем будет приводить к ошибкам.

Modern-конфигурация

В версии 5.4 системы DIRECTUM разработана Modern-конфигурация серверной части. В отличие от классической серверной части в Modern параметры соединения с SQL-сервером имеют рекомендуемые Microsoft значения ON.

Применение этой конфигурации позволит вам полностью использовать возможности для уменьшения нагрузки и оптимизации работы системы. Мы предусмотрели средства, которые помогут вам перейти на новую конфигурацию и быть полностью готовым к будущим версиям SQL Server.

Изменение параметров может повлечь несовместимости. Если у вас нет своей разработки, то беспокоиться не о чем. Стандартная поставка DIRECTUM 5.4 полностью соответствует рекомендациям и не содержит несовместимостей. Чтобы выявить несовместимости в вашем прикладном коде, используйте утилиту конвертации ISBL-текстов.

Тестирование системы показало, что принудительная параметризация позволяет добиться снижения нагрузки на процессоры сервера с СУБД на 20%. Эффект зависит от характера нагрузки на СУБД и будет уникален для каждой системы.

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

если вы серьезно дорабатывали прикладную разработку и использовали много SQL-кода, а фильтрованные индексы и принудительная параметризация вам не нужны, то выбирайте Classic;
если в вашей организации используется стандартная поставка DIRECTUM или весь SQL-код разрабатывался с учетом рекомендуемых Microsoft значений параметров соединений, то выбирайте Modern;
если же фильтрованные индексы и принудительная параметризация вам нужны, и несовместимости кода есть, то необходимо исправить код и конвертироваться на Modern серверную часть.

При установке системы DIRECTUM 5.4 с нуля по умолчанию используется конфигурация Modern.

Конвертируйтесь на новую версию системы и делитесь в комментариях своим опытом оптимизации работы DIRECTUM и SQL Server.

Авторизуйтесь, чтобы оценить материал.
13 апреля 2017 в 12:30

Проводилось ли нагрузочное тестирование? Где-то можно посмотреть результаты до и после использования параметризации?

Авторизуйтесь, чтобы оценить материал.
13 апреля 2017 в 17:22

из статьи по ссылке:
«все двойные кавычки, используемые для указания строк, замените на одинарные. Если внутри строки, окруженной двойными кавычками, ранее были одинарные, их нужно продублировать.»

Вот это настораживает сразу же. что то я боюсь например. очень боюсь. много где используются строковые значения внутри запросов и в двойных они там или в одинарных — не помнится уже.

Вопрос: при конвертации из старых версий автоматически конвертятся тексты запросов в сценариях и обработчиках в соответствие с выбранной моделью параметризации?

Игорь Прищепов: обновлено 13.04.2017 в 17:30
Авторизуйтесь, чтобы оценить материал.
14 апреля 2017 в 15:40

Автоматически не конвертятся, т.к. нельзя достоверно определить, SQL запрос это или просто очень похожий на него текст (особенно, когда запросы собирают по кусочкам, эти кусочки передают через функции, сохраняют в окружении и т.п.). Но все подозрительные места можно выловить при поиске несовместимостей, описанном в статье.

Авторизуйтесь, чтобы оценить материал.
28 апреля 2017 в 07:01

Игорь, в любом случае в будущих версиях все эти стандарты помечены как deprecated, т.е. рано или поздно необходимо будет приводить всю прикладную в порядок.

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

Авторизуйтесь, чтобы оценить материал.
8 мая 2017 в 14:19

Именно поэтому переход на modern-серверную часть сделан опциональным. Если вы останетесь на старой (по умолчанию) — при конвертации ничего делать не нужно

Дмитрий Чепель: обновлено 08.05.2017 в 14:19
Авторизуйтесь, чтобы оценить материал.
3 мая 2018 в 16:28

Вы пишите, что в коробке уже всё сделано «Стандартная поставка DIRECTUM 5.4 полностью соответствует рекомендациям и не содержит несовместимостей .». НО как тогда объяснить, что после конвертации Я вижу «Предупреждения» в коробочных отчетах?

Привожу часть строк из файла ISBLConverterReport.txt:

Текущая разработка Интегрированные отчеты, расчет Links РКК 5 Текст Предупреждение Неподдерживаемый объект IS-Builder 7.55 ANSI_NULLS ON возможно несовместимый код Действующий MAX_LEVEL = 100 NULL_STR = ‘NULL’ // Создать список ИД наших организаций

Текущая разработка Интегрированные отчеты, расчет Links РКК 82 Текст Предупреждение Неподдерживаемый объект IS-Builder 7.55 CONCAT_NULL_YIELDS_NULL ON возможно несовместимый код Действующий Level + 1, Way + ‘, ‘ + cast(links.RKK2 as varchar(max)) from dbo.MBAnalit links

Текущая разработка Интегрированные отчеты, расчет Links РКК 89 Текст Предупреждение Неподдерживаемый объект IS-Builder 7.55 CONCAT_NULL_YIELDS_NULL ON возможно несовместимый код Действующий links.Vid = %0:s — СРК and Parent.Way not like ‘%%’ + cast(links.RKK as varchar(max)) + ‘%%’ )

Текущая разработка Интегрированные отчеты, расчет Links РКК 133 Текст Предупреждение Неподдерживаемый объект IS-Builder 7.55 CONCAT_NULL_YIELDS_NULL ON возможно несовместимый код Действующий 0, — Признак необходимости отрисовки узла ‘%1:s, ‘ + cast(rrc.Analit as varchar(max)) — Сформировать список обработанных РКК from dbo.MBAnalit links — СРК

Текущая разработка Интегрированные отчеты, расчет Links РКК 154 Текст Предупреждение Неподдерживаемый объект IS-Builder 7.55 CONCAT_NULL_YIELDS_NULL ON возможно несовместимый код Действующий 0, — Признак необходимости отрисовки узла parent.Way + ‘, ‘ + cast(rrc.Analit as varchar(max)) — Сформировать список обработанных РКК from dbo.MBAnalit links — СРК

Текущая разработка Интегрированные отчеты, расчет Links РКК 165 Текст Предупреждение Неподдерживаемый объект IS-Builder 7.55 CONCAT_NULL_YIELDS_NULL ON возможно несовместимый код Действующий — Не выводить узлы дерева, которые образуют петли —and parent.Way not like ‘%%, ‘ + cast(rrc.Analit as varchar(max)) + ‘%%’ and parent.Way not like ‘%%’ + cast(links.RKK as varchar(max)) + ‘%%’ + cast(links.RKK as varchar(max)) + ‘%%’ )

Текущая разработка Интегрированные отчеты, расчет Links РКК 166 Текст Предупреждение Неподдерживаемый объект IS-Builder 7.55 CONCAT_NULL_YIELDS_NULL ON возможно несовместимый код Действующий —and parent.Way not like ‘%%, ‘ + cast(rrc.Analit as varchar(max)) + ‘%%’ and parent.Way not like ‘%%’ + cast(links.RKK as varchar(max)) + ‘%%’ + cast(links.RKK as varchar(max)) + ‘%%’ ) — Добавить узлы дерева РКК во временную таблицу

Текущая разработка Папки поиска, событие На исполнение 10 До выбора Предупреждение Неподдерживаемый объект IS-Builder 7.55 QUOTED_IDENTIFIER ON несовместимый код ‘ass.Vid = %s ‘& ‘and subass.YesNo5 = «Д» ‘& ‘and ass.IntNumber3 = Tasks.XRecID)’; References.RRCAssignments.ID)

Текущая разработка Управляемые папки, событие ВХ. На исполнение (для руководителей) Д000003 10 До выбора Предупреждение Неподдерживаемый объект IS-Builder 7.55 QUOTED_IDENTIFIER ON несовместимый код Действующий ‘ass.Vid = %s ‘& ‘and subass.YesNo5 = «Д» ‘& ‘and ass.IntNumber3 = Tasks.XRecID)’; References.RRCAssignments.ID)

Текущая разработка Управляемые папки, событие ВХ. Делегированные поручения (для руководителей) Д000005 10 До выбора Предупреждение Неподдерживаемый объект IS-Builder 7.55 QUOTED_IDENTIFIER ON несовместимый код Действующий ‘ass.Vid = %s ‘& ‘and subass.YesNo5 = «Д» ‘& ‘and ass.IntNumber3 = Tasks.XRecID)’; References.RRCAssignments.ID)

Текущая разработка Управляемые папки, событие ВХ. На контроле Д000008 20 До выбора Предупреждение Неподдерживаемый объект IS-Builder 7.55 QUOTED_IDENTIFIER ON возможно несовместимый код Действующий Criteria = Sender.SearchCriteria Criteria.AddWhere = Format(‘(Tasks.StandardRoute in (%s) or (Tasks.State = «C» and Tasks.WorkflowRouteType = «S»))’; RoutesID.DelimitedText)

Особенности использования отфильтрованных индексов в SQL Server

Создал три отфильтрованных индекса на двух таблицах, фильтрация установлена по условию [column] <> » . Запускаю джоб, в котором выполняется пару десятков процедур и функций, плотно работающих на чтение и запись с вышеупомянутыми таблицами. Джоб падает с ошибкой:

Ошибка UPDATE. Следующие параметры SET содержат неверные значения: «QUOTED_IDENTIFIER»

В документации по QUOTED_IDENTIFIER есть такая заметка:

SET QUOTED_IDENTIFIER должен иметь значение ON при создании отфильтрованного индекса.

Делал именно так. В некоторых процедурах параметру QUOTED_IDENTIFIER присваивается значение OFF, может быть в этом проблема? Как связан отфильтрованный индекс со значением параметра QUOTED_IDENTIFIER?

Отслеживать
задан 15 сен 2016 в 3:23
3,738 1 1 золотой знак 20 20 серебряных знаков 47 47 бронзовых знаков

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

В некоторых процедурах параметру QUOTED_IDENTIFIER присваивается значение OFF, может быть в этом проблема?

Да, в этом. Параметр QUOTED_IDENTIFIER должен быть установлен в ON не только при создании отфильтрованного индекса, но и тогда, когда предполагается обращение к индексу (см. CREATE INDEX, раздел Отфильтрованные индексы; обратите внимание также на другие необходимые параметры SET ).

Внутри процедуры контекст инструкций, при их исполнении, частично определяется параметрами, установленными при создании процедуры (в частности значение QUOTED_IDENTIFIER ).

Так, например, если есть таблица с индексом

CREATE INDEX IX_table_column ON [table]([column]) WHERE [column] <> '';

и, допустим, процедура

SET QUOTED_IDENTIFIER ON /*OFF*/; GO CREATE PROCEDURE P_Select AS SELECT [column] FROM [table] WHERE [column] <> ''; GO

то при исполнении P_Select , если она была создана с QUOTED_IDENTIFIER ON , использование индекса IX_table_column возможно. Если же P_Select была создана с QUOTED_IDENTIFIER OFF , то индекс будет проигнорирован, даже если остальные факторы располагают к его использованию.

Если же в процедуре не SELECT , а UPDATE :

SET QUOTED_IDENTIFIER OFF; GO CREATE PROCEDURE P_Update AS UPDATE [table] SET [column] = 'Updated'; GO

то тихо проигнорировать индекс уже не получится (т.к. изменение данных нужно отразить и в индексе). В этом случае генерируется ошибка Msg 1934 .

Как связан отфильтрованный индекс со значением параметра QUOTED_IDENTIFIER?

Значение параметра QUOTED_IDENTIFIER должно быть согласовано и быть ON и при создании отфильтрованного индекса и при его использовании. Почему это именно так в документации не поясняется. Я предполагаю, что, может быть, это сделано для однозначности трактовки предикатов в фильтре индекса и в запросах.

При ON и OFF по разному трактуются двойные кавычки. Как это может влиять на запросы можно видеть на следующем примере.

CREATE TABLE quot_ident(A CHAR(1)); INSERT INTO quot_ident VALUES('A'), ('B');

Выполним один и тот же запрос с разными установками QUOTED_IDENTIFIER :

SET QUOTED_IDENTIFIER ON; SELECT * FROM quot_ident WHERE "A" = 'A'; SET QUOTED_IDENTIFIER OFF; SELECT * FROM quot_ident WHERE "A" = 'A';

Запрос с ON вернёт одну строку, т.к. «A» = ‘A’ эквивалентно [A] = ‘A’ . Запрос с OFF — все строки, т.к. в этом случае «A» = ‘A’ эквивалентно ‘A’ = ‘A’ .

Будет не хорошо, если WHERE «A» = ‘A’ будучи фильтром в индексе при UPDATE или SELECT с другим значением QUOTED_IDENTIFIER будет иметь другой смысл, чем предполагалось при создании индекса.