how to use WITH (NOLOCK) in query with JOIN

user_5261509 0
Viewed 22247

How to use WITH (NOLOCK) to this Query. I know how to use it for normal select query. But for a query with join??? any one guide me

 SELECT 
    DISTINCT Amendmentdetails.BadgeNo, ContractNo,isnull(Amendmentdetails.ContractAmendmentNo,'')AS ContractAmendmentNo,
    value As AnnualSalary,
    Amendmentdetails.ContractType,TimesheetCategory,Rotation,RM.CRotDayOn,RM.CRotDayOff ,TSCatDays                         
    from Amendmentdetails 
    Left Join 
    RotationMaster RM
    On
    Amendmentdetails.Rotation =RM.CRotCode  
    Left Join
    TimesheetCategoryMaster TM
    On
    Amendmentdetails.TimesheetCategory=TM.TSCatCode
    Left Join
    SalaryDetails 
    On
    SalaryDetails.contractAmendmentNo =Amendmentdetails.ContractAmendmentNo AND
    Paycode in(1001,1002,1003,1004,1005)
sql-server-2008 sql
3 Answers

Add WITH(NOLOCK) hints after table names or table aliases.

SELECT DISTINCT
    Amendmentdetails.BadgeNo
    , ContractNo
    , ISNULL(Amendmentdetails.ContractAmendmentNo, '') ContractAmendmentNo
    , [value] AnnualSalary
    , Amendmentdetails.ContractType
    , TimesheetCategory
    , Rotation
    , RM.CRotDayOn
    , RM.CRotDayOff 
    , TSCatDays                         
FROM Amendmentdetails WITH(NOLOCK)
LEFT JOIN RotationMaster RM WITH(NOLOCK) ON Amendmentdetails.Rotation = RM.CRotCode  
LEFT JOIN TimesheetCategoryMaster TM WITH(NOLOCK) ON Amendmentdetails.TimesheetCategory = TM.TSCatCode
LEFT JOIN SalaryDetails WITH(NOLOCK) ON SalaryDetails.contractAmendmentNo = Amendmentdetails.ContractAmendmentNo AND Paycode IN (1001,1002,1003,1004,1005)

Make sure that any tables that you apply WITH (NOLOCK) to have a clustered index.

The easiest way to do this is to add an integer-based Id primary key column that auto-increments.

Bear in mind that the result set can contain rows that have not yet been committed, that are often later rolled back.

If WITH(NOLOCK) is applied to a table that has a non-clustered index then row-indexes can be changed by other transactions as the row data is being streamed into the result-table. This means that the result-set can be missing rows or display the same row multiple times.

READ COMMITTED adds an additional issue where data is corrupted within a single column where multiple users change the same cell simultaneously.

Keeping all this in mind will help you use WITH(NOLOCK) effectively.

You needn't bother with WITH(NOLOCK) at all if you add the command SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to the top of your query.

user_12718980
Related