SET ANSI_NULLS ON – SQL Server

The setting for ANSI NULLS should be ON while running distributed queries. When creating or altering indexes on calculated columns or indexed views, ANSI NULLS must also be ON. Any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on calculated columns or indexed views will fail if SET ANSI NULLS is set to OFF.

The documentation says –

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are non-null values in column_name.

SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

Conclusion: When writing a select statement with ANSI NULLS set to “ON” and applying =, <> to a NULL column value, no results will be returned.

CREATE TABLE tbProducts
(ProductID INT IDENTITY(1,1) PRIMARY KEY, ProductName VARCHAR(100))

INSERT INTO tbProducts (ProductName) VALUES
(‘Samsung S20’),(‘Samsung M20’),(‘iPhone14’),(‘OnePlus10’),(NULL)

when – SET ANSI_NULLS ON

SELECT * FROM tbProducts WHERE ename IS NULL
Result: 1 row(s) affected
SELECT * FROM tbProducts WHERE ename = NULL
Result: 0 row(s) affected
SELECT * FROM tbProducts WHERE ename IS not NULL
Result: 4 row(s) affected
SELECT * FROM tbProducts WHERE ename <> NULL
Result: 0 row(s) affected

when – SET ANSI_NULLS OFF

SELECT * FROM tbProducts WHERE ename IS NULL
Result: 1 row(s) affected
SELECT * FROM tbProducts WHERE ename = NULL
Result: 1 row(s) affected)
SELECT * FROM tbProducts WHERE ename IS NOT NULL
Result: 4 row(s) affected)
SELECT * FROM tbProducts WHERE ename <> NULL
Result: 4 row(s) affected)