Keywords: SQL Server | ANSI_NULLS | NULL Handling
Abstract: This paper provides an in-depth examination of the SET ANSI_NULLS ON setting in SQL Server and its impact on query processing. By analyzing NULL handling logic under ANSI SQL standards, it explains how comparison operations involving NULL values yield UNKNOWN results when ANSI_NULLS is ON, causing WHERE clauses to filter out relevant rows. Through concrete code examples, the article illustrates the effects of this setting on equality comparisons, JOIN operations, and stored procedures, emphasizing the importance of maintaining ANSI_NULLS ON in modern SQL Server versions.
Core Concepts of ANSI_NULLS Setting
In SQL Server, SET ANSI_NULLS is a critical session-level setting that governs how NULL values are handled in comparison operations. When set to ON, the system adheres to ANSI SQL standards, treating NULL as an unknown value (UNKNOWN) rather than a specific data value. This approach directly affects the correctness and consistency of query results.
Semantic Analysis of NULL Comparisons
According to ANSI SQL standards, NULL represents missing or unknown data. With SET ANSI_NULLS ON, any comparison operation involving NULL returns UNKNOWN, not TRUE or FALSE. For example, consider the following query:
SELECT Region FROM employees WHERE Region = NULLEven if rows with NULL Region exist in the table, this query returns no results because the comparison Region = NULL evaluates to UNKNOWN. The WHERE clause retains only rows where the condition evaluates to TRUE, and UNKNOWN causes rows to be filtered out.
Practical Scenarios with Variable Comparisons
A common question is whether ANSI_NULLS setting still applies when using variables in comparisons. The answer is yes. Consider this example:
DECLARE @region VARCHAR(50) = NULL
SELECT Region FROM employees WHERE Region = @regionWhen @region is NULL and SET ANSI_NULLS ON, the query returns no results even if employees table contains rows with NULL Region. This occurs because Region = @region comparison yields UNKNOWN when @region is NULL.
NULL Handling in JOIN Operations
The ANSI_NULLS setting also impacts JOIN operations. When comparing two columns that may be NULL, even if both are NULL, the comparison does not return TRUE. For instance:
CREATE TABLE #T1 (ID INT NOT NULL, Val1 VARCHAR(10) NULL)
INSERT INTO #T1 VALUES (1, NULL)
CREATE TABLE #T2 (ID INT NOT NULL, Val1 VARCHAR(10) NULL)
INSERT INTO #T2 VALUES (1, NULL)
SELECT * FROM #T1 t1 INNER JOIN #T2 t2 ON t1.ID = t2.ID AND t1.Val1 = t2.Val1This query returns 0 rows because t1.Val1 = t2.Val1 evaluates to UNKNOWN when both are NULL. To correctly match NULL values, use IS NULL conditions:
SELECT * FROM #T1 t1 INNER JOIN #T2 t2
ON t1.ID = t2.ID
AND (t1.Val1 = t2.Val1 OR (t1.Val1 IS NULL AND t2.Val1 IS NULL))Legacy Behavior with ANSI_NULLS OFF
When SET ANSI_NULLS OFF, SQL Server employs non-standard behavior, allowing NULL = NULL comparisons to return TRUE. For example:
SET ANSI_NULLS OFF
SELECT * FROM employees WHERE Region = NULLThis query returns all rows where Region is NULL. However, this mode is deprecated and should not be used in modern SQL Server versions, as it compromises query portability and predictability.
Best Practices and Version Compatibility
Since SQL Server 2005, SET ANSI_NULLS ON has been the default setting, and SET ANSI_NULLS OFF will be removed in future versions. In stored procedures and dynamic SQL, explicitly setting ANSI_NULLS ensures consistent code behavior. It is recommended to always use IS NULL and IS NOT NULL for NULL checks, avoiding reliance on comparison operators.
Conclusion
SET ANSI_NULLS ON ensures SQL Server follows ANSI SQL standards by treating NULL as an unknown value, causing comparison operations to yield UNKNOWN results. This setting affects all comparisons involving NULL, including variable comparisons and JOIN conditions. Developers should understand this semantics and adopt IS NULL for NULL checks to write robust, portable query code.