Newb - SQL Server IIF() Function
- ScottPletcher wrote:
From what I've read, at least, the STRING_AGG performs well.
First result I found in Google :
- Kristen-173977 wrote:Jeff Moden wrote:
COALESCE has a nasty quirk, as well. You know what it is... it uses whatever the highest precedence datatype there is in the operands. ISNULL doesn't have that problem.
That's specifically why we never use ISNULL ... I don't want two ways of doing something which have different behaviour, and I absolutely do not want someone doing maintenance to change ISNULL to COALESCE to add a third parameter and then get an insidious bug because Params 1 & 2 have precedence such that the outcome changes.
VERBOSE / STRICT that alerted to that would be helpful ... or SQL LINT ... only one I know of is https://sqlenlight.com/
To be honest, I'm actually very happy to have the choice. 😉 I'll also add that if someone doesn't know the quirks of both, I probably don't want them writing code in our databases anyway. 😀
--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) - ScottPletcher wrote:
I admit, I don't expect IIF to go away (ever), as bad an idea as it was to corrupt T-SQL with it. I'm just hoping they don't bring over things that don't believe in T-SQL.
BWAAAA-HAAAAA! You must be as big a "fan" of FORMAT as I am then... especially since <deep_sarcasm = ON> it performs so very well. <deep_sarcasm = OFF> 😀
--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) - Kristen-173977 wrote:ScottPletcher wrote:
From what I've read, at least, the STRING_AGG performs well.
First result I found in Google :
Exactly... and it's easy to use and it doesn't have any "special" entitizations to worry about deentitizing with TYPE, etc. Works fine, fails safe, and drains to the bilge. Now, if they'd just fix STRING_SPLIT() to return the ordinal position of the split elements, we'd have a complete pair of some awesome functions.
--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) - Kristen-173977 wrote:
Sorry, I have explained that badly. It definitely isn't a bug in SQL, but it can cause a bug in my software (if I change from ISNULL to COALESCE and then fail to also consider the difference that any precedence might cause).
I see that as defensive programming. We don't use ISNULL in order to avoid that risk. COALESCE works fine as an ISNULL replacement ... bit longer to type ... so we only use that.
Now that you mention implicit conversions I don't want any of those either - I would much prefer to explicitly CAST them all, so that intention is clear. But I don't have anything that alerts me if I forget to do that ... and so if I didn't intend there to be a conversion nothing is watching my back
A STRICT mode would suit me very well 🙂
ISNULL has another quirk, as well... if the 2nd operand is actually a SELECT or a function or any other type of executable, it will be executed even if it's not used. Took me a heck of a long time to trouble-shoot that "little" performance issue once-upon-a-time.
On the other hand, COALESCE takes longer to execute even with just two operands than ISNULL. To most people, that won't matter but with some of the stuff I run (sometimes measured in the 10's or 100's of million rows), every millisecond matters to me especially on some of the crazy-wide (>100 column) imports, etc, that we have to validate, groom, and tune. Yeah... no... I didn't design any of that junk, just in case you might be wondering.
--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) -
deleted
- Jeff Moden wrote:
ISNULL has another quirk, as well... if the 2nd operand is actually a SELECT or a function or any other type of executable, it will be executed even if it's not used. Took me a heck of a long time to trouble-shoot that "little" performance issue once-upon-a-time.
That I knew of ... but had forgotten (given ISNULL not used here ...)
On the other hand, COALESCE takes longer to execute even with just two operands than ISNULL
The milliseconds probably don't matter to me, but we always try to be optimal and some of our coding standards are chosen for that reason - better to start with performant than have to fix it later, but we definitely do tune queries later in their life ... so I can't say that doesn't happen.
Jeff Moden wrote:To be honest, I'm actually very happy to have the choice
OK, I'll bite, might be something fundamental that I'm missing - wouldn't be the first time! - when & why would you use ISNULL in preference to COALESCE? (other than the milliseconds saved)
I'll give you "less typing" (Hmmm ... CREATE SYNONYM ISNULL FOR COALESCE ...)
- ScottPletcher wrote:
If .Net developers can't figure out how to use a CASE statement, then train them better or get developers who can.
I don't think I'd blame this on .NET developers (although VB.NET has IIF I think...C# doesn't, uses ternary operator which can be a total clusterfrack too when it's abused). More likely it's Microsoft Access users. Access uses IIF, and having it in SQL Server might make translation of Access code easier. Just speculation though...I've had to deal with Access and translating an IIF to a corresponding CASE statement isn't always easy to do, especially if you had a "clever" Access programmer.
- dmbaker wrote:ScottPletcher wrote:
If .Net developers can't figure out how to use a CASE statement, then train them better or get developers who can.
I don't think I'd blame this on .NET developers (although VB.NET has IIF I think...C# doesn't, uses ternary operator which can be a total clusterfrack too when it's abused). More likely it's Microsoft Access users. Access uses IIF, and having it in SQL Server might make translation of Access code easier. Just speculation though...I've had to deal with Access and translating an IIF to a corresponding CASE statement isn't always easy to do, especially if you had a "clever" Access programmer.
C# has the ? : operator
so
IIF(x>0,"True","False) would be written
(x>0)? "True":"False;
VB.NET has IIF but an IF can be used in the same method:
if(x>0, "True", "False")
In VB.NET the advantage of if over iif is that it shortcuts only evaluating the result that is true.
-
Yep, as I said, C# has a ternary operator (? : ) that is logically similar to IIF, and VB has IIF, but that doesn't really have much to do with my point. The point was that I don't think the reason IIF was put into SQL Server was .NET...it seems more likely that it was because of Microsoft Access, because it does have IIF in its query language, and having it in SQL Server might make translation of Access SQL into T-SQL easier.
- dmbaker wrote:
Yep, as I said, C# has a ternary operator (? : ) that is logically similar to IIF, and VB has IIF, but that doesn't really have much to do with my point. The point was that I don't think the reason IIF was put into SQL Server was .NET...it seems more likely that it was because of Microsoft Access, because it does have IIF in its query language, and having it in SQL Server might make translation of Access SQL into T-SQL easier.
Sorry, my mistake, I misread your comment reading "although VB.NET has IIF I think...C# doesn't, uses ternary operator" as C# didn't have a ternary operator.
- dmbaker wrote:ScottPletcher wrote:
If .Net developers can't figure out how to use a CASE statement, then train them better or get developers who can.
I don't think I'd blame this on .NET developers (although VB.NET has IIF I think...C# doesn't, uses ternary operator which can be a total clusterfrack too when it's abused). More likely it's Microsoft Access users. Access uses IIF, and having it in SQL Server might make translation of Access code easier. Just speculation though...I've had to deal with Access and translating an IIF to a corresponding CASE statement isn't always easy to do, especially if you had a "clever" Access programmer.
Excellent point. You are extremely likely to be correct on that. I haven't done Access in a long time and haven't been a full-time developer for even longer. I probably assumed something not true based on my limited knowledge. After the: inherited-but-awful % strings in RAISERROR; and FORMAT(); maybe I must have kinda assumed all awful things in T-SQL come from .NET 😀
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Shifting gears a bit but on a similar subject... the PIVOT operator in MS Access is totally awesome. I do NOT understand why MS made such a crippled version in SQL Server.
--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) -
OMG I so agree, was just starting to post something about that! The one killer feature from Access that I wish was in T-SQL. Trying to translate Access crosstab queries into T-SQL is often a serious pain.
- ScottPletcher wrote:
After the: inherited-but-awful % strings in RAISERROR ...
Short of using CamelCase seems a bit non-standard to only have one "E" ... other such reserve words have "_" separator ...
Viewing 15 posts - 46 through 60 (of 61 total)
You must be logged in to reply to this topic. Login to reply