SET QUOTED IDENTIFIER option in SQL server

The SET QUOTED_IDENTIFIER statement in SQL Server controls how double quotation marks are interpreted, determining whether they delimit object identifiers (like table or column names) or literal strings. This is an ISO standard setting, and it is ON by default.

Syntex

SET QUOTED_IDENTIFIER { ON | OFF }

Most developers think SET QUOTED_IDENTIFIER only changes how double quotes work. But inside SQL Server, it decides whether expressions are safe enough to be indexed. When it is OFF, SQL Server cannot distinguish between identifiers and string literals reliably. That breaks determinism. So Microsoft blocks indexed views, filtered indexes, and persisted computed columns. That’s why our query works in SSMS but fails in applications. This one SET option controls whether SQL Server trusts our metadata.

In Microsoft SQL Server, SET QUOTED_IDENTIFIER controls how SQL Server interprets double quotes ( " " ).

Setting	Meaning
ON	"Name" = identifier (column, table, alias)
OFF	"Name" = string literal

Single quotes ' ' always mean string.

Let’s see the sample example

SET QUOTED_IDENTIFIER OFF GO SELECT "Bagesh" as 'Name' GO

It will treat as string.

Now we are making ON

SET QUOTED_IDENTIFIER ON GO SELECT "Bagesh" as 'Name' GO

Its treat as SQL objects.

Ø  SET QUOTED_IDENTIFIER OFF: If this setting is off, SQL Server treats the value inside the double quotes as a string. We can use any string in the double quotes, and SQL Server does not check for rules such as reserved keyword

Ø  SET QUOTED_IDENTIFIER ON: With this option, SQL Server treats values inside double-quotes as an identifier. It is the default setting in SQL Server. In the above example, we see that it treats the string

QUOTED_IDENTIFIER OFF is breaking below

• Persisted computed columns become unsafe
• Indexed Views become illegal
• Filtered Indexes stop working
• Schema Binding breaks
• Statistics on indexed views stop working
• Execution plan becomes unstable

Computed Column

Let’s create a table and add computed.

SET QUOTED_IDENTIFIER OFF; GO  CREATE TABLE Sales_QI_OFF_Demo (     Qty int,     Price int,     Total AS (Qty * Price) PERSISTED );

It’s breaking.

When we use ON we can create a table

SET QUOTED_IDENTIFIER ON; GO  CREATE TABLE Sales_QI_ON_Demo (     Qty int,     Price int,     Total AS (Qty * Price) PERSISTED );

Table created successfully.