Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

T-SQL query Expand / Collapse
Author
Message
Posted Friday, May 2, 2008 12:16 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 26, 2008 7:27 AM
Points: 239, Visits: 165
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

Post #494469
Posted Friday, May 2, 2008 12:20 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Post #494474
Posted Friday, May 2, 2008 7:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
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
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
Post #494599
Posted Friday, May 2, 2008 7:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, March 26, 2011 6:50 PM
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.
Post #494601
Posted Saturday, May 3, 2008 7:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Post #494646
Posted Monday, May 5, 2008 1:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 18, 2014 4:05 AM
Points: 55, Visits: 186
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
Post #494805
Posted Monday, May 5, 2008 4:24 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:16 AM
Points: 534, 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. ;)


Kelsey Thornton
MBCS CITP
Post #494843
Posted Monday, May 5, 2008 5:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:31 AM
Points: 112, Visits: 210
"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.
:)
Post #494852
Posted Monday, May 5, 2008 7:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 13, 2012 3:28 PM
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.
Post #494924
Posted Monday, May 5, 2008 9:10 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 3:38 PM
Points: 13, 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!
Post #495049
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse