title: SQL Server Inner Join using WITH(NOLOCK)
body: <p>I have a database query:</p>
  
  <pre><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
           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;
  </code></pre>
  
  <p>In SQL Server I get the error near : <code>BR WITH (NOLOCK)</code> that :</p>
  
  <blockquote>
    <blockquote>
      <p>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.</p>
    </blockquote>
  </blockquote>
  
  <p>But as per my understanding from sources <a href="http://sqlserverplanet.com/tsql/using-with-nolock" rel="nofollow">like</a> the syntax is as</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>So, my query looks just about right.</p>
  
  <p>Also, when I remove the WITH (NOLOCK) next to <code>BR WITH (NOLOCK)</code> i.e. my inner join query the query runs fine. Any ideas of what I might be missing??</p>
  
  <p><strong>PS:</strong> My DB compatibility level is 110.</p>