Different nolock question

Question

Post reply

Different nolock question

texpic

SSCertifiable

Points: 5885
More actions
March 28, 2013 at 2:12 pm

#291601

Well I think it is a different question because I haven't seen it asked.

If I do not have a join, I can do a query like this:

SELECT * FROM TableABC nolock

If the query is slow and I change query to it runs faster:

SELECT * FROM TableABC (nolock)

If I join I have to use the parenthesis.

QUESTION:

Is ther a difference with:

nolock,

(nolock) and

with (nolock) ?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

texpic (3/28/2013)

Well I think it is a different question because I haven't seen it asked.

If I do not have a join, I can do a query like this:

SELECT * FROM TableABC nolock

If the query is slow and I change query to it runs faster:

SELECT * FROM TableABC (nolock)

If I join I have to use the parenthesis.

QUESTION:

Is ther a difference with:

nolock,

(nolock) and

with (nolock) ?

There is no difference. All three of those are using what is probably the absolute worst possible query hint in sql server. If you are adding that to improve performance you don't understand what that query can do for. It is NOT a magic make sql go fast solution. It can return duplicates, missing or even deleted data.

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]

http://blogs.msdn.com/b/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Loundy SSCertifiable Points: 6080 More actions · Answer 2

select * from table nolock

This will simply alias the table as nolock so when you join to another table, in the ON predicate you can refer to the table as nolock instead of the table name - it would be bad form to alias an object as a reserved keyword mind...

select * from table (nolock)

or

select * from table WITH (nolock)

this is the same thing and is a query hint. I think I read somewhere that in future version of sql the WITH keyword will be compulsory - not sure what truth there is in this mind.

[font="Times New Roman"]There's no kill switch on awesome![/font]

Lowell SSC Guru Points: 323482 More actions · Answer 3

SELECT * FROM TableABC nolock

is exactly the same as

SELECT * FROM TableABC bananas

In the above, the word after the table is just an alias.

SELECT * FROM TableABC (nolock)

This is the old style way, to put the hint in parenthesis. it is deprecated, and the WITH (nolock) is required in a future version 2012 , and will not work without the WITH on anything that is not a SELECT, i believe...(so UPDATE ...WITH (TABLOCKX) is good, but you get a syntax error if you leave out the WITH.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

texpic SSCertifiable Points: 5885 More actions · Answer 4

texpic

SSCertifiable

Points: 5885

March 28, 2013 at 4:21 pm

#1601906

bananas, yep makes sense. I didn't even think it was simply an alias.