How Does QUOTED_IDENTIFIER Works in SQL Server? – Interview Question of the Week #217

Question: How Does QUOTED_IDENTIFIER Works in SQL Server?

Answer: If you search on the internet, you may find quite lots of notes about it. So in this blog post, I will explain in very simple words.

How Does QUOTED_IDENTIFIER Works in SQL Server? - Interview Question of the Week #217 quotedidentifier

First, let us see the following example where I have kept QUOTED_IDENTIFIER OFF.

SET QUOTED_IDENTIFIER OFF
GO
SELECT "Order By"
GO

Now let us run the second example where I have kept QUOTED_IDENTIFIER ON.

SET QUOTED_IDENTIFIER ON
GO
SELECT "Order By"
GO

You can clearly see how it works from a simple example. When I have QUOTED IDENTIFIER OFF, the query will identify the double quotes and will display the valid string between the double quotes. However, when the QUOTED_IDENTIFIER ON, the query will recognize the string wrapped by the double quotes as an object.

If you ever see QUOTED IDENTIFIER at the beginning of the batch (altered or created stored procedure) the impact of the settings will be applicable for the entire batch. If the statement SET QUOTED IDENTIFIER shows up in between a stored procedure, it will have no impact and will be treated as a static SQL only.

Let me know if this simple example has helped you to understand how this particular setting works. If you have another such example, it will be very helpful for everyone and I request you post on the comments area.

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

SQL Scripts, SQL Server, SQL Server Configuration
Previous Post
Do Queries Always Respect Cost Threshold of Parallelism? – Interview Question of the Week #216
Next Post
How to Limit Output of Varchar(max), Nvarchar(max) in SELECT Statement? – Interview Question of the Week #218

Related Posts

3 Comments. Leave new

  • Kristen Kinnear-Ohlmann (@kinnear_ohlmann)
    March 26, 2019 6:28 pm

    I copied this into many scripts and stored procedures but never stopped to consider what the actual function was. Thanks for a simple, cogent explanation!

    Reply
  • Sinval Pereira
    December 5, 2019 5:35 pm

    We work a lot with XML on stored procedures and we always need to enable quoted_identifier, otherwise we always get errors!

    Reply

Leave a Reply