December 28, 2011 at 5:17 am
SQL Kiwi (12/28/2011)
(and no-one reads these comments anyway :-))
Just the important ones. 🙂 Glad you and Hugo stopped by. I made a pretty bad mistake by missing my own NOT NULL constraint on the StartDate. Thank you both for clearing things up.
--Jeff Moden
December 28, 2011 at 5:25 am
Jeff Moden (12/28/2011)
SQL Kiwi (12/28/2011)
(and no-one reads these comments anyway :-))Just the important ones. 🙂 Glad you and Hugo stopped by. I made a pretty bad mistake by missing my own NOT NULL constraint on the StartDate. Thanks you both.
In addition (I only realised this after going to bed yesterday), the second column would never have been used for the seek anyway, even without the ISNULL. Since the first column is on an inequality predicate, the predicate on the second column can only be used as a residual predicate anyway.
(Makes sense, actually - just try to find all people with a surname greater than Moden and a first name smaller than Jeff in the phone book - you'll have to check all the names that follow the last Moden; you can't reduce the amount of pages in the phone book to scan based on the first name).
And please don't be too sorry about your mistake. We all make mistakes. I've stopped couting mine a long time ago. What makes this community so great is that there's always someone who'll point it out to you, so we can learn. As long as we are willing to listen to each other, to take others' comments serious, making mistakes and reading the corrections is just another great way to learn!
December 28, 2011 at 6:06 am
Great Question....
December 28, 2011 at 6:41 am
Jeff Moden (12/28/2011)
My most sincere apologies folks. Hugo and Paul are correct. I missed my own NOT NULL constraint on the StartDate column. I'm sending an email to SSC to modify the answers without changing the ones that got it correct. :blush:To be sure, I made a terrible mistake. [font="Arial Black"]ISNULL isn't SARGable.[/font]
It doesn't, however, change the utility of being able to use an ISULL on the second column of a StartDate/EndDate paired lookup and still get an INDEX SEEK out of the code.
That's what I get for skimming instead of perusing. I thought that's what you meant all along, and thought "IsNulls" (plural) was just a typo.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2011 at 7:27 am
That's what I thought. You explained it perfectly. Thanks Hugo.
Tony
------------------------------------
Are you suggesting coconuts migrate?
December 28, 2011 at 10:25 am
I appreciate the explanations. There is a lot of content worth reading from this thread.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 29, 2011 at 1:30 pm
Yes I agree, an interesting question and great explanations, thanks.
December 31, 2011 at 9:05 am
My word Jeff! A lot of thinking for a single point, considering I got 7 points for counting Santa's reindeer.
Keep 'em coming. I need the mental exercise.
January 1, 2012 at 7:35 am
Tom Brown (12/31/2011)
My word Jeff! A lot of thinking for a single point, considering I got 7 points for counting Santa's reindeer.Keep 'em coming. I need the mental exercise.
I may have missed it but I didn't see a place on the QOTD entry form to assign a point value. If I did, I'd likely have given it 3 points even with the copyable code. 🙂
--Jeff Moden
January 1, 2012 at 8:07 am
Jeff Moden (1/1/2012)
Tom Brown (12/31/2011)
My word Jeff! A lot of thinking for a single point, considering I got 7 points for counting Santa's reindeer.Keep 'em coming. I need the mental exercise.
I may have missed it but I didn't see a place on the QOTD entry form to assign a point value. If I did, I'd likely have given it 3 points even with the copyable code. 🙂
You didn't miss anything, Jeff. Steve decides the amount of points to assign to each question. And he always assigns ridiculously high amounts of points to the seasonal "humor" questions.
January 2, 2012 at 10:50 am
Hugo Kornelis (1/1/2012)
Jeff Moden (1/1/2012)
Tom Brown (12/31/2011)
My word Jeff! A lot of thinking for a single point, considering I got 7 points for counting Santa's reindeer.Keep 'em coming. I need the mental exercise.
I may have missed it but I didn't see a place on the QOTD entry form to assign a point value. If I did, I'd likely have given it 3 points even with the copyable code. 🙂
You didn't miss anything, Jeff. Steve decides the amount of points to assign to each question. And he always assigns ridiculously high amounts of points to the seasonal "humor" questions.
Yes, he's done that again today (for a seasonal simple arithmetic question).
Tom
January 3, 2012 at 3:06 am
Jeff Moden (12/28/2011)
To be sure, I made a terrible mistake. [font="Arial Black"]ISNULL isn't SARGable.[/font]
That is just a perspective thing.
Your 'terrible mistake' challenged something I believed to be true and highlighted a lots of things I didn't know.
Three days into the new year and I now have a much better understanding of how indexes are used.
I think it is going to be a good year.
Many thanks to yourself, Hugo and Paul
January 3, 2012 at 4:43 am
Steve updated the explanation with the lessons learned from this thread for me yesterday.
--Jeff Moden
January 3, 2012 at 4:46 am
440692 I am just a number (1/3/2012)
Jeff Moden (12/28/2011)
To be sure, I made a terrible mistake. [font="Arial Black"]ISNULL isn't SARGable.[/font]That is just a perspective thing.
Your 'terrible mistake' challenged something I believed to be true and highlighted a lots of things I didn't know.
Three days into the new year and I now have a much better understanding of how indexes are used.
I think it is going to be a good year.
Many thanks to yourself, Hugo and Paul
Thank you for the kind words. And, I agree... once again, the discussions are what really paid off on these fine forums.
--Jeff Moden
January 12, 2012 at 4:02 pm
Explanation:
Nothing is stopping an INDEX SEEK from being used and answer "A" is the correct answer.
Actually something does.
It's named "bookmark lookup".
_____________
Code for TallyGenerator
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply