Click here to hide categories Click here to show left categories

User: Home          welcome : Guest          Log In / Register here     




SET ANSI_NULLS explanation

This option specifies the setting for ANSI NULL comparisons. When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.

SET ANSI_NULLS ON : When on then It don’t show the Null Values and return 0
SET ANSI_NULLS OFF: When Off It it shows the null values

Example :  We are taking below table example.

empid

empname

phone

1

rohit

NULL

2

rohit2

NULL

3

karan

NULL

5

test

NULL

8

test

UnknownNumber


Example:
SET ANSI_NULLS ON
SELECT empname FROM emp1 WHERE phone=NULL

Explanation:
It will return nothing because SET ANSI_NULLS is ON.
Example:
SET ANSI_NULLS Off
SELECT empname FROM emp1 WHERE phone=NULL

Explanation:

It will return first four where phone is null because SET ANSI_NULLS is Off.

If we will run below update query then it will not effect any thing, because SET ANSI_NULLS is ON and it is returning 0 comparison in result. So It will show 0 rows effected.

SET ANSI_NULLS ON
update emp1 set phone='myphonenumber' where phone=Null
Share this article   |    Print    |    Article read by 1480 times
Author:
Rohit kakria
I am software developer
Related Articles: No related article
Related Interview Questions: No related interview question