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)
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!
Thank you so much for your kind note.
We work a lot with XML on stored procedures and we always need to enable quoted_identifier, otherwise we always get errors!