The effect of the QUOTED_IDENTIFIER option on index

The effect of the SET quoted_identifier option on index

1. When the index created or modified contains computed columns, it must be SET quoted_identifier=on;
2. When creating or modifying index on indexed view, SET quoted_identifier=on is required;
3. When you create or modify filtered index, you must SET quoted_identifier=on;

One, case

In the morning, finding the job that was used to process index fragmentation failed, check the job history and find the following error:

ALTER INDEX failed because the following SET options has incorrect settings: ' QUOTED_IDENTIFIER '. Verify that SET options is 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. [SQLSTATE 42000]  (Error 1934). The step failed.

The reason for the error is: There is no SET quoted_identifier =on.

The explanation for MSDN is:

SET QUOTED_IDENTIFIER must is on if you is creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements in tables with indexes on computed columns or indexed views would fail.

SET QUOTED_IDENTIFIER must is on if you are creating a filtered index.

SET QUOTED_IDENTIFIER must is on when you invoke XML data type methods.

Two, explanation of SET quoted_identifier option

When SET quoted_identifier are on, identifiers can being delimited by double quotation marks, and literals must be delimited b Y single quotation marks.

When SET quoted_identifier are OFF, identifiers cannot be QUOTED and must follow all Transact-SQL rules for identifiers. Literals can delimited by either single or double quotation marks.

When setting QUOTED_IDENTIFIER, strings using double quotes are interpreted as an Object's identity, so if you want to identify a string, you must use single quotation marks.

when SET quoted_identifier is on (default), all strings delimited by double quotation MA Rks is interpreted as object identifiers. Therefore, quoted identifiers do not has to follow the Transact-SQL rules for identifiers. They can reserved keywords and can include characters not generally allowed in Transact-SQL identifiers. Double quotation marks cannot is used to delimit literal string expressions; Single quotation marks must is used to enclose literal strings. If a single quotation mark ( ' ) was part of the literal string, it can be represented by both single Quotati On marks ( "). SET QUOTED_IDENTIFIER must is on when reserved keywords is used for object names in the database.

When SET quoted_identifier are OFF, literal strings in expressions can are delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such a S apostrophes.

1,delimiting identifiers

When SET quoted_identifier=on, SQL Server has double quotation marks as the delimiter, and the function and default delimiters are the same as brackets [].

While the brackets are not affected by the QUOTED_IDENTIFIER option setting as delimiters, they can always be used as delimiters.

Using brackets, [ and ], to delimit identifiers are not affected by the QUOTED_IDENTIFIER setting.

Select is a keyword and cannot be used with a user-defined object unless the delimiter is used. When SET QUOTED_IDENTIFIER ON, you can use the double quote "select", so that the SELECT keyword can be the same as the table Name, and the function is the same as [select].

SETQuoted_identifierOFFGO--An attempt to create a table with a reserved keyword as a name--should fail.CREATE TABLE"Select" ("Identity"INT IDENTITY  not NULL, "Order"INT  not NULL);GOSETQuoted_identifier on;GO--Would succeed.CREATE TABLE"Select" ("Identity"INT IDENTITY  not NULL, "Order"INT  not NULL);GOSELECT"Identity","Order"  from"Select"ORDER  by"Order";GODROP TABLE"SELECT";GOSETQuoted_identifierOFF;GO

2, when SET Quoted_identifier=on, the string must use single quotation marks

When set Quoted_identifier=off, strings can be used with single quotes, or double quotes can be used

SETQuoted_identifier on;GO--SuccessDECLARE @var varchar(Ten)Set @var='ABC'Select @varGO--FailureDECLARE @var varchar(Ten)Set @var="ABC"Select @varGOSETQuoted_identifierOFF;GO--SuccessDECLARE @var varchar(Ten)Set @var='ABC'Select @varGO--SuccessDECLARE @var varchar(Ten)Set @var="ABC"Select @varGO

Error message: Illegal column Name, very strange message. When QUOTED_IDENTIFIER is set, the string identified by double quotation marks is interpreted as an Object's identity.
MSG 207, Level A, State 1, line 4
Invalid column name ' abc '.

Second, view the settings of the QUOTED_IDENTIFIER option from the sys.databases

Select db.name,db.database_id,  db.is_quoted_identifier_onfrom sys.databases db

Third, the effect of SET quoted_identifier option on index

1. When the index created or modified contains computed columns, it must be SET quoted_identifier=on;
2. When creating or modifying index on indexed view, SET quoted_identifier=onis required;
3. When you create or modify filtered index, you must SET quoted_identifier=on;

SET QUOTED_IDENTIFIER must is on if you is creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements in tables with indexes on computed columns or indexed views would fail. For more information about required SET option settings with indexed views and indexes on computed columns, see "Considera tions when to use the SET statements "in SET statements (Transact-SQL).

SET QUOTED_IDENTIFIER must is on if you are creating a filtered index.

SET QUOTED_IDENTIFIER must is on when you invoke XML data type methods.

The SQL Server Native client ODBC driver and SQL Server Native client OLE DB Provider for SQL server automatically set QUO Ted_identifier to ON when connecting. This can is configured in the ODBC data sources, in ODBC connection attributes, or OLE DB connection properties. The default for SET quoted_identifier are OFF for connections from Db-library applications.

When a table was created, the QUOTED IDENTIFIER option is all stored as on the table ' s metadata even if the option is Set to OFF if the table is created.

When a stored procedure was created, the SET QUOTED_IDENTIFIER and set ANSI_NULLS settings are captured and used for SUBSEQ Uent invocations of that stored procedure.

When executed inside a stored procedure, the setting of SET quoted_identifier are not changed.

When set Ansi_defaults are on, SET QUOTED_IDENTIFIER is enabled.

SET QUOTED_IDENTIFIER also corresponds to the QUOTED_IDENTIFIER setting of ALTER DATABASE. For more information on database settings, see ALTER database (Transact-SQL).

IV, Scope

The SET statement only affects the current session and is set at parse time.

The Transact-SQL programming language provides several SET statements the current session handling of specific Information.

SET QUOTED_IDENTIFIER is set at parse time. Setting at parse time means so if the SET statement is present in the batch or stored procedure, it takes effect, regard Less of whether code execution actually reaches this point; And the SET statement takes effect before any statements is executed. When multiple conflicting SET statements was present in the batch, the last setting parsed was used.

Five, Summary

When creating or modifying index, remember to precede the statement with:SET quoted_identifier ON, which is beneficial to harmless.

Reference Documentation:

SET QUOTED_IDENTIFIER (Transact-SQL)

The effect of the QUOTED_IDENTIFIER option on index