Is there a more efficent way

Question

Is there a more efficent way

smallmoney

SSCarpal Tunnel

Points: 4378
More actions
December 13, 2010 at 1:10 pm

#230720

In my environment I've seen:
1. WHERE ISNULL(burgertype, 'Bison Burger') <> 'Cheese Burger'
2. WHERE (burgertype IS NOT NULL OR burgertype <> 'Cheese Burger'
I'm just curious if there is a more efficent way and if those two are the exact same thing.

Viewing 5 posts - 1 through 4 (of 4 total)

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

smallmoney SSCarpal Tunnel Points: 4378 More actions · Answer 1

SQL Iron Chef (12/13/2010)
In my environment I've seen:
1. WHERE ISNULL(burgertype, 'Bison Burger') <> 'Cheese Burger'
2. WHERE (burgertype IS NOT NULL OR burgertype <> 'Cheese Burger'
I'm just curious if there is a more efficent way and if those two are the exact same thing.

My guess is there not the samething because one is injecting a value if it's null. while the other is looking for nulls or not cheese burgers

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

SQL Iron Chef (12/13/2010)
In my environment I've seen:
1. WHERE ISNULL(burgertype, 'Bison Burger') <> 'Cheese Burger'
2. WHERE (burgertype IS NOT NULL OR burgertype <> 'Cheese Burger'
I'm just curious if there is a more efficent way and if those two are the exact same thing.

Just offhand:

2. WHERE (burgertype IS NOT NULL OR burgertype <> 'Cheese Burger'

will include Cheese Burger, because it is not null. I'm assuming you meant burgertype IS NULL or..., and usually it's @burgertype IS NULL OR burgertype <> @burgerType.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

smallmoney SSCarpal Tunnel Points: 4378 More actions · Answer 4

Craig Farrell (12/13/2010)
SQL Iron Chef (12/13/2010)
In my environment I've seen:
1. WHERE ISNULL(burgertype, 'Bison Burger') <> 'Cheese Burger'
2. WHERE (burgertype IS NOT NULL OR burgertype <> 'Cheese Burger'
I'm just curious if there is a more efficent way and if those two are the exact same thing.
Just offhand:
2. WHERE (burgertype IS NOT NULL OR burgertype <> 'Cheese Burger'
will include Cheese Burger, because it is not null. I'm assuming you meant burgertype IS NULL or..., and usually it's @burgertype IS NULL OR burgertype <> @burgerType.

" I'm assuming you meant burgertype IS NULL"

I did, sorry for the typo.

Thanks guys