February 21, 2013 at 8:07 am
Ha ha ha ha. I didn't even see that WITH was misspelled. :crazy:
Got it right anyway...
February 21, 2013 at 9:28 am
L' Eomot Inversé (2/21/2013)
Good question. ... After all, it had already been deprecated in SQL 2005, SQL 2008, and SQL 2008 R2, and surely 3 releases is enough warning for something like this?
The old saying is "Going, going, going, gone!" If Microsoft follows this rule then we are on the last going and it will be gone next release. 🙂
Agree that it is a good question and got it.
Not all gray hairs are Dinosaurs!
February 21, 2013 at 9:51 am
Christian Buettner-167247 (2/21/2013)
Nice question, but the explanation lacks important information. Let me quote it for you here:Table Hints (Transact-SQL) - SQL Server 2008 R2
WITH ( <table_hint> ) [ [ , ]...n ]With some exceptions, table hints are supported only when the hints are specified with the WITH keyword. Parentheses are required.
Important note
Important
Omitting the WITH keyword is a deprecated feature and will be removed in a future version of Microsoft SQL Server. Always specify WITH in new development work, and modify applications that currently omit this keyword.
Separating hints by spaces instead of commas is a deprecated feature and will be removed in a future version of Microsoft SQL Server. Always specify commas in new development work, and modify applications that currently omit commas.
The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, 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. For example, FROM t WITH (TABLOCK). When the hint is specified with another option, the hint must be specified with the WITH keyword. For example, FROM t WITH (TABLOCK, INDEX(myindex)).
The restrictions apply when the hints are used in queries against databases with the compatibility level of 90 and higher.
http://msdn.microsoft.com/en-us/library/ms187373%28v=sql.105%29.aspx
If you are working with a different SQL Server version, be sure to read the corresponding documentation version as well.
Yes, looks like I had posted the improper link but in the second paragraph the link to your quoted suggestion exists (For more information about the specific locking hints and their behaviors, see Table Hint (Transact-SQL)...which links directly to your article, albeit SQL 2005) - thanks for pointing that out! I'll be sure to triple-check next time :hehe:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 21, 2013 at 11:28 am
hey hey I got it right but have to admitt I overlooked the mispelled with and went ahead.. thanks for the qotd
___________________________________________________________________
If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:
February 21, 2013 at 11:33 am
Of course if your server has a case sensitive collation, you'll get an error because #Test <> #TEST.
February 21, 2013 at 12:25 pm
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 21, 2013 at 8:29 pm
I totally overlooked the misspelled WITH too. I never knew you could use a hint without using WITH. Interesting question. Thanks Serge!
February 22, 2013 at 1:36 am
interestnig question. hadn't spotted the mi-spelling at first.
February 22, 2013 at 1:49 am
If the SQL instance is case sensitive the query fails due to the different case of #Test in the CREATE statement compared with the INSERT and SELECT statements.
February 22, 2013 at 7:39 am
ldorian81 (2/21/2013)
L' Eomot Inversé (2/21/2013)
Good question.I'm glad that missing out WITH is deprecated though, so that teh answer to this question will change in the future. In fact I think it's a great pity that it hasn't already disappeared in SQL 2012 (cf http://msdn.microsoft.com/en-us/library/ms187373.aspx. After all, it had already been deprecated in SQL 2005, SQL 2008, and SQL 2008 R2, and surely 3 releases is enough warning for something like this?
Obviously I don't have 2012 but for curiosity purposes, in 2008 you can use a tablehint without using WITH keyword on any 1 hint but for 2 hints you have to specify it. e.g. WITH(noexpand, nolock)
In 2012 is that now removed?
In SQL Server 2012, you can still specify ONE of the following table hints without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. If you use more than one table hint, you must use the WITH keyword.
I verified this on my SQL Server 2012 instance - if you use more than one table hint without the WITH keyword, SQL Server seems to think you're either specifying column aliases or trying to call a function with parameters.
Doing so generates this error:
Msg 207, Level 16, State 1, Line 9
Invalid column name 'NOLOCK'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'FORCESCAN'.
Msg 215, Level 16, State 1, Line 9
Parameters supplied for object '#TEST' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.
Source: Books Online.
Jason Wolfkill
February 22, 2013 at 9:13 am
Dineshbabu (2/21/2013)
Nice and easy one. Initially I overlooked the word WTH and answered the question. After seeing the explaination only i realised it was mispelled and it will act as alias name.Thanks for your brief explaination Chris Büttner ...
+1 same here... didn't even notice the WTH I think it's because I'm getting too used to reading horrible forums posts out there and just auto-correct in my reading of them. 🙂
February 25, 2013 at 12:44 am
WOW... good question. There is a say in Indian language "Savdhani hati, durghatna ghati" <not careful, accident happens>
Didn't thought of the alias thing, jumped to the answer and lost the point. 🙂
February 25, 2013 at 10:14 am
EZ PZ
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
February 25, 2013 at 10:54 am
Glad the question was well received 🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 4, 2013 at 4:49 pm
Agreed - good question. However the stmt in the explanation is invalid. The alias, 'WTH' was not used in the FROM clause so the SELECT wth.* has no alias to match to. It happens.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply