QUERY and TABLE HINTS

  • sfayer

    SSC-Addicted

    Points: 491

    Mighty (4/29/2013)


    Richard Warr (4/29/2013)


    The answer is correct. It's the question that's wrong. I'm sure the question should be "Which DO NOT require WITH" in which case everything falls into place.

    I agree that the "DO NOT" part is missing. However, Hugo showed that even the answer is not correct then.

    +1

  • DugyC

    Hall of Fame

    Points: 3804

    call.copse (4/29/2013)


    I got it right by reading the documentation and assuming the question had been inverted by ascertaining that there were 3 official hints that did not require WITH, so if three answers were required it must surely be them. I agree the question is wrong though.

    If what Hugo says is correct though (and I would not doubt him!) then in fact none of the answers require WITH making it a little null and void anyhow. I'm happy to review hints anyway in case I need to remember what I might need.

    +1, good question though, shame about the mistake.

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    call.copse (4/29/2013)


    I got it right by reading the documentation and assuming the question had been inverted by ascertaining that there were 3 official hints that did not require WITH, so if three answers were required it must surely be them. I agree the question is wrong though.

    If what Hugo says is correct though (and I would not doubt him!) then in fact none of the answers require WITH making it a little null and void anyhow. I'm happy to review hints anyway in case I need to remember what I might need.

    Just to prevent misunderstandings - I did not intent my post to imply that one can leave out the WITH. The current version of SQL Server may be forgiving, but official syntax requires it and omitting it is deprecated. If I had a team of developers and one of them would suggest using hints without WITH, I would have a long and stern conversation with her!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • paul.knibbs

    SSCoach

    Points: 15270

    It's clearly a Monday morning--I guessed that FORCESEEK and FORCESCAN would do something different, since they're new in SQL 2012, but I then ticked those two rather than the other three, thus getting an answer which was 100% wrong in every respect...

  • Rich Weissler

    Hall of Fame

    Points: 3235

    zerko (4/28/2013)


    Tom, yes I noted the deprecation issue - it's not that ...

    Question says "Which of the following hints require the word WITH? (choose 3) "

    Explanation says "table hints ROWLOCK, XLOCK, and NOLOCK can still be used without the "WITH" keyword", ... then ... "Index hints FORCESEEK and FORCESCAN require the "WITH" keyword."

    If a hint can still be used without the WITH keyword, then it is not, by definition, required.

    If the question said "(choose 2)" I would tick FORCESEEK and FORCESCAN, as per above. If the question said "which of the following hints are to be deprecated and will require ... " then the answer would be as given. It didn't. That's my issue here.

    Yep. I tried to quest where the mistake in the question was, and decided the author really intended (choose 2) instead of (choose 3) so risked only checking FORCESEEK and FORCESCAN. So, even though I think it's still the correct answer, and it was marked wrong, at least I learned a little about the deprecation of hints without WITH. 😉

  • Kevin Gill

    SSCrazy

    Points: 2316

    Richard Warr (4/29/2013)


    baabhu (4/29/2013)


    Want my points back. The answer is those hints can be used either with "with" hint or without the with hint. My points back.

    You get a point for comlaining that you didn't get a point. Cheer up 🙂

    Good (point) point 🙂

    -------------------------------
    Oh no!

  • m mcdonald

    Hall of Fame

    Points: 3180

    zerko (4/28/2013)


    the joys of answering early :pinch: -

    The QotD question states "Which of the following hints require the word WITH? (choose 3) "

    I don't use Table Hints that often, neither do I have a SQL Server 2012 installation to test, so I hit BOL and find the page on Table Hints http://msdn.microsoft.com/en-us/library/ms187373.aspx (Yes, same page the solution refers to). I read, and note the section under 'Arguments' that says 'The following table hints are allowed with and without the WITH keyword: ' - that indicates to me that the keyword is not required.

    With 5 options in the answer list, my logic says if 3 require the WITH keyword, then 2 will not. (Old school maths, didn't even use a calculator for that one) So I scan the list ... NOLOCK, there's one, ROWLOCK, that's the second one, others must be out. Tick, tick, tick, submit ... "Sorry - you were wrong." What the ...?!

    So I look at the BOL list again, and yes, there's XLOCK as well. So no matter which 3 I chose, I'd be wrong. There are only 2 that require the WITH keyword. The solution even says so too.

    Any chance this can be fixed before a whole lot more people get miffed about losing points for a flawed (but otherwise interesting) question?

    🙂 I still learnt a lot in the process.

    +1

  • batgirl

    SSCarpal Tunnel

    Points: 4979

    This sentence from http://msdn.microsoft.com/en-us/library/ms181714.aspx is what caused me to answer as I did (which was marked as the correct answer):

    Table hints other than INDEX, FORCESCAN, and FORCESEEK are disallowed as query hints unless the query already has a WITH clause specifying the table hint.

    Accidental for certain.

  • Aleksl-294755

    Hall of Fame

    Points: 3514

    Something is incorrect with the question or answer.

    http://msdn.microsoft.com/en-us/library/ms187373.aspx

    The question is: "Which of the following hints require the word WITH? (choose 3) Assume SQL Server 2012.". -

    The correct answer is: FORCESCAN and FORCESEEK.

    Microsoft: The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints should be specified alone. - http://technet.microsoft.com/en-us/library/ms187373.aspx

  • bekahbeets2003

    SSC Rookie

    Points: 39

    so in the syntax for forcescan and forceseek, it uses the word "with" so that is what I guessed, plus xlock to have three. So I am really confused, because the syntax for xlock, rowlock, and nolock does not have the word "with".

    In the the explanation of how these worked, it said that "with" is deprecated for the three locks, but then it says that forcescan and forceseek require the word "with". Does this mean that all 5 of them require "with"???

    What is up with this question?:pinch:

  • robert.gerald.taylor

    SSChampion

    Points: 13133

    Choose 3 🙁

  • mtassin

    SSC-Insane

    Points: 23099

    Koen Verbeeck (4/29/2013)


    The correct answer is wrong. Zerko is right, the explanation even says so.

    Let the flaming begin 🙂

    Guess I need to light my torch... I'm totally in agreement here. FORCESEEK an FORCESCAN are the only two that require WITH. After that I had to guess because the others are in the list where it is still optional.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Michael_Garrison

    Hall of Fame

    Points: 3110

    zerko

    is right, BAD question. I want my point back.

  • sestell1

    SSChampion

    Points: 10230

    Argh, I figured the answers were accidentally inverted, but went by the wording of the question anyway when I answered.

    Interesting that ForceScan and ForceSeek seem to work without "WITH" though, despite the documentation. Thanks for testing that Hugo!

  • sneumersky

    SSCertifiable

    Points: 7667

    I screwed the pooch on this one by omitting the word "NOT". The purpose of the question was to bring to the developer's attention "the future deprecation of omitting the keyword 'WITH' when usinging hints".

    I hereby put myself on the SQL Server Editor Watch List (if there is one) 🙁

Viewing 15 posts - 16 through 30 (of 44 total)

You must be logged in to reply to this topic. Login to reply