Explanation of SET QUOTED_IDENTIFIER ON

Understanding SET QUOTED_IDENTIFIER ON

The SET QUOTED_IDENTIFIER ON setting in SQL Server controls how double quotes are interpreted when used with object names and string literals. When enabled, it allows greater flexibility and ensures that your code adheres to SQL standards.

✅ How It Works

• Identifiers can be enclosed in double quotes and treated as object names.
• String literals must be enclosed in single quotes.

Example – With QUOTED_IDENTIFIER ON

SET QUOTED_IDENTIFIER ON;

CREATE TABLE "Order" (
    "OrderID" INT PRIMARY KEY,
    "Customer Name" NVARCHAR(100)
);
    

This works because:

• "Order" is allowed even though it’s a reserved keyword.
• "Customer Name" is valid even with a space.

Example – With QUOTED_IDENTIFIER OFF

SET QUOTED_IDENTIFIER OFF;

CREATE TABLE "Order" (
    "OrderID" INT PRIMARY KEY,
    "Customer Name" NVARCHAR(100)
);
    

This will throw an error because double quotes are not allowed for identifiers in this mode.

✅ Why Use QUOTED_IDENTIFIER ON

• Allows reserved keywords as object names: e.g., "Order", "Select".
• Supports spaces and special characters: e.g., "Customer Name".
• Ensures ANSI SQL compliance: Makes your code portable and standardized.
• Required for advanced SQL Server features: indexed views, filtered indexes, computed columns.
• Avoids ambiguity: Clearly differentiates identifiers and string literals.

Best Practices: Always enable SET QUOTED_IDENTIFIER ON before creating procedures, views, or indexes. Use double quotes for identifiers and single quotes for string literals.

✅ When Should You Use It?

• Creating objects with names that are reserved keywords.
• Defining columns with spaces or special characters.
• Writing portable and standards-compliant SQL scripts.
• Implementing indexed views, filtered indexes, or computed columns.

📘 Summary

• SET QUOTED_IDENTIFIER ON enables the use of double quotes for identifiers.
• Identifiers can include spaces, special characters, or reserved keywords.
• String literals must be enclosed in single quotes.
• This setting helps with compliance, clarity, and advanced SQL Server functionality.