Set quoted identifier on что это в sql
SET QUOTED_IDENTIFIER
������� � ����� �� ���������� � ���������� SQL Server, ����������� SET. ��� ���� �� ��� ���� �����������, � ������ � SET QUOTED_IDENTIFIER, �.�. ��� ���������� ���� � ���� ����� ����� ���������� ������������, � ������� ������ ���� �� ������������.
���� �� ����������� � ����� ������ ��� ������ XML, � �������� ������ ���� ������ XML, �� ��� ���������� �����������, ��� ��������� set quoted_identifier ����� �������� ON. 
� ��������� ������, �� ������� ������ ���������� ������ �������: Msg 1934, Level 16, State 1, Line 6
SELECT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Msg 1934, Level 16, State 1, Line 4
CREATE INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with spatial index operations.
Msg 1934, Level 16, State 1, Line 4
CREATE INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Msg 1934, Level 16, State 1, Line 4
CREATE INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Msg 1934, Level 16, State 1, Line 4
CREATE INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
��� ���� �������: � ���� ����������� �����������: Msg 103, Level 15, State 4, Line 1
The identifier that starts with ‘C:\Program Files\Microsoft Visual Studio\Microsoft Visual Studio 10.0\Common7\Packages\Debugger\X64\Microsoft Visual Studio Test’ is too long. Maximum length is 128.
��� ��������, ������������� ����, ���������� ���������, �.�., ������� ������, �� ����� ���������� �������� �������� ����������. �� ���������� ��� ���� �����������, ���� ���� �� ������ ������� ������ � �������� ��� ������ �� ����������� ����, �� ����������� �� ������ ������ �� ������������, �.�. �� ����� �� ������� �� ���������� set quoted_identifier ON. � ������ ������������: 
� ������ ��� �� �������, �� � ���������� OFF 
Msg 1934, Level 16, State 1, Procedure test_proc_01, Line 5
INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
��� ���� �� �� �������� ����� ��������� ���������� � ON: � ���� ���� �� � ���� ����� ��������� ���� �� ���������� ON, �� �������� �� ��� ������: ��� � ��� ������ ����, ��������� ��������� ����������� � ������ ��������/���������. ��������� �� ����� � ������� ��������� ��������:
�� � ��� ��� �� ��� ��������. ���� � ��� ��������� ������� ������ ���������, �� ��� ��� �� ��������� ���������, � �������� ���� �������������� ������� ���������. ������: 
�� � ��� �� �� ��� ������, �����������, ��� �������� �������� � ��������� ��� �� �� ������, � ����� xp_cmdshell, ������� � ���� ������� �������� ������� ��������� ������ sqlcmd.exe � ��� ����� �� ������ ������: 
SQL Server. Интересные советы
Рекомендации по установкам для создания объектов баз данных Microsoft SQL Server
Рекомендации по установкам для создания объектов баз данных Microsoft SQL ServerSET NOCOUNT — меньше трафик
Если инструкция принимает значение ON, то количество строк (которые обработаны инструкцией Transact-SQL) не возвращается. Запрещает всем инструкциям хранимой процедуры отправлять клиенту сообщения DONE_IN_PROC. Если запросы выполняются из программы, то в результирующем наборе таких инструкций Transact-SQL как: SELECT, INSERT, UPDATE и DELETE значение: “nn rows affected” (строк обработано: nn) отображаться не будет. Для хранимых процедур с несколькими инструкциями, не возвращающих большое количество строк данных, это может значительно повысить производительность за счет существенного снижения объема сетевого трафика. Инструкция SET NOCOUNT устанавливается во время исполнения, а не на этапе синтаксического анализа.
ON по умолчанию. Идентификаторы можно заключать в двойные кавычки, а литералы должны быть разделены одинарными кавычками. Все строки, разделенные двойными кавычками, рассматриваются как идентификаторы объектов. Если в именах объектов базы данных используются зарезервированные ключевые слова, то параметру SET QUOTED_IDENTIFIER должно быть присвоено значение ON. При создании или изменении индексов в вычисляемых столбцах или индексированных представлениях параметру SET QUOTED_IDENTIFIER должно быть присвоено значение OFF. Драйвер ODBC и поставщик OLE DB для собственного клиента SQL Server при соединении автоматически присваивают параметру QUOTED_IDENTIFIER значение ON. По умолчанию параметр SET QUOTED_IDENTIFIER имеет значение OFF для соединений из приложений DB-Library. Когда создается хранимая процедура, параметры SET QUOTED_IDENTIFIER и SET ANSI_NULLS фиксируются и используются для последующих вызовов этой хранимой процедуры. При выполнении операций внутри хранимой процедуры значение SET QUOTED_IDENTIFIER не меняется. Если параметр SET ANSI_DEFAULTS имеет значение ON, параметр SET QUOTED_IDENTIFIER включается. Параметр SET QUOTED_IDENTIFIER устанавливается во время синтаксического анализа. Настройка на время синтаксического анализа означает, что если инструкция SET присутствует в пакете или хранимой процедуре, она выполняется вне зависимости от того, достигает ли выполнение кода фактически этой точки. Кроме того, инструкция SET выполняется до выполнения любых инструкций.
Стандарт SQL-92 требует, чтобы операторы “=” и “<>” при использовании со значениями NULL всегда возвращали FALSE. SQL Server интерпретирует пустую строку как один пробел или действительно пустую строку в зависимости от настройки уровня совместимости. Директива SET ANSI_NULLS ON влияет только на сравнения, где в качестве одного из операндов используется NULL в виде переменной или литеральной константы. Если оба операнда представляют собой столбцы или составные выражения, эта настройка не влияет на результат сравнения. Для хранимых процедур SQL Server использует значение настройки SET ANSI_NULLS, которое действовало в момент создания процедуры. Значение SET ANSI_NULLS должно быть равно ON при выполнении распределенных запросов. SET ANSI_NULLS также должно быть ON при создании или изменении индексов вычисляемых столбцов или индексированных представлений (это один из семи обязательных для этого параметров директивы SET: ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER и CONCAT_NULL_YIELDS_NULL должны иметь значение ON, а параметр NUMERIC_ROUNDABORT – значение OFF). Драйвер ODBC и поставщик OLE DB собственного клиента SQL Server при соединении автоматически устанавливают параметру ANSI_NULLS значение ON. Для соединений из приложений DB-Library значением по умолчанию для параметра SET ANSI_NULLS является OFF. Установка значения SET ANSI_NULLS происходит во время запуска или выполнения, но не во время синтаксического анализа.
Формирует предупреждающее сообщение, если значения NULL появляются в статистических функциях: SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP или COUNT. Инструкции INSERT или UPDATE, выполнение которой привело к ошибке деления на ноль или арифметического переполнения, в соответствии со стандартом SQL-92 будут откачены и сформировано сообщение об ошибке. Конечные пробелы игнорируются для символьных столбцов, а конечные значения NULL игнорируются для бинарных столбцов. Значение ANSI_WARNINGS игнорируется при передаче аргументов хранимой процедуре или пользовательской функции, а также при объявлении и настройке переменных в инструкции пакетных заданий. Например, если объявить переменную как char(3), а затем присвоить ей значение длиннее трех символов, данные будут усечены до размера переменной, а инструкция INSERT или UPDATE завершится без ошибок. Параметр SET ANSI_WARNINGS должен иметь значение ON при создании или изменении индексов, основанных на вычисляемых столбцах или индексированных представлениях. Параметр ANSI_WARNINGS должен быть установлен в ON для выполнения распределенных запросов. Драйвер ODBC собственного клиента и поставщик OLE DB для собственного клиента SQL для SQL Server при соединении автоматически устанавливает параметр ANSI_WARNINGS в значение ON. Параметр SET ANSI_WARNINGS устанавливается во время выполнения, а не во время синтаксического анализа. Если значение параметра SET ARITHABORT или SET ARITHIGNORE установлено в OFF, а значение параметра SET ANSI_WARNINGS установлено в ON, то SQL Server возвращает сообщение об ошибке при обнаружении ошибок деления на ноль и переполнения.
Контролирует способ хранения значений, которые короче, чем заданный размер поля, а также способ хранения в полях типов: char, varchar, binary и varbinary таких значений, которые имеют оконечные пробелы. Производитель рекомендует ON. Значение параметра инструкции SET ANSI_PADDING не оказывает влияния на значения типа nchar, nvarchar, ntext, text, image, а также на большие значения, для которых SET ANSI_PADDING всегда ON. Это означает, что оконечные пробелы и нули не отбрасываются. SET ANSI_PADDING ON необходимо при создании или изменении индексов по вычисляемым столбцам или индексированным представлениям. Драйвер ODBC и поставщик OLE DB для собственного клиента SQL Server при соединении автоматически устанавливают параметр ANSI_WARNINGS в значение ON. Значение параметра SET ANSI_PADDING устанавливается во время выполнения или запуска, а не во время синтаксического анализа.
Ошибка в транзакции приведёт к её откату, а не к предупреждению. Если параметры SET ARITHABORT и SET ANSI WARNINGS установлены в ON, ошибка приведёт к завершению запроса. Если SET ARITHABORT ON а SET ANSI WARNINGS OFF, ошибка прервёт пакет. Установка параметра SET ARITHABORT происходит при запуске или во время исполнения, но не во время синтаксического анализа. SET ARITHABORT ON необходимо при создании или изменении индексов по вычисляемым столбцам или индексированным представлениям.
Если произошла ошибка при исполнении инструкции Transact-SQL, транзакция будет откачена целиком. Инструкция SET XACT_ABORT не влияет на компиляцию ошибок (например, синтаксических). Параметр XACT_ABORT должен иметь значение ON для инструкций изменения данных в явных или неявных транзакциях, применяющихся к большинству поставщиков OLE DB, включая SQL Server. Единственным случаем, когда этот параметр не требуется, является поддержка поставщиком вложенных транзакций. Дополнительные сведения см. в разделе Распределенные запросы и распределенные транзакции. Значение параметра XACT_ABORT устанавливается во время выполнения, а не во время синтаксического анализа. Включение этого параметра позволяет не заботиться об обработке ошибок при вставках и изменениях.
При этой установке, сцепление значения NULL со строкой дает в результате NULL. Настройка SET CONCAT_NULL_YIELDS_NULL устанавливается во время выполнения или запуска, но не во время синтаксического анализа. SET CONCAT_NULL_YIELDS_NULL ON необходимо при создании или изменении индексов по вычисляемым столбцам или индексированным представлениям.
Потеря точности не приводят к формированию сообщений об ошибках, а результат округляется с точностью столбца или переменной, в которых будет сохранен. Потеря точности происходит, когда выполняется попытка сохранения значения с фиксированной точностью в столбце или переменной с меньшей точностью. Если параметру SET NUMERIC_ROUNDABORT присвоено значение ON, параметр SET ARITHABORT определяет серьезность формируемой ошибки. В следующей таблице показано влияние этих двух параметров на сообщения об ошибках при потере точности. Значение параметра SET NUMERIC_ROUNDABORT задается на этапе выполнения или запуска, но не на этапе синтаксического анализа. При создании или изменении индексов вычисляемых столбцов или индексированных представлений параметр SET NUMERIC_ROUNDABORT должен принимать значение OFF.
1. Избегите использования звёздочки (*) в SELECT, всегда перечисляйте только необходимые столбцы.
2. В инструкции INSERT всегда указывайте имена столбцов.
3. Всегда присваивайте таблицам (а при необходимости и столбцам) псевдонимы – это позволяет избежать путаницы. При использовании псевдонима столбца обязательно добавляйте ключевое слово AS.
4. При ссылке на объект всегда указывайте схему (владельца).
5. Избегите использования non-SARGable предикатов (“IS NULL”, “<>”, “!=”, “!>”, “! 100 ).
6. Для сокращения числа итераций старайтесь по возможности использовать строчный оператор CASE. Например:
ANSI NULLS и QUOTED IDENTIFIER убивали вещи. Для чего они?
примечание: Я проверил понимание значение параметра и это не ответ на мой вопрос.
Я получил мои DBAs для запуска индекса, который я сделал на моих серверах Prod (они просмотрели его и одобрили).
это ускорило мои запросы, как я и хотел. Однако я начал получать такие ошибки:
как разработчик я обычно игнорировал эти настройки. И это никогда не имело значения. (За 9+ лет). Ну, сегодня это важно.
Я пошел и посмотрел на один из sprocs, которые терпят неудачу, и у него есть это перед созданием для sproc:
может ли кто-нибудь сказать мне с точки зрения разработчика приложений, что делают эти операторы set? (просто добавление вышеуказанного кода перед моими инструкциями Index create не устранило проблему.)
Примечание: вот пример того, как выглядели мои индексы:
4 ответов
OK, с точки зрения разработчика приложений, вот что делают эти настройки:
значение параметра
этот параметр управляет тем, как кавычки «..» интерпретируются компилятором SQL. Когда QUOTED_IDENTIFIER включено, тогда кавычки рассматриваются как скобки ( [. ] ) и может использоваться для цитирования имен объектов SQL, таких как имена таблиц, имена столбцов и т. д. Когда он выключен (не рекомендуется), кавычки обрабатываются как апострофы ( ‘..’ ) и может использоваться для цитаты текстовые строки в командах SQL.
параметр ansi_nulls
параметр concat_null_yields_null
найти документация, блога, и StackOverflow ответы бесполезно объяснять, что включается QUOTED_IDENTIFIER средства.
былые времена
первоначально SQL Server разрешал использовать кавычки ( «. » ) и Апостроф ( ‘. ‘ ) вокруг строк взаимозаменяемо (как это делает Javascript):
и все это работало, и имело смысл.
затем Анси и другие идеи:
что означает, что если вы хотите «цитата» фанки имя столбца или таблицы вы должны использовать кавычки:
если вы знали SQL Server, вы знали, что цитата Маркс!—31—> уже использовались для представления строк. Если вы слепо пытались выполнить это В ANSI-SQL В как будто T-SQL: это ерунда, и SQL Server сказал вам так:
вы должны выбрать новое поведение ANSI
таким образом, Microsoft добавила функцию, чтобы позволить вам выбрать в ANSI вкус SQL.
Оригинал
УСТАНОВИТЬ QUOTED_IDENTIFIER ON
T-SQL (плохо?) (например, SQL, созданный Entity Framework)
ANSI-SQL (хорошо?)
Я думаю, что при перестройке индексов он был выключен.
при работе с фильтрованным индексом необходимо включить нижеприведенную настройку:
вам нужно добавить, чтобы добавить
для всех моих хранимых процедур редактирование таблицы с вычисляемым столбцом, чтобы избежать этого ошибка.
когда SET ANSI_NULLS включен, оператор SELECT, который использует WHERE column_name = NULL возвращает нулевые строки, даже если в имя столбца. Инструкция SELECT, использующая WHERE column_name NULL возвращает нулевые строки, даже если в column_name есть значения nonnull.
когда SET ANSI_NULLS выключен, равно ( = ) и не равно () операторы сравнения не следуют стандарт ISO. ОТБОР оператор, использующий WHERE column_name = NULL возвращает строки, которые имеют значения null в column_name. Инструкция SELECT, использующая WHERE column_name NULL возвращает строки, которые имеют значения nonnull в колонна. Кроме того, оператор SELECT, который использует WHERE column_name Возвращает XYZ_value всех строк, которые не XYZ_value и что не НОЛЬ.
при установке QUOTED_IDENTIFIER включен, идентификаторы могут быть разделены двойные кавычки, и литералы должны быть заключены в одинарные кавычки. Когда SET QUOTED_IDENTIFIER выключен, идентификаторы не могут быть в кавычках и должен следовать всем правилам Transact-SQL для идентификаторов. Для дополнительные сведения см. В разделе идентификаторы базы данных. Литералы могут быть разделены одинарными или двойными кавычками.
когда SET QUOTED_IDENTIFIER включен (по умолчанию), все строки разделены двойная цитата метки интерпретируются как идентификаторы объектов. Таким образом кавычки идентификаторы не должны следовать Transact-SQL правила для идентификаторов. Они могут быть зарезервированы ключевые слова и могут включать символы, не разрешенные в языке Transact-SQL для идентификаторов. Двойной кавычки нельзя использовать для разграничения строковых выражений; одинарные кавычки должны использоваться для заключения символьных строк. Если одинарная кавычка ( ‘ ) является частью литеральной строки, она может быть представлены два один кавычки.)»( УСТАНОВИТЬ ЗНАЧЕНИЕ ПАРАМЕТРА должен быть включен, когда зарезервированные ключевые слова используются для имен объектов в база данных.
когда SET CONCAT_NULL_YIELDS_NULL включен, объединение нулевого значения с помощью строки получается нулевой результат. Например, выберите ‘ abc ‘ + NULL дает NULL. Когда SET CONCAT_NULL_YIELDS_NULL выключен, объединение значения null со строкой дает саму строку (значение null обрабатывается как пустая строка). Например, выберите » abc » + null выходы азбука.
Если SET CONCAT_NULL_YIELDS_NULL не указан, то настройка Применяется параметр базы данных CONCAT_NULL_YIELDS_NULL.
ANSI_NULLS ON делает любое двоичное логическое выражение с нулевым значением равным false. Используя следующий шаблон:
правильный способ проверить значение null-использовать is [not] NULL
QUOTED_IDENTIFER ON просто позволяет использовать двойные кавычки для разграничения идентификаторов (плохая идея IMO, просто квадратные скобки пользователя)
SQLShack
SET QUOTED_IDENTIFIER settings in SQL Server
In this article, we will explore QUOTED_IDENTIFIER behavior, default value, and comparison with a different value. Set options at connection level control query behavior in SQL Server. The query might behave differently with different set options and their values.
Introduction
Most of the developers use SET QUOTED_IDENTIFIER ON/OFF option in a stored procedure and user-defined functions. If you have not used it, SQL Server automatically adds this while scripting out the objects. This article explains the configuration and its benefits.
Let’s create a stored procedure with the following script:
It generates the script in the new query window of SSMS. In the generated script, we can see an option SET QUOTED_IDENTIFIER ON. We have not added any such argument while creating the stored procedure. You can review the stored procedure script again for verification:
You are probably asking yourself why SSMS adds these options while generating a script for an object?
Let’s understand the QUOTED_IDENTIFIER option in SQL Server in the next section.
SET QUOTED_IDENTIFIER
We use single or double quotes in the script. Suppose we want to store employees’ name in a SQL table. The employee name might include a single quote in the name as well.
Let’s say customer name is O’Brien, if we try to insert this name in a table, we get the following error message:
207, Level 16, State 1, Line 3 Invalid column name ‘O’Brien’.
We can execute the query as shown below, and it inserts record successfully in the table:
We can use single quotes as well to insert the data.
QUOTED_IDENTIFIER controls the behavior of SQL Server handling double-quotes.
Look at the following example with different values of QUOTED_IDENTIFIER:
Example 1: SET QUOTED_IDENTIFIER OFF
It gives the output Rajendra:
Example 2: SET QUOTED_IDENTIFIER ON
Let’s turn this option ON and execute the query.
We get an error message stating invalid column name:
We cannot use a reserved keyword for object names. For example, function is a reserved keyword, and we cannot create any existing object with this:
It gives the following error message:
As highlighted earlier, we can use reserved keywords in the object name once we enable the QUOTED_IDENTIFIER ON and SQL Server does not check the string for any existing rules.
In the following query, we use the reserved keyword function as an object:
Once the object is created, we can access the object without specifying the QUOTED_IDENTIFIER option. Both queries execute successfully:
Let’s view the behavior of single quotes along with QUOTED_IDENTIFIER ON and OFF:
As you can see in the following screenshot, it treats string inside single quotes as literal. In the first select statement, it prints the string inside a single quote.
With the QUOTED_IDENTIFIER OFF, it gives a similar output. This setting does not impact SQL Server behavior for the single quotes:
In the following example, let’s observe a single and double quote behavior with QUOTED_IDENTIFIER:
Both Select statements return similar output once we turn off the QUOTED_IDENTIFIER option:
In another example, we use previous Select statements with the QUOTE_INDENTIFIER ON:
We get an error message with the double-quotes. You can also see that SQL Server does not parse the double-quoted string. It cannot recognize the object and highlights the string with an underline:
Let’s look at a few complex examples with a combination of single and double quotes in a string:
It contains three single quotes marks:
If we use a double quote and single quote together, it treats double quote as string delimiter and prints the other single quote. We can explore a few more commands to see the behavior of QUOTED_IDENTIFIER:
Identify objects using QUOTED_IDENTIER explicitly
We can use sys.sql_modules to filter the results using the QUOTED_IDENTIFIER setting ON or OFF. It shows the objects in which we defined QUOTED_IDENTIFIER explicitly. It does not show the objects using the default behavior:
Query to find objects using QUOTED_IDENTIFIER ON
Query to find objects using QUOTED_IDENTIFIER OFF
SSMS property to control QUOTED_IDENTIFIER
At the beginning of the article, we created a new stored procedure without specifying QUOTED_IDENTIFIER. Later, we generated the script, and it includes SET QUOTED_IDENTIFIER ON option.
It gives various options to control SSMS behavior such as environment details, query execution, query results behavior:
It also has a search option in that we can filter out the required configuration. Search for keyword ANSI, and it gives you settings to control query execution behavior.
In this screenshot, we can see it has an option SET QUOTED_IDENTIFIER, and it is enabled. It is the default behavior of SQL Server:
If we want to turn off QUOTE_IDENIFIER, we can remove the tick from the checkbox:
Click OK and restart the SSMS so that this setting can take effect. Let’s create the procedure [dbo].[SQLTest] again and generate the script for it. In the below screenshot, we can see that it has QUOTED_IDENTIFIER OFF now:
Note: If we change the QUOTED_IDENTIFIER option SSMS tools, it turns off the QUOTED_IDENTIFIER for all client session. You should be careful about modifying this option in the production environment.
Verify QUOTED_IDENTIFIER in the SSMS execution plan
We can use the SQL execution plan to view the current value of QUOTED_IDENTIFIER. Enable Actual execution plan before executing the query (shortcut key – CTRL+M):