SET ANSI_NULLS ON / OFF – ?

Notes:-

1.They are used in objects like stored procedures and functions.

2.This is a setting for ANSI NULL comparisions.

3.If the option is ON , any statement that compares a identifier with a null returns a value 0.

4.IF the option is OFF , any statement that compares a identifier with a null returns a NULL means nothing.

5.You can set the option like SET ANSI_NULLS ON GO at the begining of the stored procedure.

Below example will explain the things in detail

–Create a table
CREATE TABLE TESTANSINNULL
(
id int identity(1,1) primary key,
[name] varchar(100) NULL
)

–Insert some values in the table
INSERT INTO TESTANSINNULL VALUES ( NULL )
INSERT INTO TESTANSINNULL VALUES ( NULL )
INSERT INTO TESTANSINNULL VALUES ( NULL )
INSERT INTO TESTANSINNULL VALUES ( NULL )
INSERT INTO TESTANSINNULL VALUES ( NULL )
INSERT INTO TESTANSINNULL VALUES ( ‘Pawan’ )

SELECT * from TESTANSINNULL — Check the table values

OUTPUT
_________
id name
———– ———-
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
6 Pawan

(6 row(s) affected)

1.
FIRST MAKE THE OPTION OFF
SET ANSI_NULLS OFF
SELECT * FROM TESTANSINNULL where name = null

OUTPUT — This has return all the rows in which the name is null
________

id name
———– ——–
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL

(5 row(s) affected)

2.
FIRST MAKE THE OPTION ON
SET ANSI_NULLS ON
SELECT * FROM TESTANSINNULL where name = null

OUTPUT — This has return 0 rows.
________

id name
———– ——

(0 row(s) affected)

USE OF IS NULL

If you use IS NULL then the queries will return all the rows in which name is null.
In this case ON or OFF doesnt matter . Try out the below examples.

3.
SET ANSI_NULLS ON
SELECT * FROM TESTANSINNULL where name IS NULL

OUTPUT — This has return all the rows in which the name is null
________

id name
———– ——–
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL

(5 row(s) affected)

4.
SET ANSI_NULLS OFF
SELECT * FROM TESTANSINNULL where name IS NULL

OUTPUT — This has return all the rows in which the name is null
________

id name
———– ——–
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL

(5 row(s) affected)

PLEASE NOTE :

1.ANSI_NULLS affect the result of equal to (=)
2.ANSI_NULLS does not affect the result of ISNULL in case of NULL comparisions.

Hence always use ISNULL for checking NULL values.

Pawan Kumar
Pawankkmr@hotmail.com