views:

84

answers:

3

I have only SQL Server 2008R2 installed though I need to communicate with customers having 2005.

[1] tells:

  • "NOLOCK

    This does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements"

[2] doesn't seem to mention it, but my checking in SSMS/SS 2008R2 shows that nolock is not default hint for SELECT.

Is WITH(NOLOCK) really default in SQL Server 2005 SELECT?
Where is it written in BOL2005/2008?


Update:
Under "Where is it written" I expected to see answers/comments/clarifications (or, better, citations) on all cited from [1] parts:

  • "This does not lock any object"

    Does SELECT without NOLOCK put any any locks in SQL Server 2005 (having default isolation level READ UNCOMMITTED)?
    ... in SQL Server 2008 (resp. having READ COMMITTED)?

    What I have read on it can be understood that NOLOCK permits to ignore/bypass existing locks put by another transaction... but it was quite unclear whether current transaction (SELECT with NOLOCK) puts (or trying to put) its own locks...

    Does READ UNCOMMITTED isolation level (which is used as synonym to using NOLOCK hint) imply absence of any locking?

  • "It does not apply to INSERT, UPDATE, and DELETE statements"
    It seems to be correct?
    Is it because they (INSERT, UPDATE, and DELETE) always lock but SELECT doesn't?
    etc.

[1]
http://blog.sqlauthority.com/2007/04/27/sql-server-2005-locking-hints-and-examples/
[2]
SQL Server 2005 Books Online. Table Hint (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms187373(SQL.90).aspx

+2  A: 

As far as I am aware, NOLOCK is not default for the SELECT statements. You need to specify specifically if you want to use NOLOCK with SELECT.

Sachin Shanbhag
+6  A: 

See the duplicate

NOLOCK is not the default and can not be set as such. The SQL Authority link is plainly wrong.

gbn
Well, the portion he quoted from the SQL Authority page does say NOLOCK is the default for select statements, but I'd say it's wrong.
Damien_The_Unbeliever
@Damien_The_Unbeliever: Sh1te. I've never liked that site anyway
gbn
Agreed - there are mistakes on that site.
AlexKuznetsov
A: 

Nope, as far as I know ROWLOCK is the default for SELECT statements in NORMAL cicumstances. Use something like SQL profiler to confirm it. Obviously various other circumstances will change the default locking strategy like whether you are in recovery mode and isolations levels etc etc.

uriDium
-1 "rowlock" is granularity and is decided by the engine. It is not an isolation level like nolock
gbn
dope sorry you are correct.
uriDium