SQL Server Inner Join using WITH(NOLOCK)

sql sql-server join inner-join self-join

38,542

Solution 1

You apply with (nolock) to tables, not to subqueries. So, instead of:

(SELECT Table1_id, MAX(version_no) as version_no
 FROM Table1
 where Table1.status='00002'
 GROUP BY Table1_id
) as BR WITH (NOLOCK)

You would write:

(SELECT Table1_id, MAX(version_no) as version_no
 FROM Table1 WITH (NOLOCK)
 where Table1.status='00002'
 GROUP BY Table1_id
) BR

Solution 2

Just put

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

before your query. NOLOCK behavior will be used for all your tables in the query. Using hint NOLOCK just means using READ UNCOMMITTED transaction isolation level for a particular table.

Solution 3

The error is where you have WITH (NOLOCK) in the GROUP BY clause, when it should only be in the FROM clause:

 GROUP BY Table1_id) as  BR WITH (NOLOCK)

should be

FROM Table1 WITH (NOLOCK)

This is 3 lines from the bottom of your posted code. It also appears further up.

Your code with corrections (I think I got them all!):

DECLARE @Pager_PageNumber AS INT, @Pager_PageSize AS INT; SET @Pager_PageNumber = 1; SET @Pager_PageSize = 12; SELECT [Name],[Description],[Table1ID],[VersionNo],[Status] FROM(SELECT CAST(Table1.name AS VARCHAR(MAX)) As [Name],CAST(Table1.description AS VARCHAR(MAX)) As [Description],CAST(CAST(Table1.Table1_ID AS DECIMAL(18,0)) AS VARCHAR(MAX)) As
 [Table1ID],CAST(CAST(Table1.VERSION_NO AS DECIMAL(18,0)) AS VARCHAR(MAX)) As [VersionNo],
 CAST(Table2.br_status AS VARCHAR(MAX)) As [Status]  FROM  Table1 WITH (NOLOCK)
  INNER JOIN (SELECT Table1_id, MAX(version_no) as version_no
    FROM Table1 WITH (NOLOCK)
    where Table1.status='00002'
    GROUP BY Table1_id) as BR ON Table1.Table1_id = BR.Table1_id AND BR.version_no=Table1.version_no 
    INNER JOIN Table2 WITH (NOLOCK) ON Table1.status = Table2.br_status_code )A 
    ORDER BY [Name],[Description],[Table1ID],[VersionNo],[Status] OFFSET ((@Pager_PageNumber - 1) * @Pager_PageSize)
     ROWS FETCH NEXT @Pager_PageSize ROWS ONLY;SELECT COUNT(*) FROM(SELECT CAST(Table1.name AS VARCHAR(MAX)) As 
     [Name],CAST(Table1.description AS VARCHAR(MAX)) As [Description],CAST(CAST(Table1.Table1_ID AS DECIMAL(18,0)) 
     AS VARCHAR(MAX)) As [Table1ID],CAST(CAST(Table1.VERSION_NO AS DECIMAL(18,0)) AS VARCHAR(MAX)) As [VersionNo],
     CAST(Table2.br_status AS VARCHAR(MAX)) As [Status]  FROM  Table1 WITH (NOLOCK)  INNER JOIN
      (SELECT Table1_id, MAX(version_no) as version_no
    FROM Table1 WITH (NOLOCK)
    where Table1.status='00002'
    GROUP BY Table1_id) as  BR
     ON Table1.Table1_id = BR.Table1_id AND BR.version_no=Table1.version_no INNER JOIN Table2 
     WITH (NOLOCK) ON Table1.status = Table2.br_status_code )A;

Solution 4

Can you not move the WITH (NOLOCK), so it's inside the sub-query?

Essentially, this...

INNER JOIN ( SELECT Table1_id
                                       ,MAX(version_no) AS version_no
                                 FROM   Table1 WITH ( NOLOCK )
                                 WHERE  Table1.status = '00002'
                                 GROUP BY Table1_id
                               ) AS BR

Full code

DECLARE @Pager_PageNumber AS INT
   ,@Pager_PageSize AS INT;
SET @Pager_PageNumber = 1;
SET @Pager_PageSize = 12;
SELECT  [Name]
       ,[Description]
       ,[Table1ID]
       ,[VersionNo]
       ,[Status]
FROM    ( SELECT    CAST(Table1.name AS VARCHAR(MAX)) AS [Name]
                   ,CAST(Table1.description AS VARCHAR(MAX)) AS [Description]
                   ,CAST(CAST(Table1.Table1_ID AS DECIMAL(18, 0)) AS VARCHAR(MAX)) AS [Table1ID]
                   ,CAST(CAST(Table1.VERSION_NO AS DECIMAL(18, 0)) AS VARCHAR(MAX)) AS [VersionNo]
                   ,CAST(Table2.br_status AS VARCHAR(MAX)) AS [Status]
          FROM      Table1 WITH ( NOLOCK )
                    INNER JOIN ( SELECT Table1_id
                                       ,MAX(version_no) AS version_no
                                 FROM   Table1 WITH ( NOLOCK )
                                 WHERE  Table1.status = '00002'
                                 GROUP BY Table1_id
                               ) AS BR ON Table1.Table1_id = BR.Table1_id
                                          AND BR.version_no = Table1.version_no
                    INNER JOIN Table2 WITH ( NOLOCK ) ON Table1.status = Table2.br_status_code
        ) A
ORDER BY [Name]
       ,[Description]
       ,[Table1ID]
       ,[VersionNo]
       ,[Status]
       OFFSET ( ( @Pager_PageNumber - 1 ) * @Pager_PageSize ) ROWS FETCH NEXT @Pager_PageSize
        ROWS ONLY;
SELECT  COUNT(*)
FROM    ( SELECT    CAST(Table1.name AS VARCHAR(MAX)) AS [Name]
                   ,CAST(Table1.description AS VARCHAR(MAX)) AS [Description]
                   ,CAST(CAST(Table1.Table1_ID AS DECIMAL(18, 0)) AS VARCHAR(MAX)) AS [Table1ID]
                   ,CAST(CAST(Table1.VERSION_NO AS DECIMAL(18, 0)) AS VARCHAR(MAX)) AS [VersionNo]
                   ,CAST(Table2.br_status AS VARCHAR(MAX)) AS [Status]
          FROM      Table1 WITH ( NOLOCK )
                    INNER JOIN ( SELECT Table1_id
                                       ,MAX(version_no) AS version_no
                                 FROM   Table1 WITH ( NOLOCK )
                                 WHERE  Table1.status = '00002'
                                 GROUP BY Table1_id
                               ) AS BR ON Table1.Table1_id = BR.Table1_id
                                          AND BR.version_no = Table1.version_no
                    INNER JOIN Table2 WITH ( NOLOCK ) ON Table1.status = Table2.br_status_code
        ) A;

Solution 5

DECLARE @Pager_PageNumber AS INT,
        @Pager_PageSize AS INT;

SELECT @Pager_PageNumber = 1, @Pager_PageSize = 12;

SELECT [Name],[Description],[BRMAINID],[VersionNo],[Status]
FROM (
    SELECT
        Table1.name As [Name],
        Table1.[description] As [Description],
        Table1.Table1_ID AS [BRMAINID],
        Table1.VERSION_NO AS [VersionNo],
        Table2.br_status AS [Status]
    FROM dbo.Table1 WITH(NOLOCK)
    JOIN (
        SELECT Table1_id, MAX(version_no) as version_no
        FROM dbo.Table1 WITH(NOLOCK)
        WHERE Table1.[status] = '00002'
        GROUP BY Table1_id
    ) AS BR ON Table1.Table1_id = BR.Table1_id AND BR.version_no=Table1.version_no 
    JOIN dbo.Table2 WITH(NOLOCK) ON Table1.status = Table2.br_status_code
) A 
ORDER BY [Name], [Description], [BRMAINID], [VersionNo], [Status]
    OFFSET ((@Pager_PageNumber - 1) * @Pager_PageSize)
    ROWS FETCH NEXT @Pager_PageSize ROWS ONLY

SELECT COUNT(*)
FROM dbo.Table1 WITH(NOLOCK)
JOIN (
    SELECT Table1_id, MAX(version_no) as version_no
    FROM dbo.Table1 WITH(NOLOCK)
    where Table1.[status] = '00002'
    GROUP BY Table1_id
) as BR ON Table1.Table1_id = BR.Table1_id AND BR.version_no=Table1.version_no
JOIN dbo.Table2 WITH(NOLOCK) ON Table1.status = Table2.br_status_code

just try...

View more solutions

38,542

Author by

Syed Osama Maruf

Software Enthusiast

Updated on December 04, 2020

Comments

Syed Osama Maruf over 3 years

I have a database query:

DECLARE @Pager_PageNumber AS INT, @Pager_PageSize AS INT; 

SET @Pager_PageNumber = 1; 
SET @Pager_PageSize = 12; 

SELECT 
    [Name], [Description], [Table1ID], [VersionNo], [Status] 
FROM
    (SELECT 
         CAST(Table1.name AS VARCHAR(MAX)) As [Name],
         CAST(Table1.description AS VARCHAR(MAX)) AS [Description],
         CAST(CAST(Table1.Table1_ID AS DECIMAL(18,0)) AS VARCHAR(MAX)) AS [Table1ID],
         CAST(CAST(Table1.VERSION_NO AS DECIMAL(18,0)) AS VARCHAR(MAX)) AS [VersionNo],
         CAST(Table2.br_status AS VARCHAR(MAX)) AS [Status]  
    FROM  
        Table1 WITH (NOLOCK)
    INNER JOIN 
        (SELECT 
             Table1_id, MAX(version_no) as version_no
         FROM Table1
         WHERE Table1.status = '00002'
         GROUP BY Table1_id) AS BR WITH (NOLOCK) ON Table1.Table1_id = BR.Table1_id 
                                                 AND BR.version_no = Table1.version_no 
    INNER JOIN 
        Table2 WITH (NOLOCK) ON Table1.status = Table2.br_status_code) A 
ORDER BY 
    [Name], [Description], [Table1ID], [VersionNo], [Status] 
    OFFSET ((@Pager_PageNumber - 1) * @Pager_PageSize) ROWS 
       FETCH NEXT @Pager_PageSize ROWS ONLY;

SELECT COUNT(*) 
FROM
    (SELECT 
         CAST(Table1.name AS VARCHAR(MAX)) AS [Name],
         CAST(Table1.description AS VARCHAR(MAX)) AS [Description],
         CAST(CAST(Table1.Table1_ID AS DECIMAL(18,0)) AS VARCHAR(MAX)) AS [Table1ID],
         CAST(CAST(Table1.VERSION_NO AS DECIMAL(18,0)) AS VARCHAR(MAX)) As [VersionNo],
         CAST(Table2.br_status AS VARCHAR(MAX)) AS [Status]  
    FROM  
        Table1 WITH (NOLOCK)  
    INNER JOIN
        (SELECT Table1_id, MAX(version_no) as version_no
         FROM Table1
         WHERE Table1.status = '00002'
         GROUP BY Table1_id) AS BR WITH (NOLOCK) ON Table1.Table1_id = BR.Table1_id 
                                                 AND BR.version_no = Table1.version_no 
    INNER JOIN 
        Table2 WITH (NOLOCK) ON Table1.status = Table2.br_status_code) A;

In SQL Server I get the error near : BR WITH (NOLOCK) that :

Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

But as per my understanding from sources like the syntax is as

SELECT 
    first_name, last_name,
FROM 
    dbo.person p WITH (NOLOCK)
JOIN 
    dbo.employee e WITH (NOLOCK) ON e.person_id = p.person_id
WHERE 
    p.person_id = 1;

So, my query looks just about right.

Also, when I remove the WITH (NOLOCK) next to BR WITH (NOLOCK) i.e. my inner join query the query runs fine. Any ideas of what I might be missing??

PS: My DB compatibility level is 110.