July 18, 2016 at 1:10 pm
I would say that it had no real-world application, mostly since it prevents you from using any indexing on the OrderDt field. I guess if you were dead-set on using it, it really needs to also be in the SELECT clause, and so that gives you some consistency in your predicates and the data you're showing to your user:
SELECT CASE YEAR(OrderDT)
WHEN 2014 THEN 'Year 1'
WHEN 2013 THEN 'Year 2'
WHEN 2012 THEN 'Year 3'
ELSE 'Year 4 and beyond' END as myField
FROM MyOrder
WHERE CASE YEAR(OrderDT)
WHEN 2014 THEN 'Year 1'
WHEN 2013 THEN 'Year 2'
WHEN 2012 THEN 'Year 3'
ELSE 'Year 4 and beyond' END = 'Year 1';
or better yet
SELECT *
FROM (SELECT CASE YEAR(OrderDT)
WHEN 2014 THEN 'Year 1'
WHEN 2013 THEN 'Year 2'
WHEN 2012 THEN 'Year 3'
ELSE 'Year 4 and beyond' END as myField
FROM MyOrder) inline_view
WHERE my_field = 'Year 1'
The second example is better, so that you are assured that the CASE statement in the SELECT and the WHERE are identical. The first example runs the risk of someone having different CASE clauses.
--=cf
July 18, 2016 at 1:29 pm
Thanks Chuck for clarifying!
I agree with your statement:
it really needs to also be in the SELECT clause, and so that gives you some consistency in your predicates and the data you're showing to your user
I was told that the second query would perform better than the first query when tuning. Is that true?
-----------------------------------------------------------------------
Known is a DROP, Unknown is an OCEAN.:ermm:
July 18, 2016 at 1:38 pm
I don't think either would be a better performer. The better performer would be
SELECT CASE YEAR(OrderDT)
WHEN 2014 THEN 'Year 1'
WHEN 2013 THEN 'Year 2'
WHEN 2012 THEN 'Year 3'
ELSE 'Year 4 and beyond' END as myField
FROM MyOrder
WHERE OrderDT between DATEFROMPARTS(2014, 01, 01) and DATEFROMPARTS(2014, 12, 31)
This is assuming your OrderDT is declared as a DATE, and not DATETIME2. If OrderDT is DATETIME2, then you'd need to take seconds into consideration for the predicate (there's also a DATETIMEFROMPARTS function in SQL 2012+):
WHERE OrderDT between DATEFROMPARTS(2014, 01, 01) and DATETIMEFROMPARTS(2014, 12, 31, 23, 59, 59, 998)
--=Chuck
July 18, 2016 at 1:44 pm
Maybe this would be better, in the case of DATETIME2, one of the more experienced SQL Server developers could let us know:
WHERE OrderDT > DATEFROMPARTS(2014, 01, 01) and
OrderDT < DATEFROMPARTS(2015, 01, 01)
I like the specificity of asking for everything up until that last millisecond, but I noticed that if you put in 999 for the milliseconds, SQL Server rounds up, so that closest you can get is 998:
select DATETIMEFROMPARTS(2014, 12, 31, 23, 59, 59, 998) as almost,
DATETIMEFROMPARTS(2014, 12, 31, 23, 59, 59, 999) as over_rounded
almost over_rounded
2014-12-31 23:59:59.9972015-01-01 00:00:00.000
July 18, 2016 at 1:45 pm
Doesn't it matter if the OrderDT is indexed or not when used in filter?
-----------------------------------------------------------------------
Known is a DROP, Unknown is an OCEAN.:ermm:
July 18, 2016 at 1:50 pm
Even if it's not indexed, with a CASE statement, you're still asking SQL Server to convert every DATE value to a VARCHAR before it can compare it to your string literal. That's an extra step. For small tables, not a big deal. I don't honestly know where the breaking point is in a larger table, but it makes sense to me that there's a penalty somewhere for converting datatypes, when you don't have to.
--=cf
July 18, 2016 at 2:04 pm
OK, thank you!
-----------------------------------------------------------------------
Known is a DROP, Unknown is an OCEAN.:ermm:
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply