Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.