Nolocks on a view - does it nolock all the tables in the view?

I received this question from a colleague this week:

"If there is a view that is not using nolock [in the from clause] but you have a stored procedure that uses nolock when it calls the view then do all the FROMs in the view run with nolock?"


The simple answer : Yes, the nolock hint on the view should propagate down to the tables queried in the view*.


A brief visit to our favorite resource, Books Online, reveals the following:

In SQL Server 2005/2008, all lock hints are propagated to all the tables and views that are referenced in a view. Also, SQL Server performs the corresponding lock consistency checks.

 And the asterisk:

*If a table contains computed columns and the computed columns are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables. This means the table hints are not propagated. For example, a NOLOCK table hint is specified on a table in the query. This table has computed columns that are computed by a combination of expressions and functions that access columns in another table. The tables referenced by the expressions and functions do not use the NOLOCK table hint when accessed.

And my (rather simple) test proved it out:

use master

go

create database lockTest

go

use lockTest

go

create table t1 (f1 int, f2 int)

go

insert into t1 values (1, 1)

insert into t1 values (2, 2)

go

create view v1 as

select *

from t1

go

set transaction isolation level serializable

go

begin tran

select * from v1

insert into v1 values (3,3)

-- go into a new window and run the following:

/*

-- Works

select *

from v1 with (nolock)

-- Hangs

select *

from v1

*/

/*

-- cleanup

use master

go

drop database lockTest

go

*/