does "with (nolock)" absolutely guarantee no locks are taken?

I need to run a few short-running queries against a production system. I need to be absolutely certain that SS doesn't take out any locks on the table as a result.

Does "with (nolock)" absolutely guarantee this? I've read BOL on the topic and I understand isolation level read-uncommitted. But I want to validate that there's not any undocumented behavior in SS that might violate the documentation (which clearly states that no shared locks are issued).

Thanks!

You're talking two different things.

with (nolock) is a HINT. That means there is a possibility that SS will override it.

SET TRANSACTION ISOLATION LEVEL is a command. AFAIK, SS won't override this.

|||SQL Server honors the read uncommitted hint. It is however possible that you might get some error due to the dirty read behavior. The transaction isolation level is recommended if you do not want to specify hint on every table that you query and it is for the session. You can also set it in the tools option so it is always automatic. Note that this does affect behavior of any query since you will be reading uncommitted data i.e., perform dirty reads. In SQL Server 2005, you can use the READ COMMITTED SNAPSHOT option at the database level or the new snapshot isoaltion level which uses versioning mechanism to provide consistent view of data without taking locks.|||

The answer I am about to give is not likely to be pertinent to your question since you state short running, but just in case since you were so adament about NO LOCKS :) There is one tiny exception to the nolock/read uncommitted isolation level, in that it does take a shared schema lock so other users cannot drop/alter the table while your query is running. in 2005, run this:

create table testLocks
(
testLockId int,
bigValue char(8000) default (replicate('*',8000))
)

insert into testLocks(testLockId)
select 1
go 1000

set transaction isolation level read uncommitted
select * from testLocks
cross join testLocks as t2
-

Then on another connection run this (that query will return 1000000 16KB rows, so it will take a while :)

select login_name,
case des.transaction_isolation_level
when 0 then 'Unspecified' when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted' when 3 then 'Repeatable'
when 4 then 'Serializable' when 5 then 'Snapshot'
end as transaction_isolation_level,
request_session_id, resource_type, resource_subtype, request_mode,
request_type, request_status, request_owner_type,
case when resource_type = 'object' then object_name(resource_associated_entity_id)
when resource_type = 'database' then db_name(resource_associated_entity_id)
when resource_type in ('key','page') then
(select object_name(object_id) from sys.partitions
where hobt_id = resource_associated_entity_id)
else cast(resource_associated_entity_id as varchar(20))
end
from sys.dm_tran_locks dtl
left outer join sys.dm_exec_sessions des
on dtl.request_session_id = des.session_id
where request_session_id <> @.@.spid

On my 3Ghz, 512 MB machine running Express Edition (on a Media Center PC) I got two rows, one of which was the shared schema lock, the other was a bulk operation lock, likely in Tempdb trying to build these rows. Unless you are dropping and creating rows, this is unlikely to be a concern.

|||If you perform insert/update in a transaction that has isolation level READ UNCOMMITTED it will anyhow acquire X locks. I suppose your short transaction are read-only though.|||We all kind of forgot about that one huh? Good point :)|||

thanks everyone for your very helpful information.

|||sorry I should have mentioned that I'm only running queries