QUERY and TABLE HINTS

Question

QUERY and TABLE HINTS

Viewing 14 posts - 31 through 43 (of 43 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

hahahahahaha

Well, this one was a bit backwards.

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

sneumersky SSCertifiable Points: 7667 More actions · Answer 2

First off, please accept my apologies to everyone for bringing your brains to a screeching halt.

In breaking the QDC (Question Of The Day Cherry), I now feel like Henry Hill getting pinched the first time in GoodFellas. Not much harm was done to the family, but I (re)learned two good lessons:

1. Haste makes waste.

2. Trust Microsoft documentation, but verify it as well.

Enjoy the rest of your day 🙂

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 3

Don't feel too bad about it, Steven. It happens to all of us.

It was a great effort, and I hope you'll submit more questions in the future.

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/

jlennartz SSCommitted Points: 1574 More actions · Answer 4

Nice question to bad the answer was phrased incorrectly.

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 5

sneumersky (4/29/2013)
... Not much harm was done to the family, but I (re)learned two good lessons:
1. Haste makes waste.
2. Trust Microsoft documentation, but verify it as well.
Enjoy the rest of your day 🙂

Good lessons even when NOT posting QOTD's.

Thanks for the question -- we all learned something.

Rob Schripsema
Propack, Inc.

TomThomson SSC Guru Points: 104773 More actions · Answer 6

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.
While I agree a little knowledge can be dangerous, a little confusion can be far worse.

I think I had my brain switched off at half past four yesterday morning. Must remember not to come back from bar and tackle next days QotD - or at least, not comment on it!

Yes, you were completely right. All five hints listed were table hints, force seek and force scan included. None were join hints. The question asks for exactly the opposite of what the "correct" answer states. Even the explanation contradicts the answer.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 7

Hugo Kornelis (4/29/2013)
Too bad the question author somewhere switched the two options in his head and marked the wrong answers as right and the right answers as wrong. Fortunately, I did the very same when replying, so I got a point I didn't really deserve. (Anyone who feels cheated out of a point - you can have mine! It's not like I can buy anything from those points anyway).

You can have mine too. At times like this I wish Steve fixed things to subtract points as well as add them when correcting things.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Hugo Kornelis (4/29/2013)
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!

That's a bit like me - but I've been intending to have that long and stern conversation with myself, not with anyone wlse. I still find myself writing "(tablockx)" without any "with" now and again (I've begun to use ";" now and again, despite regarding its inclusion in SQL as pointless and irritating, a beautiful example of incompetent language design leading to incompetent parser design requiring nonsense; so perhaps I ought to be able to start using "with" for table hints, which is certainly no more offensive than those ";"s).

And I'm surprised to see that "with" isn't required where BOL says it is; maybe they should deprecate that, too?

Tom

vinu512 SSCoach Points: 15729 More actions · Answer 9

I thought I had nailed this one.....but then...:crying::alien:

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

arturv Grasshopper Points: 21 More actions · Answer 10

Hi,

With SQL 2012 (11.3.3128), you only need to declare "WITH" if you are using FORCESEEK.

Artur

KarenR Old Hand Points: 395 More actions · Answer 11

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.

ditto!

Ol'SureHand SSCrazy Points: 2744 More actions · Answer 12

Hugo Kornelis (4/29/2013)
Too bad the question author somewhere switched the two options in his head and marked the wrong answers as right and the right answers as wrong. Fortunately, I did the very same when replying, so I got a point I didn't really deserve. (Anyone who feels cheated out of a point - you can have mine! It's not like I can buy anything from those points anyway).
However, here's a far more interesting observation. I wanted to see what error message I get when omitting the word WITH for a hint that does require it - and then I found out that the documentation is incorrect. My tests indicate that, in spite of what Books Online says, FORCESCAN and FORCESEEK can be used without WITH!
CREATE TABLE abc(abx int)
GO
SELECT *
FROM dbo.abc (FORCESCAN) AS a
INNER JOIN dbo.abc AS b
ON b.abx = a.abx;
GO
SELECT *
FROM dbo.abc (FORCESEEK) AS a
INNER JOIN dbo.abc AS b
ON b.abx = a.abx;
GO
DROP TABLE abc;
go
The first query executes just fine. The second query does produce an error, but not because of the missing WITH - the error says that it's impossible to create a plan that sattisfies the hints. (Which makes sense - with no index on the table, using a seek is absolutely impossible).
(In case you're wondering why I didn't simply use a SELECT * FROM table (hint) - I tried that first, then tried if I would force a parsing error by increasing the complexity of the query).
Has anyone been able to actually get SQL Server to produce an error by using these hints without WITH?

No, they seem to be accepted still. Not a reason to "save" typing the extra 4 characters in WITH.

However, the hint (INDEX=[tblIdxName]) is truly deprecated - since 2008 in fact.

However ... even in 2012 ... if you set your compatilbility back to .. say SQL2000, even this deprecated hint will work.

We all keep learning ... and learning ...

Greetings

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Ol'SureHand (5/2/2013)
No, they seem to be accepted still. Not a reason to "save" typing the extra 4 characters in WITH.

<PEDANTRY>It's 5 extra characters, not 4. Typing WITHFORCESCAN will get you an error message </PEDANTRY>. :hehe:

Tom

Ol'SureHand SSCrazy Points: 2744 More actions · Answer 14

L' Eomot Inversé (5/2/2013)
Ol'SureHand (5/2/2013)
No, they seem to be accepted still. Not a reason to "save" typing the extra 4 characters in WITH.
<PEDANTRY>It's 5 extra characters, not 4. Typing WITHFORCESCAN will get you an error message </PEDANTRY>. :hehe:

Thanks, Tom

I knew as I typed that que someone was going to pick on the number of keystrokes !!

C'est la vie!