How to use set quoted_identifier in SQL Server
Editor to share with you the use of set quoted_identifier in SQL Server, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!
SQL Server is often found in stored procedures.
SET QUOTED_IDENTIFIER on SET QUOTED_IDENTIFIER off
If SET QUOTED_IDENTIFIER on, when creating a table, if the table name of the table happens to use the keyword sqlserver, as in the following case
Create table distinct (id int not null constraint pk_1 primary key, value varchar, flag int)
The above statement runs erratically, regardless of whether the SET QUOTED_IDENTIFIER is on or off, prompting a syntax error near the keyword 'distinct'.
The usage of SET QUOTED_IDENTIFIER in SQL Server
That is because distinct is the identifier of sqlserver, if you want to use distinct as the table, when QUOTED_IDENTIFIER is off, you cannot create a table named distinct, because in the case of QUOTED_IDENTIFIER for off, sqlserver identifiers are not allowed to be quoted, so in the case of SET QUOTED_IDENTIFIER off, it is not possible to add quotation marks or no quotation marks or double quotation marks on distinct.
However, in the case of SET QUOTED_IDENTIFIER on, it is possible to add double quotation marks to the sqlserver identifier to create a table with the sqlserver identifier as the table name, but single quotation marks are also not allowed.
Create table "distinct" (id int not null constraint pk_1 primary key, value varchar, flag int)
Can be run
Create table 'distinct' (id int not null constraint pk_1 primary key, value varchar, flag int)
Cannot run
When SET QUOTED_IDENTIFIER is ON, identifiers / keywords in the database can be enclosed in double quotes. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted, and quotation marks are useless, and must comply with all Transact-SQL identifier rules.
1SELECT SESSIONPROPERTY ('QUOTED_IDENTIFIER') quotedidentifier
Default is on
The above is all the contents of the article "how to use set quoted_identifier in SQL Server". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
