Use of SET QUOTED_IDENTIFIER in SQL Server

Use of SET QUOTED_IDENTIFIER in SQL Server

In a stored procedure, there are often
SET QUOTED_IDENTIFIER ON
SET QUOTED_IDENTIFIER OFF

If SET QUOTED_IDENTIFIER ON, when you create a table, if the table name of the table, just use the SQL Server keyword, as in the following case

CREATE TABLE Distinct (
ID int NOT NULL constraint Pk_1 primary KEY,
Value varchar (255),
Flag int
)

The above statement will run in error, regardless of whether set QUOTED_IDENTIFIER is on or off, it will prompt for syntax errors near the keyword ' distinct '.

That's because distinct is an identifier for SQL Server, and if you want to distinct as a table, you can't create a table with a table named distinct when QUOTED_IDENTIFIER is off, because in Quoted_ Identifier is off, SQL Server identifiers are not allowed in quotes, so the
In the case of SET quoted_identifier off, it is not possible to quote distinct or unquoted or double quotes.

However, in the case of SET QUOTED_IDENTIFIER on, the SQL Server identifier can be enclosed in double quotation marks to create a table with the SQL Server identifier as the table name, but it is also not possible to add single quotes.

CREATE TABLE "distinct" (
ID int NOT NULL constraint Pk_1 primary KEY,
Value varchar (255),
Flag int
)

Can run

CREATE TABLE ' distinct ' (
ID int NOT NULL constraint Pk_1 primary KEY,
Value varchar (255),
Flag int
)

Can not run

When SET QUOTED_IDENTIFIER is on, the identifier/database keyword can be enclosed in double quotation marks. When SET QUOTED_IDENTIFIER is OFF, the identifier is not quoted, the quotation marks are not used, and all Transact-SQL identifier rules must be met.

SELECT sessionproperty (' quoted_identifier ') quotedidentifier
Default is ON
1

Use of SET QUOTED_IDENTIFIER in SQL Server