ANSI NULL SQL Server

ANSI_NULLS and QUOTED_IDENTIFIER are among the most common settings that a DBA/developer may come across in Ansi Null SQL Server. Let’s have a look at these settings in detail.

ANSI_NULLS

This is used to govern NULL comparison. When ON, ISO NULL comparison standard are followed and = and <> operators aren’t used for NULL comparison instead IS NULL and IS NOT NULL are used for NULL comparison. This value is ON by default. Let’s have a look at an example.

SET ANSI_NULLS ON
GO
CREATE TABLE tblnulls(sno INT IDENTITY,col1 CHAR(1))
GO
INSERT INTO tblnulls 
SELECT 'A'
UNION 
SELECT NULL

The above query sets ANSI_NULLS = ON and creates a table with some dummy values. Let’s do NULL comparison and analyze the results.

1_ANSI_NULLS and QUOTED_IDENTIFIER in SQL Server

As explained above, the = and <> operators didn’t return any rows whereas IS NULL and IS NOT NULL returned correct results. Let’s now switch ANSI_NULLS to OFF and analyze the resultset.

2_ANSI_NULLS and QUOTED_IDENTIFIER in SQL Server

As expected, the = and <> operator return correct result with ANSI_ NULLS OFF.

In future SQL Versions, the ANSI_NULLS will always be ON  by default and the setting it to OFF will generate errors.

 

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *