|
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
http://
http://
Contributed by:
Rohit kakria
I am software developer
Resourse address on xpode.com
http://www.xpode.com/Print.aspx?Articleid=599
Click here to go on website
|