August 18, 2021 at 12:54 pm
ScottPletcher wrote:I don't see the need for all that, this is much simpler:
SET @ytdfrDate = DATEFROMPARTS(YEAR(@frdate) -
CASE WHEN MONTH(@frdate) < 4 THEN 1 ELSE 0 END, 04, 01)Sweet. I first tested it for DATETIME values from 1900 through 2099 and got the correct results. Then I tested it on a million rows and it's 20ms faster than mine (seems trivial to most but that's a win, IMHO). As you say, it's also simpler to understand and, if we replace the CASE with IIF (which resolves to a CASE behind the scenes so no perf change when I measured it), it's really short.
SELECT FiscalYearStartDT = DATEFROMPARTS(YEAR(@frdate)-IIF(MONTH(@frdate)<4,1,0),4,1);Nicely done, Scott.
Thank you! Although I despise IIF being used in SQL, since it's not a part of SQL, so I will stick with CASE. Btw, SQL translates the IIF into a CASE ... because IIF is not really a part of SQL. It's a familiar crutch for developers used to something other than 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".
August 18, 2021 at 1:11 pm
Heh... Yeah... understood there. I have the same feeling about FORMAT except that FORMAT is actually a performance issue on top of everything else.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2021 at 2:45 pm
CLR functions for performing Regular Expression searches aren't SQL either, but they're useful. Where do you draw the line?
IIF is part of T-SQL and is more succinct than CASE, and that's good enough for me, as I'd rather not read unnecessary verbiage in code.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 18, 2021 at 3:07 pm
CLR functions for performing Regular Expression searches aren't SQL either, but they're useful. Where do you draw the line?
IIF is part of T-SQL and is more succinct than CASE, and that's good enough for me, as I'd rather not read unnecessary verbiage in code.
CLR functions to support regular expressions add functionality. For that matter, FORMAT adds functionality.
IIF adds no functionality at all: standard CASE expressions can do the same and more. It was added solely as a crutch, and that is where I draw the line. It's syntax is not typical SQL style, it looks out of place. I'm worried about T-SQL ending up like PL/SQL, which is just a horrid mish-mash of all kinds of syntaxes slammed together.
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".
August 18, 2021 at 3:24 pm
Phil Parkin wrote:CLR functions for performing Regular Expression searches aren't SQL either, but they're useful. Where do you draw the line?
IIF is part of T-SQL and is more succinct than CASE, and that's good enough for me, as I'd rather not read unnecessary verbiage in code.
CLR functions to support regular expressions add functionality. For that matter, FORMAT adds functionality.
IIF adds no functionality at all: standard CASE expressions can do the same and more. It was added solely as a crutch, and that is where I draw the line. It's syntax is not typical SQL style, it looks out of place. I'm worried about T-SQL ending up like PL/SQL, which is just a horrid mish-mash of all kinds of syntaxes slammed together.
That is your choice - but since it is available and does lead to more concise code it is something that is useful. There are other functions available that are really just shortcuts to CASE expressions - so I assume those are not useful either.
I assume you replace any functions where CASE can be used - or is it only IIF that you don't use?
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
August 18, 2021 at 3:30 pm
I assume you replace any functions where CASE can be used - or is it only IIF that you don't use?
Like a COALESCE with 10 arguments, for example. Why have one row of code when you can have 20, by being consistent and sticking with CASE?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 18, 2021 at 3:36 pm
ScottPletcher wrote:Phil Parkin wrote:CLR functions for performing Regular Expression searches aren't SQL either, but they're useful. Where do you draw the line?
IIF is part of T-SQL and is more succinct than CASE, and that's good enough for me, as I'd rather not read unnecessary verbiage in code.
CLR functions to support regular expressions add functionality. For that matter, FORMAT adds functionality.
IIF adds no functionality at all: standard CASE expressions can do the same and more. It was added solely as a crutch, and that is where I draw the line. It's syntax is not typical SQL style, it looks out of place. I'm worried about T-SQL ending up like PL/SQL, which is just a horrid mish-mash of all kinds of syntaxes slammed together.
That is your choice - but since it is available and does lead to more concise code it is something that is useful. There are other functions available that are really just shortcuts to CASE expressions - so I assume those are not useful either.
I assume you replace any functions where CASE can be used - or is it only IIF that you don't use?
Only IIF. ISNULL() and COALESCE() are native to SQL and are thus already known to SQL developers.
IIF's not that much more concise anyway, and it's much less clear to other SQL developers, i.e., those people not already familiar with it. So, yeah, I don't like to needlessly add obfuscation to code for no real gain.
But the bigger, and core, problem is that IIF has a falsehood built into it. The docs say "(condition, true_value, false_value)", but what if the result is neither true nor false? Viz:
DECLARE @var1 int
SELECT IIF(@var1 > 5, 'True', 'False')
You get the 'False' result, but it's not False, it's NULL (unknown). CASE has ELSE, which is accurate: the result was something other than the CASE condition being True.
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".
August 18, 2021 at 3:38 pm
Jeffrey Williams wrote:I assume you replace any functions where CASE can be used - or is it only IIF that you don't use?
Like a COALESCE with 10 arguments, for example. Why have one row of code when you can have 20, by being consistent and sticking with CASE?
COALESCE() is a natural part of SQL and always has been. IIF is a kludgy add-on as a crutch for Windows developers. No comparison. See also my immediately previous comments.
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".
August 18, 2021 at 3:55 pm
The rule for SQL expressions and functions is that if a NULL value goes in, a NULL value comes back, unless you explicitly do something to change that.
IIF violates, in fact reverses, that rule! There is no way to get NULL back from an IIF without explicitly doing something to get it back, the exact opposite of every other function in SQL. So, again, hell no, I'm not eager to use IIF in any T-SQL ever, at any time.
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".
August 18, 2021 at 3:56 pm
Phil Parkin wrote:Jeffrey Williams wrote:I assume you replace any functions where CASE can be used - or is it only IIF that you don't use?
Like a COALESCE with 10 arguments, for example. Why have one row of code when you can have 20, by being consistent and sticking with CASE?
COALESCE() is a natural part of SQL and always has been. IIF is a kludgy add-on as a crutch for Windows developers. No comparison. See also my immediately previous comments.
Oh - I get your objection to it. I just don't think it is a valid argument for not using it. Your example of IIF built-in falsehood is also incorrect:
DECLARE @var1 int;
SELECT CASE WHEN @var1 > 5 THEN 'true' ELSE 'false' END;
If the answer for IIF should be unknown then the answer to the above should also be unknown - and yet, it also returns 'false'. In fact - both statements will generate the exact same code.
BTW - there are a lot of SQL developers who have absolutely no idea that COALESCE even exists. They rely solely on ISNULL and are quite surprised when presented with COALESCE.
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
August 18, 2021 at 3:58 pm
The rule for SQL expressions and functions is that if a NULL value goes in, a NULL value comes back, unless you explicitly do something to change that.
IIF violates, in fact reverses, that rule! There is no way to get NULL back from an IIF without explicitly doing something to get it back, the exact opposite of every other function in SQL. So, again, hell no, I'm not eager to use IIF in any T-SQL ever, at any time.
See my other post - since IIF is just a shortcut to a case expression, what you are saying here is that it should NOT work the same as the corresponding CASE expression and instead return different results.
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
August 18, 2021 at 4:05 pm
ScottPletcher wrote:Phil Parkin wrote:Jeffrey Williams wrote:I assume you replace any functions where CASE can be used - or is it only IIF that you don't use?
Like a COALESCE with 10 arguments, for example. Why have one row of code when you can have 20, by being consistent and sticking with CASE?
COALESCE() is a natural part of SQL and always has been. IIF is a kludgy add-on as a crutch for Windows developers. No comparison. See also my immediately previous comments.
Oh - I get your objection to it. I just don't think it is a valid argument for not using it. Your example of IIF built-in falsehood is also incorrect:
DECLARE @var1 int;
SELECT CASE WHEN @var1 > 5 THEN 'true' ELSE 'false' END;
If the answer for IIF should be unknown then the answer to the above should also be unknown - and yet, it also returns 'false'. In fact - both statements will generate the exact same code.
BTW - there are a lot of SQL developers who have absolutely no idea that COALESCE even exists. They rely solely on ISNULL and are quite surprised when presented with COALESCE.
It's still a falsehood. The docs say it is the "false_value", but the result is not "false". ELSE is accurate, because that simply means the WHEN condition was not proven true. If you need to distinguish NULL, you need to do it yourself, which is more easily recognized with a CASE expression.
The idea of NULL is a critical concept for SQL developers, to understand (not sure about Windows developers). The WHERE clause must be true for the row to be SELECTed. Not even just "not false" but affirmatively (proven) true.
As to COALESCE(), SQL developers should know about it. If they don't, they sure wouldn't know about IIF. I don't expect my SQL developers to know about IIF, but I absolutely expect them to know about CASE.
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".
August 18, 2021 at 4:32 pm
It's still a falsehood. The docs say it is the "false_value", but the result is not "false". ELSE is accurate, because that simply means the WHEN condition was not proven true. If you need to distinguish NULL, you need to do it yourself, which is more easily recognized with a CASE expression.
Again - I understand your objection. The problem is that I disagree with your assertion that all functions should return unknown when a NULL is encountered. A function returns a value based on how that function is written - and since IIF was written as a shortcut to a case expression in the form of 'CASE WHEN {condition} THEN {true value} ELSE {false value} END' it is performing exactly how it was intended to perform.
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
August 18, 2021 at 5:17 pm
ScottPletcher wrote:It's still a falsehood. The docs say it is the "false_value", but the result is not "false". ELSE is accurate, because that simply means the WHEN condition was not proven true. If you need to distinguish NULL, you need to do it yourself, which is more easily recognized with a CASE expression.
Again - I understand your objection. The problem is that I disagree with your assertion that all functions should return unknown when a NULL is encountered. A function returns a value based on how that function is written - and since IIF was written as a shortcut to a case expression in the form of 'CASE WHEN {condition} THEN {true value} ELSE {false value} END' it is performing exactly how it was intended to perform.
No, it wasn't. It was written to copy other languages' syntax, where it's a true_value and false_value. It does not even allow for an ELSE value.
The SQL CASE docs do not say "false" value, they say "else expression". There is a difference. If you can't see the difference between them, then you don't see it, but it is there. And it does make the IIF expression a bit of a bogus construct when used in SQL Server vs in the languages it was designed for.
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 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply