Locking Hints

Question

Post reply

Locking Hints

MyDoggieJessie

SSC-Forever

Points: 44486
More actions
February 20, 2013 at 8:17 pm

#268897

Comments posted to this topic are about the item Locking Hints
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 21, 2013 at 2:37 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 34 total)

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

Easy and Nice question for the day. Thanks Serge 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

demonfox SSCertifiable Points: 6289 More actions · Answer 2

🙂

Lokesh Vij (2/20/2013)
Easy and Nice question for the day. Thanks Serge 🙂

SQL Server assumes that this is a table alias 😀

+1

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 3

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.

Best Regards,

Chris Büttner

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 4

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 ...

--
Dineshbabu
Desire to learn new things..

ldorian81 SSCommitted Points: 1694 More actions · Answer 5

I see what you did there, nice 1.

Good Question!

Regards
ld

Stoke-on-Trent
United Kingdom

If at first you don't succeed, go to the pub and drink away your current thought plan.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 6

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

seriously... i forgot to wear my glasses and for WTH I actually read as WITH and when my choice was marked as wrong I was really like WTH (i mean the.... expression) and then whole thing came to my understanding...

Really interesting question, thank you for posting... more to see like these... so simple... and yet... i scr.... up

(not sure what is with me... I am getting dumber and dumber each day... as I am supposed to be going the other way.. duh!!! ) 😉

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 7

(Bob Brown)

SSCrazy

Points: 2705

February 21, 2013 at 3:58 am

#1589878

Thanks for an excellent question today.

ITGuy268 Mr or Mrs. 500 Points: 550 More actions · Answer 8

Interesting question - luckily I spotted 'WTH' wasn't colour coded as keyword and was mispelt 🙂

MCTS | MCITP | Microsoft SQL Server 2008 Administration & Development
MCSA | MCSE | Business Intelligence SQL Server 2012

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 9

good one !!!

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

TomThomson SSC Guru Points: 104773 More actions · Answer 10

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?

Tom

Dana Medley SSCertifiable Points: 6764 More actions · Answer 11

Good question, you almost got me! 😀

Everything is awesome!

ldorian81 SSCommitted Points: 1694 More actions · Answer 12

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?

Regards
ld

Stoke-on-Trent
United Kingdom

If at first you don't succeed, go to the pub and drink away your current thought plan.

sestell1 SSChampion Points: 10230 More actions · Answer 13

Nice question.

I didn't know the WITH has been optional for hints.