Explanation of SET ANSI_NULLS ON

Understanding SET ANSI_NULLS ON

The SET ANSI_NULLS ON setting controls how SQL Server handles NULL values during comparisons, ensuring that the behavior follows the ANSI SQL standard.

✅ How It Works

When ANSI_NULLS is ON, comparisons like = NULL or <> NULL will always return FALSE or UNKNOWN. You must explicitly use IS NULL or IS NOT NULL to check for NULL values.

Example – With ANSI_NULLS ON

SET ANSI_NULLS ON;

SELECT * FROM Employees WHERE ManagerID = NULL;
    

This query will not return rows with NULL values. Use:

SELECT * FROM Employees WHERE ManagerID IS NULL;
    

Example – With ANSI_NULLS OFF

SET ANSI_NULLS OFF;

SELECT * FROM Employees WHERE ManagerID = NULL;
    

This will treat NULL = NULL as TRUE and return rows with NULL.

✅ Why Use ANSI_NULLS ON

• Standards Compliance: Aligns with the ANSI SQL standard.
• Data Consistency: Forces explicit handling of NULL.
• Modern SQL Server Requirements: Needed for indexed views, filtered indexes, and computed columns.
• Avoid Logical Errors: Prevents incorrect or incomplete query results.

Best Practices: Always use SET ANSI_NULLS ON when creating procedures, views, or functions and use IS NULL or IS NOT NULL for comparisons.

✅ When It Matters

• Filtering data where nullable columns are involved.
• Creating procedures, views, and functions.
• Handling joins and conditions that might involve NULL values.
• Working with indexed views and computed columns.

📘 Summary

• SET ANSI_NULLS ON ensures proper handling of NULL values.
• Comparisons with NULL must use IS NULL or IS NOT NULL.
• It improves code portability, consistency, and prevents subtle bugs.
• It’s required for many modern SQL Server features.