ISNULL of an INTEGER

Question

Post reply

ISNULL of an INTEGER

imohamed

SSC Veteran

Points: 274
More actions
December 3, 2010 at 8:42 am

#232141

I discovered a functionality of ISNULL today that I hope there is a good explanation for it.
Eg: Table "T1" has an integer field "A" which is either 0 or NULL. The following 2 queries behave exactly the same.
a) SELECT * FROM T1 WHERE ISNULL(T1.A,0) = 0
b) SELECT * FROM T1 WHERE ISNULL(T1.A,'') = ''
The above 2 queries return exactly the same result. It seems to me the ISNULL treats empty string '' as 0 (zero) when it evaluates an integer column. Is this a default feature of ISNULL?
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

It's an example of implicit conversion

SELECT CAST('' AS INT)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

imohamed SSC Veteran Points: 274 More actions · Answer 2

Thanks Chris. Just wanted to confirm that it's safe to use it either way.

Rydunzel Valued Member Points: 67 More actions · Answer 3

This isn't an issue with the ISNULL funtion, it is just how the integer zero is treated.

I have a posting that addresses my frustration of this.

I believe that an integer of zero should equal zero and nothing else.

SELECT CASE WHEN 0 = '' THEN 'True' ELSE 'False' END

SELECT CASE WHEN 0 = 0 THEN 'True' ELSE 'False' END

SELECT CASE WHEN 10 = '' THEN 'True' ELSE 'False' END

SELECT CASE WHEN '0' = '' THEN 'True' ELSE 'False' END

drew.allen SSC Guru Points: 77014 More actions · Answer 4

Rydunzel (1/17/2011)
This isn't an issue with the ISNULL funtion, it is just how the integer zero is treated.

No, it's how the STRING '' is treated and it follows logically from certain numeric properties, so it's not likely to change.

I have a posting that addresses my frustration of this.

Do you also object to the fact that Cast('1' as int) = Cast('01' as int)?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

imohamed (12/3/2010)
I discovered a functionality of ISNULL today that I hope there is a good explanation for it.
Eg: Table "T1" has an integer field "A" which is either 0 or NULL. The following 2 queries behave exactly the same.
a) SELECT * FROM T1 WHERE ISNULL(T1.A,0) = 0
b) SELECT * FROM T1 WHERE ISNULL(T1.A,'') = ''
The above 2 queries return exactly the same result. It seems to me the ISNULL treats empty string '' as 0 (zero) when it evaluates an integer column. Is this a default feature of ISNULL?
Thanks

They're not the same. They may result in the same answer, but that's where the similarity ends. As already pointed out in a previous post on this thread, there's and implicit conversion from '' to 0. That's an extra conversion and although the amount of time it takes is quite small, it does add up especially on a heavy hit site or in heavy-lifting batch code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)