No Lock

Lets say i have a view.

vuTestingNoLocks

this view looks like this...

Create View vuTestingNoLocks as

SElect *

From dbo.Employees

inner Join dbo.EmployeeTerritories on EmployeeTerritories.EmployeeID = Employees.EmployeeID

If I select from this view using Select * From vuTestingNoLocks (NOLOCK)

Does the (nolock) command propegate down through the tables? Meaning will it scan tables that are locked still ignoring their locks?

I think you should be able to test it out and come to your own conclusions. Let me know what you are going to interpret from this.

In one window, create these tables, populate dummy data and create the view

Code Snippet

--Start of window1

create table t1 (t1id int identity(10,10),

t1code char(2)

)

create table t2 (t2id int,

t2name varchar(10)

)

insert t1 select 'AB'

insert t1 select 'BC'

insert t1 select 'CD'

insert t2 select 10, 'Group AB'

insert t2 select 20, 'Group BC'

insert t2 select 30, 'Group CD'

create view vw_t1_t2 as

select t1id, t1code, t2name

from t1 join t2

on t1.t1id = t2id

--End of window1

--start of window2

--we are setting up to take exclusive lock

begin tran

update vw_t1_t2

set t1code = 'KK'

--end of window2

--start of window3

--Issue this once

select * from vw_t1_t2 (nolock)

--and this later

select * from vw_t1_t2

--end of window3

|||

Yes, NOLOCK table hint will be propageted to the tables in view defination.

It is also very usefull for indexed views as they are treated as tables as well.

Please see following article for more detail.

http://msdn2.microsoft.com/en-us/library/ms190237.aspx

Hope this will answer your question.

Thanks,

FB

|||

I appeciate you taking the time to help me test the theory myself. I didn't think of Opening a transaction and not comitting it to Lock the table..

To answer your question it does propogate down..

|||

THank you for that answer it was very helpful...