SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL query


T-SQL query

Author
Message
Q -631159
Q -631159
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 165
aditi.iyer (5/2/2008)
[not like '%e%'] will not work as it will include January and August also Smile


The QOTD desired result set included January and August. The only exclusions were months 2,6, and 9-12. It is Friday. Wink

Q

Please take a number. Now serving emergency 1,203,894
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3547 Visits: 3889
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
WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9743 Visits: 10569
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. Smile

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

engame
engame
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 38
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.
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3547 Visits: 3889
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
Th. Fuchs
Th. Fuchs
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 190
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
Kelsey Thornton
Kelsey Thornton
SSChasing Mays
SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)

Group: General Forum Members
Points: 631 Visits: 282
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. Wink

Kelsey Thornton
MBCS CITP
Sagesh
Sagesh
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 220
"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.
Smile
WarePhreak
WarePhreak
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 50
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. Cool

Michael Sallmen
Michael Sallmen
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 110
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search