QUOTED_IDENTIFIER and ANSI_NULLS: Silent Query Killers

QUOTED_IDENTIFIER: Double Quotes or String Literals?

The Core Issue

In SQL, text strings are enclosed in single quotes: 'hello'. But what about double quotes: "hello"?

QUOTED_IDENTIFIER controls this:

Setting	Double Quotes Mean	Example
QUOTED_IDENTIFIER ON (Default)	Identifier delimiter (like square brackets)	`"ColumnName"` = a column named ColumnName
QUOTED_IDENTIFIER OFF	String literal (like single quotes)	`"hello"` = a string 'hello'

Example: ON vs. OFF

-- QUOTED_IDENTIFIER ON (default in SSMS and modern tools)
SET QUOTED_IDENTIFIER ON;

SELECT "UserID", "UserName" FROM dbo.Users;
-- ✓ Selects columns UserID and UserName

WHERE "Status" = 'Active'
-- ✓ Filters where column Status equals string 'Active'

-- QUOTED_IDENTIFIER OFF (legacy, rare)
SET QUOTED_IDENTIFIER OFF;

SELECT "UserID", "UserName" FROM dbo.Users;
-- ✗ ERROR: "UserID" is interpreted as a string literal
-- SQL Server tries to find columns named [the string "UserID"]
-- which doesn't exist

WHERE "Status" = 'Active'
-- ✓ This works: "Status" is treated as string literal
-- But then it tries to compare string "Status" = 'Active'
-- Which fails because "Status" is not a column

Why This Matters: The Stored Procedure Problem

-- Created in SSMS (QUOTED_IDENTIFIER ON by default)
CREATE PROCEDURE usp_GetActiveUsers
AS
BEGIN
  SELECT "UserID", "UserName", "Email"
  FROM dbo.Users
  WHERE "Status" = 'Active';
END;

-- Procedure created successfully ✓
-- But what if someone sets QUOTED_IDENTIFIER OFF before executing?

SET QUOTED_IDENTIFIER OFF;
EXEC usp_GetActiveUsers;

-- The procedure was CREATED with QUOTED_IDENTIFIER ON
-- So the double quotes mean "column names"
-- But now the session has QUOTED_IDENTIFIER OFF
-- Does the procedure use the OLD setting (ON) or the NEW setting (OFF)?

-- Answer: The setting when the procedure was CREATED
-- Stored procedures preserve the QUOTED_IDENTIFIER setting from creation time

Key insight: Stored procedures, views, and functions capture QUOTED_IDENTIFIER (and ANSI_NULLS) at creation time. They always use those settings, regardless of the session setting that executes them.

ANSI_NULLS: The Null Comparison Mystery

The Core Issue

How should NULL compare to NULL or other values?

Setting	NULL = NULL	NULL = Value	Usage
ANSI_NULLS ON (Standard)	Unknown (treated as false)	Unknown (treated as false)	ANSI SQL standard, use with indexes
ANSI_NULLS OFF (Backward compat)	True	False (but allows = operator)	Legacy code only

Example: ANSI_NULLS ON (Correct)

-- ANSI_NULLS ON (default, ANSI standard)
SET ANSI_NULLS ON;

SELECT * FROM dbo.Users WHERE Status = NULL;
-- ✗ Returns 0 rows (even if Status IS NULL in some rows!)
-- Reason: NULL = NULL evaluates to UNKNOWN, not TRUE

SELECT * FROM dbo.Users WHERE Status IS NULL;
-- ✓ Returns all rows where Status is NULL
-- This is the CORRECT way to check for NULL with ANSI_NULLS ON

Example: ANSI_NULLS OFF (Wrong, but Backward Compatible)

-- ANSI_NULLS OFF (legacy, deprecated)
SET ANSI_NULLS OFF;

SELECT * FROM dbo.Users WHERE Status = NULL;
-- ✓ Returns rows where Status IS NULL
-- But violates ANSI standard!
-- Also breaks index usage (optimizer assumes ANSI NULL semantics)

SELECT * FROM dbo.Users WHERE Status IS NULL;
-- ✓ Still works correctly
-- But the = operator also works (unusual)

⚠ Critical: With ANSI_NULLS OFF, indexes may not be used correctly because the optimizer assumes ANSI NULL semantics. Your query might scan the whole table instead of using an index.

The Real-World Problem: Unexpected Query Behavior

Scenario: Migration from Legacy System

-- Old legacy database (ANSI_NULLS OFF for backward compatibility)
-- Old query that worked:
SELECT * FROM Orders WHERE DiscountCode = NULL;
-- This returned all orders with NULL discount code (worked by accident!)

-- New system migrated to modern SQL Server (ANSI_NULLS ON)
-- Same query now returns 0 rows (disaster!)
-- Application that relied on this query fails silently

-- DBA debugging:
-- "Query works in SSMS but not in the application!"
-- Reason: Application's connection string sets ANSI_NULLS OFF
-- But procedure was created with ANSI_NULLS ON
-- Conflict!

Scenario: Linked Server or ODBC Connection

-- ODBC drivers and older connection strings set ANSI_NULLS OFF
-- When connecting to modern SQL Server (which defaults to ON)
-- You get unexpected NULL behavior

-- Connection string (ODBC):
-- "Driver={ODBC Driver 17 for SQL Server};Server=localhost;Database=MyDB;
--  Trusted_Connection=yes;ANSI_NULLS=0;"

-- This overrides SQL Server's default ANSI_NULLS ON
-- Queries behave unexpectedly

Default Settings by Context

Context	QUOTED_IDENTIFIER	ANSI_NULLS
SSMS (SQL Query Editor)	ON	ON
sqlcmd (Command Line)	ON	ON
ODBC Driver (older)	OFF	OFF
OLEDB Provider	ON	ON
.NET SqlClient (default)	ON	ON
Stored Procedure Creation	Uses current session setting at creation time	Uses current session setting at creation time

Best Practices

Rule 1: Always Use QUOTED_IDENTIFIER ON

-- At the top of any script creating procedures, views, functions:
SET QUOTED_IDENTIFIER ON;

-- Then use square brackets instead of double quotes for clarity:
SELECT [UserID], [UserName] FROM dbo.Users;

-- NOT double quotes (confusing and non-portable):
-- SELECT "UserID", "UserName" FROM dbo.Users;

Rule 2: Always Use ANSI_NULLS ON

-- At the top of any script:
SET ANSI_NULLS ON;

-- Then always use IS NULL / IS NOT NULL for NULL checks:
WHERE Status IS NULL
WHERE Status IS NOT NULL

-- NEVER use = NULL or != NULL (even though ANSI_NULLS ON makes it return 0 rows)

Rule 3: Make Defaults Explicit in Procedures

-- Best practice: Set at the top of every procedure
CREATE PROCEDURE usp_GetActiveUsers
AS
BEGIN
  SET QUOTED_IDENTIFIER ON;
  SET ANSI_NULLS ON;

  SELECT UserID, UserName, Email
  FROM dbo.Users
  WHERE Status IS NULL;
END;

Rule 4: Check Your Stored Procedures

-- Query to find procedures created with unexpected settings:
SELECT
  OBJECT_NAME(object_id) AS procedure_name,
  uses_quoted_identifier,
  uses_ansi_nulls
FROM sys.sql_modules
WHERE object_id IN (SELECT object_id FROM sys.procedures)
  AND (uses_quoted_identifier = 0 OR uses_ansi_nulls = 0);

-- Any result = potential problem!
-- Recreate these procedures with proper settings

Debugging: How to Tell What Setting a Procedure Uses

-- Query the actual stored procedure definition:
SELECT
  OBJECT_NAME(object_id) AS procedure_name,
  uses_quoted_identifier,
  uses_ansi_nulls,
  definition
FROM sys.sql_modules
WHERE OBJECT_NAME(object_id) = 'usp_GetActiveUsers';

-- Check the columns:
-- uses_quoted_identifier: 1 = ON, 0 = OFF
-- uses_ansi_nulls: 1 = ON, 0 = OFF

Connection String Examples

.NET SqlClient (Correct)

// Modern .NET uses ANSI_NULLS ON and QUOTED_IDENTIFIER ON by default
// But be explicit for clarity:

string connectionString = @"
  Server=localhost;
  Database=MyDatabase;
  Integrated Security=true;
  ANSI_NULLS=true;
  QUOTED_IDENTIFIER=true;
";

ODBC (Watch Out!)

// Older ODBC drivers default to OFF
// Explicitly set to ON for modern SQL Server:

string connectionString = @"
  Driver={ODBC Driver 17 for SQL Server};
  Server=localhost;
  Database=MyDatabase;
  Trusted_Connection=yes;
  ANSI_NULLS=1;
  QUOTED_IDENTIFIER=1;
";

Common Mistakes & Symptoms

Mistake	Symptom	Root Cause	Fix
Using double quotes for identifiers	Column not found error	QUOTED_IDENTIFIER OFF interprets as string	Use brackets [ColumnName] instead
WHERE Col = NULL never matches	Query returns 0 rows unexpectedly	ANSI_NULLS ON (correct behavior, but wrong pattern)	Use IS NULL instead
Procedure works in SSMS but not in app	Inconsistent behavior	Session settings differ	Set both in procedure definition
Index not used (table scan)	Slow query with = NULL	ANSI_NULLS OFF breaks index assumptions	Switch to ANSI_NULLS ON and use IS NULL

Migration Checklist

☐ Audit all stored procedures for QUOTED_IDENTIFIER and ANSI_NULLS settings
☐ Identify procedures created with OFF (legacy)
☐ Recreate procedures with ON settings
☐ Update all application connection strings to use ON settings
☐ Replace all = NULL with IS NULL (or IS NOT NULL)
☐ Test queries that filter on NULL columns (verify they use indexes)
☐ Verify ODBC driver version (update to 17+ if possible)
☐ Document that all new code must use ON for both settings

The Bottom Line

These settings are invisible but powerful. They control how your queries parse and execute. Ignore them, and you'll spend days debugging why a query works in SSMS but not in your application.

Rule of thumb: Always set both to ON. Use square brackets for identifiers. Use IS NULL for NULL checks. Document these requirements. Done.