SQL Server 2008: how does NOLOCK works for CTE?

I have CTEs which all uses NOLOCK inside. But then selects from those CTEs in parent CTEs using children CTEs doesn't use NOLOCK in presumption that it is already NOLOCK'd. And the final select doesn't use NOLOCK either.

Something like that:

with cte1 as
(select * from tab1 (nolock)),
cte2 as
(select * from cte1)

select * from cte2

or shall I write

with cte1 as
(select * from tab1 (nolock)),
cte2 as
(select * from cte1 (nolock))

select * from cte2 (nolock)

thank开发者_StackOverflows

You don't need the outer nolock to avoid taking shared locks on tab1. You can easily verify this by setting up a SQL Profiler trace capturing the various events in the locks category, filtering on the spid of an SSMS connection and trying both versions.

nolock is quite a dangerous setting though, are you aware of all of the possible downsides to using it (dirty reads, reading data twice or not at all)?

NOLOCK on an "outer" query applies to any inner queries too. A CTE is just a macro like a view or inline table udf: nothing more, nothing less. So you actually have (ignoring NOLOCK hints)

select * from (
               select * from (
                             select * from tab1
                             ) t1
               ) t2

From Table Hints on MSDN, under "Remarks"

All lock hints are propagated to all the tables and views that are accessed by the query plan, including tables and views referenced in a view.

In this case, you need only one. Doesn't matter where.

Where it does matter is where you have JOINs. If cte1 was a join of 2 tables, you'd need it for each table,. Or specify it once at a higher/outer level.

Oh, and I'll join in with everyone else: NOLOCK is a bad idea

The innermost nolock is sufficient, no need to repeat it for the outer selects.

You can test this by starting a transaction without ending it:

begin transaction
; with YourCte ( ...

Then you can view the locks using Management Studio. They'll be there until the transaction times out.

NOLOCK for CTEs work the same way as with everything else: it causes inconsistent results. Use SNAPSHOT instead, see SQL Server 2005 Row Versioning-Based Transaction Isolation.