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 «««12345

Query Tuning Expand / Collapse
Author
Message
Posted Monday, January 2, 2012 10:50 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 8,745, Visits: 9,293
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
Post #1228963
Posted Tuesday, January 3, 2012 3:06 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:47 AM
Points: 1,657, Visits: 2,085
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
Post #1229108
Posted Tuesday, January 3, 2012 4:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1229147
Posted Tuesday, January 3, 2012 4:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1229149
Posted Thursday, January 12, 2012 4:02 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:36 AM
Points: 4,576, Visits: 8,348
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".
Post #1235293
Posted Thursday, January 12, 2012 9:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1235368
Posted Sunday, January 15, 2012 4:19 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:36 AM
Points: 4,576, Visits: 8,348
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.
Post #1236354
Posted Tuesday, January 24, 2012 1:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 13,638, Visits: 10,528
Great question, thanks.



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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1240664
Posted Thursday, March 15, 2012 8:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, February 21, 2014 7:54 AM
Points: 1,619, Visits: 1,233
Thanks for the question. Even though I got it wrong, I learned a lot.

_________________________________
seth delconte
http://sqlkeys.com
Post #1267508
Posted Saturday, January 25, 2014 5:48 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 2:43 AM
Points: 795, Visits: 1,381
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



Post #1534717
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse