May 2, 2008 at 12:16 pm
aditi.iyer (5/2/2008)
[not like '%e%'] will not work as it will include January and August also 🙂
The QOTD desired result set included January and August. The only exclusions were months 2,6, and 9-12. It is Friday. 😉
Q
Please take a number. Now serving emergency 1,203,894
May 2, 2008 at 12:20 pm
saimsaboor (5/2/2008)
In the "where" Clause the "datename" function is Nondeterministic function also when we use "like" operator then query can not use indexes on that key resulted in table scan rather than index seek.I will prefer the Case Statement
i.e.
where
case month(dob)
when 1 then 1
when 3 then 1
when 4 then 1
when 5 then 1
when 7 then 1
when 8 then 1
else 0 end = 1
In the above statement month is used which is deterministic function and this query will take part in index seek.
Saim
Hello Saim,
Why do you think that this would result in an index seek?
I am pretty sure that this will result in an index scan just like the "like" method.
You need to create a persisted computed column or an indexed view to allow an index seek. Otherwise, SQL Server first has to calculate the month for all dob's which results in the mentioned scan.
Best Regards,
Chris Büttner
May 2, 2008 at 7:14 pm
webrunner (5/2/2008)
I agree. But I think the English phrasing should be: "The condition should not use either the IN or the OR operators." Or, alternatively, "The condition should use neither the IN nor the OR operators."
I stand corrected. I meant neither, but either choice you gave works. 🙂
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 2, 2008 at 7:46 pm
To my knowledge the provided answer will always result in an index scan, so in a real environment will be a poor choice. I will say that the best way to do this filter is with a join to a either a temp table or to an inline query.
May 3, 2008 at 7:36 am
Christian Buettner (5/2/2008)
saimsaboor (5/2/2008)
In the "where" Clause the "datename" function is Nondeterministic function also when we use "like" operator then query can not use indexes on that key resulted in table scan rather than index seek.I will prefer the Case Statement
i.e.
where
case month(dob)
when 1 then 1
when 3 then 1
when 4 then 1
when 5 then 1
when 7 then 1
when 8 then 1
else 0 end = 1
In the above statement month is used which is deterministic function and this query will take part in index seek.
Saim
Hello Saim,
Why do you think that this would result in an index seek?
I am pretty sure that this will result in an index scan just like the "like" method.
You need to create a persisted computed column or an indexed view to allow an index seek. Otherwise, SQL Server first has to calculate the month for all dob's which results in the mentioned scan.
Hm, was kind of late yesterday - the average number of dobs for these months is probably around 50%. So SQL Server will in any case do an index scan, even if you have persisted the col or created an indexed view.
Best Regards,
Chris Büttner
May 5, 2008 at 1:29 am
you can use char/patindex as IN operator:
where birthday is not NULL and charindex(cast(datepart(month, birthday) as varchar(8)) + ',', '1,3,4,5,7,8,') > 0
May 5, 2008 at 4:24 am
Once again, the given "answer" does not always work if youre not running in English Language...
For example, in Dutch, month 5 (mei) would NOT be included and month 6 (juni) WOULD...
I think if things are language dependent this should be specified in the question. 😉
Kelsey Thornton
MBCS CITP
May 5, 2008 at 5:07 am
"I'd have to disagree. I think these ones about working out pointless conditions to satisfy one off special cases that nobody will EVER use, are more about your ability (and level of interest) in working out what's different about those months than the others. As such, they have almost nothing to to with T-SQL (which was the 'Question Type') other than that they chose to write the solution in it. I imagine many people will simply guess yes (there's usually a way to do anything that starts with 'is there a way...') without really knowing why. I decided to be contrary and guessed no, based on the last (equally pointless) one of these being 'yes'.
-- Kev "
I am agree with your suggestions.
🙂
May 5, 2008 at 7:11 am
My first problem is the question is too broad and I chose Yes without thinking about it as there is almost always several ways to do the same thing. The second problem is that the solution given is locale specific as has been pointed out in other questions.
I don't mind this type of question if it truly shakes me up like Paul said but I think it needs to lead me into thinking about a particular way to solve it that I might not normally consider.
Kevin Gill (5/2/2008)
... I think these ones about working out pointless conditions to satisfy one off special cases that nobody will EVER use, are more about your ability (and level of interest) in working out what's different about those months than the others. As such, they have almost nothing to to with T-SQL (which was the 'Question Type') other than that they chose to write the solution in it. I imagine many people will simply guess yes (there's usually a way to do anything that starts with 'is there a way...') without really knowing why. ...
Anirban Paul (5/2/2008)
Kevin all solutions may not be the best but sometimes these type of solutions/questions shake our brains. We sometimes do monotonous work, so this solutions help us to break the shackle. We learnt something differently is also interesting. 😎
May 5, 2008 at 9:10 am
It is good to bend our minds around a concept in a new way, but that is the only redeeming value for this question. It would be a terrible implementation in real life.
What nobody has mentioned yet is the sheer horror of readability and maintainability. There is no way to read the answer syntax and immediately know what it does. Even after studying it for a while, more than one person thought it did something other than what it does. (See the foggy Friday posts above!) Honestly, the task is so simple that any SQL programmer ought to be able the read and write the required syntax while half asleep!
What happens when the requirements for this statement change? With the answer given, it's not possible to add or remove a case statement or digit in a list of numbers. The whole premise needs to be thrown out unless the new requirements include only months that don't have some other letter in them!
It's a clever little question for a Friday, but just pray that you don't inherit code written like this!
May 5, 2008 at 2:12 pm
Maybe that's why we keep getting these QoDs - the poster inherited a system using this awful code. 😉
That being said, this is the second of these strange "out of the box" type questions. It was pretty poor last time and equally poor this time. The solution given may be technically correct, but it doesn't show better ways of doing it and breaks once you switch from English to just about any other language.
I'd love to see just a little more screening for better QoDs rather than get another one of these. It's not just that it's not really challenging to figure "yes, there must be another way", but the answer is lacking. There are multiple solutions and I probably wouldn't mind if more of those were listed, but just to leave the language-specific solution in the answer alone makes it a pretty poor question/answer.
May 6, 2008 at 2:09 am
the answer is not an multilingual answer ! i prefer to use the month number!
May 6, 2008 at 3:01 am
Stupid question, pointless answer, 'nuff said.
May 6, 2008 at 5:56 am
Although I realised that this was another silly question which was going to have an answer which was language specific, inflexible and basically stupid, my immediate thought was to use Boolean algebra. 🙂
Since 'A OR B' can alwasy be rewritter as 'NOT (NOT A AND NOT B)' then the (still silly) solution of
where
mob = 1
or mob = 3
or mob = 4
or mob = 5
or mob = 7
or mob = 8
can be rewritten aswhere not (
mob <> 1
and mob <> 3
and mob <> 4
and mob <> 5
and mob <> 7
and mob <> 8
)
Which satisfies the (poorly specified) requirement of using neither IN nor OR. 😀
Derek
May 6, 2008 at 9:11 am
mike brockington (5/6/2008)
Stupid question, pointless answer, 'nuff said.
I totally agree - English question - English Answer
Does it work in Swahili, Spanish, French and German? Can I spell Swahili?
😀 'If you database was not adequately designed - accept your applications will be crap!' 😀
--Shaun Tzu's Art of DBAdmin and Design
Hiding under a desk from SSIS Implemenation Work :crazy:
Viewing 15 posts - 31 through 45 (of 51 total)
You must be logged in to reply to this topic. Login to reply