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

Question: What does QUOTED_IDENTIFIER change? With ON, double quotes delimit identifiers. With OFF, double quotes can delimit string literals. Single quotes are the reliable way to write string literals in either case.

There are many notes about this setting, but the original small example is enough to see the important difference. First:

SET QUOTED_IDENTIFIER OFF;
GO
SELECT "Order By";
GO

This returns the text Order By. Now run:

SET QUOTED_IDENTIFIER ON;
GO
SELECT "Order By";
GO

This attempts to reference a column named Order By. Because the example has no such column, SQL Server reports error 207, Invalid column name. It does not display the same literal again.

SELECT 'Order By' AS LiteralValue;

The single-quoted form returns the text with ON or OFF. Square-bracket identifiers such as [Order By] are unaffected by QUOTED_IDENTIFIER.

The setting acts at parse time. In an ordinary ad hoc batch, a SET changes parsing from that point onward; saying it necessarily affects every statement in the whole batch is too broad. For static SQL in a stored procedure, SQL Server uses the setting captured when that procedure was created or altered. Putting SET QUOTED_IDENTIFIER inside its body doesn’t reparse that static SQL under a new setting.

Keep ON for normal work. It is required for filtered indexes, indexed views and indexes on computed columns. The demonstration changes only its own session and ends with ON.

I still like this example because one pair of double quotes changes meaning visibly. If you have another concise example that explains a tricky setting, please share it in the comments.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.