TSQL NOLOCK VIEW and Store procedure

Question

In our company we tend to us views and store procedures.

We've recently started to implement the NOLOCK statement to alot of our views.

i was wondering if applying NOLOCK to a view, it "trickles down" to the store procedure...

Say i have a view call viewPartyPackage

and view statement was...

SELECT     PartyPackageID, Name, Created, LastModified, Deleted FROM        dbo.PartyPackage WITH (NOLOCK) WHERE     (Deleted = 0)

and also i had a store proc:

ALTER proc [dbo].[partypackage_Select](  @PartyPackageID bigint = null ) AS SELECT * FROM [viewPartyPackage] PartyPackage WHERE (@PartyPackageID is null or @PartyPackageID = [PartyPackageID])

would i lose the NOLOCK feature because im calling from a store proc and in turn would i need to put a (NOLOCK) on the store proc as well? or does the NOLOCK thats in the view come into play?

Answer 1

The NOLOCK in the view will take effect no matter where the view is called from.

Answer 2

See the answers to this SO question. To quote:

See Table Hints in MSDN: "In SQL Server 2005, 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."