• I have to take issue with the following statement:

    When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it.

    A dirty read is not the result of not issuing locks during a Select query. A dirty read is the result of not honoring locks issued by update/insert/delete queries.

    However, a Select query (without using nolock) does do both. It honors locks by other queries and issues locks of its own. The locks it issues are shared locks, meaning other queries requesting a shared lock can still read the data.

    If you need an exclusive lock (i.e., to perform an update), a shared lock will block the exclusive lock. On a system that is heavily read and only ocassionally updated, not using nolock can cause extensive blocking and often time outs. This is particularly true when the lock count is high enough to cause SQL Server to escalate the exclusive lock request to a table lock.

    So when to use nolock is definitely a judgement call.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]