Prev: Looking for a function to declare a table variable
Next: Backup Analysis databases
From: Joe K. on 18 May 2010 11:01

I have a SQL Server 2005 database that was several stored procedures and
several views.

Will the stored procedure listed below with the NOLOCK statement not lock
the dbo.Team table?

The stored procedure has numerous other processing details but I took that
part out of the procedure.

My goal is to make sure the dbo.Team table is not locked.

Please help me resolve this issue.




CREATE view v_Team
AS
SELECT * from dbo.Team

CREATE PROCEDURE dbo.MasterTeam
AS
select * from v_Team (NOLOCK)
GO



From: John Bell on 18 May 2010 14:33
On Tue, 18 May 2010 08:01:01 -0700, Joe K.
<JoeK(a)discussions.microsoft.com> wrote:

>
>I have a SQL Server 2005 database that was several stored procedures and
>several views.
>
>Will the stored procedure listed below with the NOLOCK statement not lock
>the dbo.Team table?
>
>The stored procedure has numerous other processing details but I took that
>part out of the procedure.
>
>My goal is to make sure the dbo.Team table is not locked.
>
>Please help me resolve this issue.
>
>
>
>
>CREATE view v_Team
>AS
>SELECT * from dbo.Team
>
>CREATE PROCEDURE dbo.MasterTeam
>AS
>select * from v_Team (NOLOCK)
>GO
>
>

This is a table hint see
http://msdn.microsoft.com/en-us/library/ms187373(SQL.90).aspx it may
mean that you get dirty reads and it can still take out shared schema
locks.

John
From: Andrew J. Kelly on 18 May 2010 18:11
Yes it will but you have to be careful in that it will be applied to all
tables in the view which my not be the desired effect. You may want to place
the hint in the view itself. But one comment I want to make is that
omitting the WITH keyword is being depreciated and will be required in
future versions so I always recommend you use it in all code.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"John Bell" <jbellnewsposts(a)hotmail.com> wrote in message
news:tqm5v59njhad1v1aof7vpl3c5tt41h0ttc(a)4ax.com...
> On Tue, 18 May 2010 08:01:01 -0700, Joe K.
> <JoeK(a)discussions.microsoft.com> wrote:
>
>>
>>I have a SQL Server 2005 database that was several stored procedures and
>>several views.
>>
>>Will the stored procedure listed below with the NOLOCK statement not lock
>>the dbo.Team table?
>>
>>The stored procedure has numerous other processing details but I took that
>>part out of the procedure.
>>
>>My goal is to make sure the dbo.Team table is not locked.
>>
>>Please help me resolve this issue.
>>
>>
>>
>>
>>CREATE view v_Team
>>AS
>>SELECT * from dbo.Team
>>
>>CREATE PROCEDURE dbo.MasterTeam
>>AS
>>select * from v_Team (NOLOCK)
>>GO
>>
>>
>
> This is a table hint see
> http://msdn.microsoft.com/en-us/library/ms187373(SQL.90).aspx it may
> mean that you get dirty reads and it can still take out shared schema
> locks.
>
> John

 | 
Pages: 1
Prev: Looking for a function to declare a table variable
Next: Backup Analysis databases