March 1, 2021 at 11:59 pm
I have to disagree a bit on hating "ordering" in an RDBMS... indexes would simply be of no use without "ordering". The "order" of things is quite critical in RDBMS's and not just where indexes come into play.
I also have a bit of dislike for the data precedence observed in COALESCE because of the things that's really detrimental to performance is datatype mismatches. At the very least, it forgives and, perhaps, perpetuates some really bad data design decisions, IMHO.
Anyway, very cool history lesson about how COALESCE got named, Joe. Thank you.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2021 at 6:11 am
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/
March 2, 2021 at 9:29 am
... 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.
Why would you call that a bug just because the software does not work the way you want it to, it does what it does the way it was designed, just like ISNULL works they way it does. It is the same with all implicit conversions in SQL, if you use COALESCE with varying different datatypes without CASTing then you deserve all the problems you bemoan about. Why would you code in T-SQL without checking the usage first, isn't that the height of hubris.
Far away is close at hand in the images of elsewhere.
Anon.
March 2, 2021 at 10:01 am
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 🙂
March 2, 2021 at 10:01 am
IIF is so unlike other other functions and expressions in SQL that it shouldn't be there. That is, nowhere else in SQL does it enclose conditions and the results of it in parentheses separated by commas. It's like putting Russian in the middle of English, it's just jarring.
The CONCAT and CONVERT functions both enclose conditions and the results in parentheses separated by commas.
I don't mind IIF, I seldom use it but do occasionally use it for putting a simple condition on one line in a concise format. I do think it should never be nested as the resulting code is unreadable.
March 2, 2021 at 2:43 pm
As a guy who constantly advocates for ANSI/ISO standards, I would like to point out that the CONCAT is not SQL but local dialect. The correct syntax is in fixed operator, consisting of two pipes which Microsoft does not support. It's the equivalent of the + and Sybase dialect. We took the two pipes from the PL/1 language.
Likewise, CONVERT is a piece of Sybase dialect that was taken from COBOL. It was literally added so that COBOL programmers could do display formatting in the database tier instead of going out to a presentation layer. Like you are supposed to in a tiered architecture. Both of these are pretty much like Chinese or Russian in the middle of an English document.
Please post DDL and follow ANSI/ISO standards when asking for help.
March 2, 2021 at 2:50 pm
As a guy who constantly advocates for ANSI/ISO standards, I would like to point out that the CONCAT is not SQL but local dialect. The correct syntax is in fixed operator, consisting of two pipes which Microsoft does not support. It's the equivalent of the + and Sybase dialect. We took the two pipes from the PL/1 language.
Likewise, CONVERT is a piece of Sybase dialect that was taken from COBOL. It was literally added so that COBOL programmers could do display formatting in the database tier instead of going out to a presentation layer. Like you are supposed to in a tiered architecture. Both of these are pretty much like Chinese or Russian in the middle of an English document.
What alternative would you propose to CONVERT other than using Chinese/Russian style?
March 2, 2021 at 3:14 pm
CONCAT and CONVERT do not work on conditions, i.e. comparisons. They work on expressions, as do all SQL functions.
That's why I used IF to compare to IIF -- it's one of the very other comparison methods in T-SQL. Within SQL, CASE is the standard comparison method. IIF offers no real advantage over CASE -- in fact it has a big disadvantage when embedding / using multiple comparisons -- so there's no need for it. It's just there as a crutch to developers.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 2, 2021 at 5:47 pm
IIF(column1 = 'A', 1, 2) = CASE WHEN column1 = 'A' THEN 1 ELSE 2 END
NULLIF(column1, '') = CASE WHEN column1 = '' THEN NULL ELSE column1 END
COALESCE(column1, column2, '') = CASE WHEN column1 IS NOT NULL THEN column1 WHEN column2 IS NOT NULL THEN column2 ELSE '' END
SQL Server evaluates each function to its corresponding CASE expression. The only difference is that for IIF the conditional is defined by the user - where the conditional for the others is defined by SQL and the function being used.
The arguments against IIF seem to be:
IIF is a function - not an expression and the format of a function call is maintained the same way as calling any function in SQL Server. The format is the same as ISNULL, NULLIF, COALESCE, etc. Comparing a function to an expression isn't really a valid argument as they are different constructs.
Defensive programming: if the possibility exists that a future change would require removing the function and changing to a CASE expression, then in reality you should not be using NULLIF or COALESCE or CHOOSE either. The same possibility exists with these as exists with IIF.
CASE is an expression - different construct than a function. Comparing a function to the CASE expression is meaningless.
If you want to prevent usage of a function in your systems - then by all means set your standards for your shop.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 2, 2021 at 6:02 pm
As a guy who constantly advocates for ANSI/ISO standards, I would like to point out that the CONCAT is not SQL but local dialect. The correct syntax is in fixed operator, consisting of two pipes which Microsoft does not support. It's the equivalent of the + and Sybase dialect. We took the two pipes from the PL/1 language.
Likewise, CONVERT is a piece of Sybase dialect that was taken from COBOL. It was literally added so that COBOL programmers could do display formatting in the database tier instead of going out to a presentation layer. Like you are supposed to in a tiered architecture. Both of these are pretty much like Chinese or Russian in the middle of an English document.
Heh... you know what I'm going to say, Joe. Use what you have in the dialect you have instead of doing things the hard way. 😉
On that note, do you know if the ISO/ANSI standards support STRING_AGG()? I'm really looking forward to that when we shift our boxes at work to 2017+. That'll be one (like ROW_NUMBER(), etc) that they'll need to pry from my cold dead hands someday in the future. Same goes for the new one in 2019 that also allows you to also identify the delimiter (_ws).
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2021 at 8:02 pm
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 belong in T-SQL.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 2, 2021 at 8:02 pm
STRING_AGG()? I'm really looking forward to that when we shift our boxes at work to 2017+. That'll be one (like ROW_NUMBER(), etc) that they'll need to pry from my cold dead hands someday in the future.
Are we done with
SELECT[MyValueList] = STUFF(
(
SELECTCONCAT(', ', C.StringColToList, C.SecondColumn)
FROMdbo.ChildTable AS C
WHEREC.MyKey = P.MyKey
ORDER BY C.StringColToList
, C.C_ID
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 2, '')
FROMdbo.ParentTable AS P
then? 🙂
Or are there some gotchas? Performance maybe?
March 2, 2021 at 8:07 pm
I think we are, and hooray! for that. From what I've read, at least, the STRING_AGG performs well.
I have only one box on 2017 right now, many on 2016 (and, yeah, still one (SSRS) on SQL 2012; SSRS can be quite a bit of work to migrate).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 2, 2021 at 9:15 pm
The other issue with IIF is that it can be nested only 10 deep. I do have some CASE conditions that have (far) more than 10 WHEN conditions. It would be impossible to write that using IIFs.
In that sense, IIF is "denormalized". You basically have condition1 to condition10. CASE WHEN is more relational, in that you can have as many WHEN conditions as you need. Yes, you can only nest CASEs 10 deep as well, but each CASE can have far more than 10 WHEN conditions.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 31 through 45 (of 61 total)
You must be logged in to reply to this topic. Login to reply