how to use WITH (NOLOCK) in query with JOIN

Question

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)

With(NOLOCK) goes after the alias and before the ON in the JOIN. Any MS documentation would have showed you that. — SS_DBA
– SS_DBA, Commented May 24, 2017 at 14:13

WonderWorker · Accepted Answer · 2019-10-08 10:09:44Z

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.

WonderWorker · Accepted Answer · 2019-10-08 10:08:41Z

with (nolock) goes right after the table name if there is no alias. If there is an alias it goes right after that.

SELECT 
    DISTINCT Amendmentdetails.BadgeNo, ContractNo,isnull(Amendmentdetails.ContractAmendmentNo,'')AS ContractAmendmentNo,
    value As 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)

You can read more about table hints here: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

Shawn · Accepted Answer · 2017-05-24 14:57:17Z

You can add NOLOCK after the table/alias.

SELECT t1.col1, t2.col1
FROM table1 t1 NOLOCK 
INNER JOIN table2 t2 NOLOCK ON t1.x = t2.x
WHERE t1.col2 = 42

The thing about NOLOCK though is that it isn't the magical bullet that it seems to be. It is essentially READUNCOMMITTED, so you could be reading dirty data that changes right after you SELECT it. If it's not an issue, then go ahead and use it, but if dirty data will impact your final results, you might want to look at some other way to deal with your blocking issues.

Also see:

https://blogs.msdn.microsoft.com/davidlean/2009/04/05/sql-server-nolock-hint-other-poor-ideas/

http://itknowledgeexchange.techtarget.com/sql-server/what-does-with-nolock-actually-mean/

Collectives™ on Stack Overflow

how to use WITH (NOLOCK) in query with JOIN

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related