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...
