June 22, 2010 at 4:00 am
Hi
i picked few lines from below link
Now “IS NULL” is a little trickier and is the preferred method for evaluating the condition of a variable being NULL. When you use the “IS NULL” clause, it checks both the address of the variable and the data within the variable as being unknown. So if I for example do:
DECLARE @val CHAR(4)
If @val IS NULL
PRINT ‘TRUE’
ELSE
PRINT ‘FALSE’
SET @val = NULL
If @val IS NULL
PRINT ‘TRUE’
ELSE
PRINT ‘FALSE’
Both outputs will be TRUE. The reason is in the first @val IS NULL I have only declared the variable and no address space for data has been set which “IS NULL” check for. And in the second the value has been explicitly set to NULL which “IS NULL” checks also.
http://qa.sqlservercentral.com/articles/T-SQL/understandingthedifferencebetweenisnull/871/
Can anybody explain quoted lines , how address plays here important role ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 22, 2010 at 4:36 am
I can't say the information there is wrong, but I would say it's not relevant.
Comparing NULLS with the '=' sign will work depending on the ANSI_NULLS setting.
Comparing with IS NULL will work regardless of that setting.
I think this is enough to understand how things work. Involving considerations on the memory pointers is complicating things more than necessary, IMHO.
-- Gianluca Sartori
June 22, 2010 at 5:39 am
Although the topic is not relevant to the description and question that is being raised...
IS NULL if used against the variable will check
1. Whether the variable is assigned to any value (even if its assigned to null), if the variable is assigned the memory address will be allocated and mapped to the respective variable.
2. If the variable is assigned the value it will check whether it is null or not
This is what is explained in the description that is provided.
However, it is not concerned with the comparison IS NULL vs =NULL
Sartori has given the appropriate description about the comparison
Prashant Bhatt
Sr Engineer - Application Programming
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply