使用 WITH(NOLOCK) 的 SQL Server 内部联接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34065670/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Inner Join using WITH(NOLOCK)
提问by Syed Osama Maruf
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 :
在 SQL Server 中，我收到附近的错误：BR WITH (NOLOCK)即：
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.
关键字“WITH”附近的语法不正确。关键字“with”附近的语法不正确。如果此语句是公共表表达式、xmlnamespaces 子句或更改跟踪上下文子句，则前一条语句必须以分号终止。
But as per my understanding from sources likethe 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??
此外，当我删除BR WITH (NOLOCK)ie 内连接查询旁边的 WITH (NOLOCK) 时，查询运行良好。关于我可能会遗漏的任何想法？
PS:My DB compatibility level is 110.
PS：我的数据库兼容级别是 110。
回答by Gordon Linoff
You apply with (nolock)to tables, not to subqueries. So, instead of:
您适用with (nolock)于表，而不是子查询。所以，而不是：
(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
回答by Denis Reznik
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.
在您查询之前。NOLOCK 行为将用于查询中的所有表。使用提示 NOLOCK 仅意味着对特定表使用 READ UNCOMMITTED 事务隔离级别。
回答by BeanFrog
The error is where you have WITH (NOLOCK)in the GROUP BY clause, when it should only be in the FROM clause:
错误是您WITH (NOLOCK)在 GROUP BY 子句中的位置，而它应该只出现在 FROM 子句中：
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.
这是您发布的代码底部的 3 行。它也出现在更远的地方。
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;
回答by christiandev
Can you not move the WITH (NOLOCK), so it's inside the sub-query?
你不能移动WITH (NOLOCK), 所以它在子查询中吗？
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;
回答by Devart
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...
你试一试...
