Use of SET QUOTED_IDENTIFIER in SQL Server

Source: Internet
Author: User

In a stored procedure, there are often

SQL code
    1. SET quoted_identifier on
    2. SET quoted_identifier off
SET quoted_identifier OnSET quoted_identifier off
Such a statement, then SET QUOTED_IDENTIFIER what exactly mean, what is the use of it, this afternoon carefully looked at.
If SET QUOTED_IDENTIFIER on, when creating a table, the table name of the table is exactly the SQL Server identifier, as in the following case
    1. Create Table distinct (
    2. ID int not null constraint pk_1 primary key,
    3. Value varchar (255),
    4. Flag int
    5. )
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's identifier is not quoted, so in the case of SET quoted_identifier off, it is not possible to enclose the distinct in quotation marks or without quotation marks 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. SQL code
    1. Create table "distinct" (
    2. ID int not null constraint pk_1 primary key,
    3. Value varchar (255),
    4. Flag int
    5. )
CREATE TABLE "distinct" (id int not  NULL constraint Pk_1 primary   key, value varchar (255), flag int)
can run SQL code
    1. Create table 'distinct ' (
    2. ID int not null constraint pk_1 primary key,
    3. Value varchar (255),
    4. Flag int
    5. )
CREATE TABLE ' distinct ' (id int not  NULL constraint Pk_1 primary   key, value varchar (255), flag int)
Can not run

Use of SET QUOTED_IDENTIFIER in SQL Server

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.