SQL SERVER – QUOTED_IDENTIFIER ON/OFF Explanation and Example – Question on Real World Usage

This is a follow up blog post of SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation. I wrote that blog six years ago and I had plans that I will write a follow up blog post of the same. Today, when I was going over my to-do list and I was surprised that I had an item there which was six years old and I never got to do that.

In the earlier blog post I wrote about exploitation of the Quoted Identifier and ANSI Null. In this blog post we will see a quick example of Quoted Identifier. However, before we continue this blog post, let us see a refresh what both of Quoted Identifider do.

QUOTED IDENTIFIER ON/OFF

This option specifies the setting for use of double quotes. When this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.

In simple words when we have QUOTED IDENTIFIER ON, anything which is wrapped in double quotes becomes an object.

E.g.

-- The following will work
SET QUOTED_IDENTIFIER ON
GO
CREATE DATABASE "Test1"
GO
-- The following will throw an error about Incorrect syntax near 'Test2'.
SET QUOTED_IDENTIFIER OFF
GO
CREATE DATABASE "Test2"
GO

This feature is particularly helpful when we are working with reserved keywords in SQL Server. For example if you have to create a database with the name VARCHAR or INT or DATABASE you may want to put double quotes around your database name and turn on quoted identifiers to create a database with the such name. Personally, I do not think so anybody will ever create a database with the reserve keywords intentionally, as it will just lead to confusion.

Here is another example to give you further clarity about how Quoted Idenifier setting works with SELECT statement.

-- The following will throw an error about Invalid column name 'Column'.
SET QUOTED_IDENTIFIER ON
GO
SELECT "Column"
GO
-- The following will work
SET QUOTED_IDENTIFIER OFF
GO
SELECT "Column"
GO

Personally,

I always use the following method to create database as it works irrespective of what is the quoted identifier’s status. It always creates objects with my desire name whenever I would like to create.

CREATE DATABASE [Test3]

I believe the future of the quoted identifier on or off is useful in the real world when we have script generated from another database where this setting was ON and we have to now execute the same script again in our environment again.

Question to you -

I personally have never used this feature as I mentioned earlier. I believe this feature is there to support the scripts which are generated in another SQL Database or generate the script for other database. Do you have a real world scenario where we need to turn on or off Quoted Identifiers.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

5 thoughts on “SQL SERVER – QUOTED_IDENTIFIER ON/OFF Explanation and Example – Question on Real World Usage

  1. Yes, I have real word scenario. The situation is like this – We have to add a computed column and index on that. To work this, you must have to have the quoted identifier on.
    Also if your procedures that are going to do insert/updare on that table and those prorcedures are created with quoted identifier OFF then so you must have to re-create procedures with the quoted identifier ON other wise it will lead to below error.

    Msg 1934, Level 16, State 1, Procedure XXXX
    INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are 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.

    Like

  2. Yes, I have a real world scenario as well. I got the same error as mentioned by vinayak.
    The Query was working fine with a cursor to retrieve rows and send email feature in it. But when I used the same query in SQLJOB, I got this error.

    Like

  3. Here is the code snippet
    SET @tableHTML =
    N’ANCDS Application’ +
    N’Following Bulletins need attention’ +
    N’Click Dashboard to open the application or individual links below to take quick action. ‘+
    N” +
    N’Bulletin NumberBulletin Name’ +
    CAST ( ( SELECT DISTINCT
    td = NavigateURL , ”
    FROM [dbo].[vwDailySummary]
    WHERE Status = 2 AND EmailId = @EmailID
    FOR XML PATH(‘tr’), TYPE
    ).value(‘.’,’NVARCHAR(MAX)’) AS NVARCHAR(MAX) ) +
    N” ;

    Like

  4. Get this message while attaching a database to SQL 2008 R2.

    Msg 1934, Level 16, State 1, Procedure DDL_RESTRICT_DB_FILE_PLACEMENT, Line 90
    SELECT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDING’. Verify that SET options are 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.

    how to fix this?

    Like

  5. Imagine a table with an XML column (called ‘Data’) where you need to run an XPATH query on that XML column to look for some value so that you can delete that row.
    Without setting quoted identifier the following statement will fail.

    SET QUOTED_IDENTIFIER ON

    DELETE T
    FROM table T with (NOLOCK)
    WHERE Data.query(‘/X/Y[@name = “Z”]’).value(‘.’, ‘varchar(max)’) = ‘Some Value’

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s