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


Query Tuning


Query Tuning

Author
Message
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25907 Visits: 12494
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

Ian_McCann
Ian_McCann
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2460 Visits: 2332
Jeff Moden (12/28/2011)

To be sure, I made a terrible mistake. ISNULL isn't SARGable.


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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214090 Visits: 41979
Steve updated the explanation with the lessons learned from this thread for me yesterday.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214090 Visits: 41979
440692 I am just a number (1/3/2012)
Jeff Moden (12/28/2011)

To be sure, I made a terrible mistake. ISNULL isn't SARGable.


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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25495 Visits: 12464
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".
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35408 Visits: 11361
Sergiy (1/12/2012)
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".

There is no 'bookmark lookup' in this case as Jeff's explanation of point H reveals: the non-clustered index is covering for the query.

'Bookmark lookup' is out-of-date now: they are referred to as an RID Lookup for a heap, or Key Lookup for a clustered index. The lookup operation is a seek on the clustered index key, or direct navigation to a row locator (RID) if the base table is a heap.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25495 Visits: 12464
SQL Kiwi (1/12/2012)

There is no 'bookmark lookup' in this case as Jeff's explanation of point H reveals: the non-clustered index is covering for the query.

Sounds right.
I need to find out what did I do wrong to make it do scan instead of seek in my test case.
:-)
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62181 Visits: 13297
Great question, thanks.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
seth delconte
seth delconte
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2499 Visits: 1360
Thanks for the question. Even though I got it wrong, I learned a lot. :-)

_________________________________
seth delconte
http://sqlkeys.com
pmadhavapeddi22
pmadhavapeddi22
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2008 Visits: 1870
I thought answer B is including answer A also, that is why i did not check answer A.
and i did a big mistake by not reading "select 2"

learned a lot through the explanation given by all. thanks every one :-)
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