general

Question

general

Viewing 7 posts - 16 through 21 (of 21 total)

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

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

paul.knibbs (6/27/2013)
Is ISNULL such an expensive operation, then? (Genuinely curious, never actually sat down and analysed it)...

It's not expensive, but it's not free either. Have a look at the actual plan for this:

SELECT Number = ISNULL(n,0)

FROM (SELECT n = 1 UNION ALL SELECT NULL) d

^{“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

paul.knibbs SSCoach Points: 15320 More actions · Answer 2

Is the "Compute Scalar" part the bit that is ISNULL?

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

paul.knibbs (6/27/2013)
Is the "Compute Scalar" part the bit that is ISNULL?

Right-click on the Compute Scalar operator to raise the properties sheet.

^{“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

Afdsahauhan SSC Enthusiast Points: 118 More actions · Answer 4

@ Kingston Dhasian

i dont think so...

as question says...

selects all orders with zeroes or NULLs in the order_amt field.

so how my query is just reverse?? it will select those orders which has order_amt null or 0...

Afdsahauhan SSC Enthusiast Points: 118 More actions · Answer 5

Kingston Dhasian (6/25/2013)
Adarsh Chauhan (6/25/2013)
select * from Orders
where isnull(order_amt,0) =0
or
select * from Orders
where order_amt is null or order_amt = 0
This is exactly the opposite of what the OP wants

I dont think so... as OP said 'select all orders save with zeroes or NULLs in the order_amt field.

and if he wants reverse of it then he can use is not null and !=0 simple...:-D

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

paul.knibbs (6/26/2013)
Jeff Moden (6/25/2013)
No. NULLs cannot be compared to so they would also be excluded from any check for <> 0. Try it an see.
I would tend to use ISNULL(value, 0) <> 0 anyway, though, because I don't like things that works differently if you use a different comparison operator! 🙂
(By which I mean--if the requirement changed so you had to find all the items where the amount *was* 0 or NULL, you'd get invalid results if you forgot to change the 0 to the ISNULL version--safer to have it that way in the first place).

Just keep in mind that encapsulating a column in a function can very easily make for a full table scan. Permanently sacrificing performance to satisfy a programming tic for a change in requirements that may never happen seems a little counter productive.

--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)
Intro to Tally Tables and Functions

TomThomson SSC Guru Points: 104773 More actions · Answer 7

paul.knibbs (6/26/2013)
Not sure how I can explain my reasoning (or lack thereof, probably) more clearly, sorry...

I guess your point is that if you take Chris's code (or equivalently Jeff's suggestion) and change "Amt <> 0" to either "Amt = 0" or "not Amt <> 0" you just get the 0s, not the NULLs, while the unchanged code excludes both 0s and NULLs.

To me, this is just an instance of the general rule that if you want to change the code to do something different you make sure that the change you make takes account of any use of 3-valued logic to make a two-way choice.

So yes, if you are careless about how you change things when the requirement changes it might be better to write the more verbose version even when it isn't needed; on the other hand, if you aren't careless the shorter version might be better, since the short version may well do much less IO than the verbose version.

Tom