Any difference between < > and NULLIF...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Any difference between < > and NULLIF...

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-25 : 11:47:25

You guys see any performance benefit / cost for using this syntax:

WHERE NullIf(columnName, '0' ) IS NOT NULL

VS.

WHERE columnName <> '0'

Thanks!

Nathan Skerl

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-25 : 11:54:58

I can see a readability benefit / cost

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-25 : 11:58:35

I totally agree. Thats why Im asking, Id like to change it.

Nathan Skerl

Kristen
Test

22859 Posts

Posted - 2005-10-25 : 12:24:21

"WHERE columnName <> '0'"

There's a "silent" failure if columnName IS NULL - so if I was going that route I would comment it as such - so that a "Junior" looking at the code in the future realises that was intentional.

I suppose


WHERE columnName <> '0' AND WHERE columnName IS NOT NULL

is completely OTT?

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-25 : 12:39:52

WHERE ColumnName <> '0' -- Note to Junior: the column allows NULL so these rows will automatically fail the test

If anything NULLIF() would be worse performancewise (note: I think...)

>> WHERE columnName <> '0' AND WHERE columnName IS NOT NULL
What version are you on ?, can't get it to work *LOL*

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-10-25 : 14:09:55

"What version are you on"

The Belt&Brace Verbose version

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-25 : 16:16:19

This seems like something that you should just run a test on to see if one or the other has a performance benefit, rather than posting a question here.

quote:
Originally posted by nathans

You guys see any performance benefit / cost for using this syntax:

WHERE NullIf(columnName, '0' ) IS NOT NULL

VS.

WHERE columnName <> '0'

Thanks!

Nathan Skerl

CODO ERGO SUM

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-25 : 16:44:23

Michael, youre absolutely right, I should worded my question a tad more specifc:

"Can anyone here think of a reason why someone would use this syntax, as I dont see anything performance-wise?"

If anything, I see the nullif function degrading performance.

Just curious if I was missing something... didnt seem right to me that someone would go out their way to code something like that.

Nathan Skerl

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-25 : 19:33:45

On the question of how to code this, I would go with the following, because it doesn't depend on the the setting of ANSI_NULLS.

where columnName <> '0' and columnName is not null

This would give a different result, depending on the setting of ANSI_NULLS.

WHERE columnName <> '0'

This script and result shows what I mean about ANSI_NULLS settings:

print 'set ansi_nulls off'
set ansi_nulls off
if null = null begin print 'null = null' end  
else begin print 'fails null = null' end
if null <> 0 begin print 'null <> 0' end   
else begin print 'fails null <> 0' end

print 'set ansi_nulls on'
set ansi_nulls on
if null = null begin print 'null = null' end  
else begin print 'fails null = null' end
if null <> 0 begin print 'null <> 0' end   
else begin print 'fails null <> 0' end


Result:
set ansi_nulls off
null = null
null <> 0
set ansi_nulls on
fails null = null
fails null <> 0

CODO ERGO SUM

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-25 : 19:41:07

Much appreciated Michael.

Nathan Skerl

Kristen
Test

22859 Posts

Posted - 2005-10-26 : 00:39:50

"ANSI_NULLS"

Jolly good point MVJ! Blasted things though, ANSI_NULLS

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-26 : 03:16:14

Got me!

... let's see ... do I have any code to rewrite ....

quote:
Blasted things though, ANSI_NULLS

Edit: thx for the update mvj.
____________________
I have null problems

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-26 : 11:03:22

Maybe ANSI_NULLS is why the original code was this:
WHERE NullIf(columnName, '0' ) IS NOT NULL

It does work the same, no matter what the setting of ANSI_NULLS.

These issues are why I include this code at the beginning of every stored procedure:
set ansi_nulls on

Does the meaning of this sentence change, depending on the setting of ANSI_NULLS?
"I have null problems"

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 01:49:36

"Does the meaning of this sentence change, depending on the setting of ANSI_NULLS?
"I have null problems""

ROTFL

I get

"I have  problems"

here - how do I supress the double-space? Are my ANSI_NULL setting wrong?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources