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

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

No Inner Joins?

Hello:

I'm relatively new to MSQL - I come from a MySQL background and have a crap load of experience with Access/Jet. I tried executing a query in MSQL last night, and got an error. Here's my query:

update inv_mast inner join inv_loc on inv_mast.inv_mast_uid = inv_loc.inv_mast_uid SET inv_mast.short_code = 'Entrelec' WHERE inv_loc.primary_supplier_id = '100086'

I got the error:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'inner'.

So I tried changing the syntax to:

UPDATE inv_mast
SET inv_mast.short_code = 'Entrelec'
FROM inv_mast, inv_loc
WHERE inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
AND inv_loc.primary_supplier_id = '100086'

And didn't get an error, but I didn't get any results (0 row(s) affected).

What am I doing wrong?

If my queries don't give enough of an explanation of the layout of the data, let me know, I'll gladly explain more.

Thanks all! :)

-jimyour update statment probably should have looked like:
update inv_mast
SET inv_mast.short_code = 'Entrelec'
from inv_mast
inner join inv_loc on inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
WHERE inv_loc.primary_supplier_id = '100086'

and if you like aliases:
update im
SET im.short_code = 'Entrelec'
from inv_mast im
join inv_loc il on im.inv_mast_uid = il.inv_mast_uid
WHERE il.primary_supplier_id = '100086'

as to why you didn't update anything, maybe your keys don't line up or you have nothing that satisfies your where clause. what does the following produce?
select im.inv_mast_uid,il.primary_supplier_id
from inv_mast im
join inv_loc il on im.inv_mast_uid = il.inv_mast_uid
order by 1,2|||Paul,

Thanks a TON for your response! The last query you posted lists all the item master ID's with their vendor IDs. So I guess the keys are good.

I'm going to try your update query now...

Dude - I owe you big time!! It worked like a charm - how can I thank you?

-jim