SQL Server Performance

Select statement with (NOLock)

Discussion in 'SQL Server 2005 General Developer Questions' started by yhchan2005, Oct 14, 2010.

  1. yhchan2005 Member

    Refer to below SQL script
    Select Field1, Field2,
    ( Select Field99 from Table3 with (NoLock)
    Where .... ) Field3
    from Table1 with (NOLock)
    Inner Join Table2 with (NoLock) ON.....
    Where ......
    Do we need to put in the (NoLock) for subquery and inner join / left jon
    if we have put in the (Nolock) in main query ????
    thanks.


    yhchan2005, Oct 14, 2010
    #2

  2. FrankKalis Moderator

    You need WITH (NOLOCK) for every table that you don't want to be locked. If this happens to be all the tables in the query, you will be better off using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED right before the query. This lifts you off the burden to specify all these individual table hints. And just to avoid any subsequent maybe unpleasant suprises afterwards, I would set it back to the default setting after the query (or at the end of the procedure) by SET TRANSACTION ISOLATION LEVEL READ COMMITED.
    FrankKalis, Oct 14, 2010
    #3

Share This Page