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.

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)