SQL 查询在 SQL Server 2012 中工作正常，但在 SQL Server 2008 R2 中无法执行 [英] SQL query working fine in SQL Server 2012, but failed to execute in SQL Server 2008 R2

查看：44
本文介绍了SQL 查询在 SQL Server 2012 中工作正常，但在 SQL Server 2008 R2 中无法执行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个名为 MyTextstable (myTextsTable_id INT, myTextsTable_text VARCHAR(MAX)) 的表.该表有大约 400 万条记录，我正在尝试删除 VARCHAR(MAX) 列 myTextsTable_text 以下范围内的任何 ASCII 字符实例.

  • 00 - 08
  • 11 - 12
  • 14 - 31
  • 127

我编写了以下 SQL 查询，该查询在 SQL Server 2012 上耗时不到 10 分钟，但即使在两小时后仍无法在 SQL Server 2008 R2 上执行(因此我停止了执行).请注意，我已经在 SQL Server 2012 上恢复了 SQL Server 2008 R2 数据库的备份(即数据完全相同).

开始交易 [Tran1]开始尝试更新 myTextsTableSET myTextsTable_text = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE)(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(myTextsTable_text, CHAR(0), ''), CHAR(1), ''), CHAR(2), ''), CHAR(3), ''), CHAR(4), ''), CHAR(5), ''), CHAR(6), ''), CHAR(7), ''), CHAR(8), ''), CHAR(11), ''), CHAR(12), ''), CHAR(14), ''), CHAR(15), ''), CHAR(16), ''), CHAR(17), ''),CHAR(18), ''), CHAR(19), ''), CHAR(20), ''), CHAR(21), ''), CHAR(22), ''), CHAR(23),''), CHAR(24), ''), CHAR(25), ''), CHAR(26), ''), CHAR(27), ''), CHAR(28), ''), CHAR(29), ''), CHAR(30), ''), CHAR(31), ''), CHAR(127), '')WHERE myTextsTable_text LIKE '%[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(11) + CHAR(12) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + 字符 (127) + ']%';提交交易 [Tran1];结束尝试开始捕捉回滚交易 [Tran1];--PRINT ERROR_MESSAGE();结束捕捉；

只有 135 条记录受到影响.由于单个 UPDATE 查询在 SQL Server 2008 中不起作用，我尝试了以下使用临时表的方法.

开始交易 [Tran1]开始尝试如果 OBJECT_ID('tempdb..#myTextsTable') 不是 NULL 删除表 #myTextsTable;选择 myTextsTable_id、myTextsTable_text进入#myTextsTable从我的文本表WHERE myTextsTable_text LIKE '%[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(11) + CHAR(12) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + 字符 (127) + ']%';更新#myTextsTableSET myTextsTable_text = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE)(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(myTextsTable_text, CHAR(0), ''), CHAR(1), ''), CHAR(2), ''), CHAR(3), ''), CHAR(4), ''), CHAR(5), ''), CHAR(6), ''), CHAR(7), ''), CHAR(8), ''), CHAR(11), ''), CHAR(12), ''), CHAR(14), ''), CHAR(15), ''), CHAR(16), ''), CHAR(17), ''),CHAR(18), ''), CHAR(19), ''), CHAR(20), ''), CHAR(21), ''), CHAR(22), ''), CHAR(23),''), CHAR(24), ''), CHAR(25), ''), CHAR(26), ''), CHAR(27), ''), CHAR(28), ''), CHAR(29), ''), CHAR(30), ''), CHAR(31), ''), CHAR(127), '')更新 myTextsTableSET myTextsTable_text = new.myTextsTable_text从我的文本表INNER JOIN #myTextsTable new ON new.myTextsTable_id=myTextsTable.myTextsTable_id删除表#myTextsTable;提交交易 [Tran1];结束尝试开始捕捉回滚交易 [Tran1];--PRINT ERROR_MESSAGE();结束捕捉；

然而，结果是一样的.在 SQL Server 2012 中工作得很好，但在 SQL Server 2008 R2 中不工作.我发现 UPDATE 查询在两小时后仍在执行(记录在几分钟内保存到临时表 (#myTextsTable) 中，我稍后检查了这个确定哪个部分需要更长的时间).

由于上述两种方法不起作用，我尝试使用 TABLE 变量使用它来检查它是否有任何区别，但结果是相同的(即在 SQL Server 2012 中工作正常但不是在 SQL Server 2008 R2)

开始交易 [Tran1]开始尝试声明@myTextsTable 表(myTextsTable_id INT，myTextsTable_text VARCHAR(MAX))插入@myTextsTable(myTextsTable_id, myTextsTable_text)选择 myTextsTable_id、myTextsTable_text从我的文本表WHERE myTextsTable_text LIKE '%[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(11) + CHAR(12) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + 字符 (127) + ']%';更新@myTextsTableSET myTextsTable_text = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE)(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(myTextsTable_text, CHAR(0), ''), CHAR(1), ''), CHAR(2), ''), CHAR(3), ''), CHAR(4), ''), CHAR(5), ''), CHAR(6), ''), CHAR(7), ''), CHAR(8), ''), CHAR(11), ''), CHAR(12), ''), CHAR(14), ''), CHAR(15), ''), CHAR(16), ''), CHAR(17), ''),CHAR(18), ''), CHAR(19), ''), CHAR(20), ''), CHAR(21), ''), CHAR(22), ''), CHAR(23),''), CHAR(24), ''), CHAR(25), ''), CHAR(26), ''), CHAR(27), ''), CHAR(28), ''), CHAR(29), ''), CHAR(30), ''), CHAR(31), ''), CHAR(127), '')更新 myTextsTableSET myTextsTable_updated = GETDATE(),myTextsTable_updatedby = '根据 V87058',myTextsTable_text = new.myTextsTable_text从我的文本表INNER JOIN @myTextsTable new ON new.myTextsTable_id=myTextsTable.myTextsTable_id提交交易 [Tran1];结束尝试开始捕捉回滚交易 [Tran1];--PRINT ERROR_MESSAGE();结束捕捉；

谁能解释为什么会发生这种情况?如何在 SQL Server 2008 R2 中使用此 SQL 查询?

注意:我知道数据库服务器/层中的字符串操作并不理想，建议在应用程序层进行字符串操作，然后将其保存在数据库中.但是，我试图理解为什么这会在一个版本中出现问题，而在另一个版本中却没有.

<块引用>

SQL Server 2012
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
Windows NT 6.3 (Build 9600:) 上的标准版(64 位)(管理程序)

SQL Server 2008 R2
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
Windows NT 6.3 (Build 9600:) 上的标准版(64 位)(管理程序)

解决方案

这是一个

而且修复也很容易.要么使用非 MAX 数据类型...

... 或二进制排序规则

/*不挂*/声明 @VcMax varchar(max)= char(0) + 'a'SELECT REPLACE(@VcMax COLLATE Latin1_General_100_BIN2, char(0), '')

I have a table called MyTextstable (myTextsTable_id INT, myTextsTable_text VARCHAR(MAX)). This table has around 4 million records and I am trying to remove any instance of the ASCII characters in the following range(s) the VARCHAR(MAX) column myTextsTable_text.

  • 00 - 08
  • 11 - 12
  • 14 - 31
  • 127

I have written the following SQL query, which is taking under 10 minutes on SQL Server 2012, but failed to execute on SQL Server 2008 R2 even after two hours (so I stopped the execution). Please note I have restored the backup of a SQL Server 2008 R2 database on SQL Server 2012 (i.e. the data is exactly same).

BEGIN TRANSACTION [Tran1]

BEGIN TRY
    UPDATE myTextsTable
    SET myTextsTable_text = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(myTextsTable_text, CHAR(0), ''), CHAR(1), ''), CHAR(2), ''), CHAR(3), ''), CHAR(4), ''), CHAR(5), ''), CHAR(6), ''), CHAR(7), ''), CHAR(8), ''), CHAR(11), ''), CHAR(12), ''), CHAR(14), ''), CHAR(15), ''), CHAR(16), ''), CHAR(17), ''), CHAR(18), ''), CHAR(19), ''), CHAR(20), ''), CHAR(21), ''), CHAR(22), ''), CHAR(23), ''), CHAR(24), ''), CHAR(25), ''), CHAR(26), ''), CHAR(27), ''), CHAR(28), ''), CHAR(29), ''), CHAR(30), ''), CHAR(31), ''), CHAR(127), '')
    WHERE myTextsTable_text LIKE '%[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(11) + CHAR(12) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(127) + ']%';
    COMMIT TRANSACTION [Tran1];
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION [Tran1];
    --PRINT ERROR_MESSAGE();
END CATCH;

There are only 135 records affected. As the single UPDATE query wasn't working in SQL Server 2008, I have tried the following approach with a temp table.

BEGIN TRANSACTION [Tran1]

BEGIN TRY
    IF OBJECT_ID('tempdb..#myTextsTable') IS NOT NULL DROP TABLE #myTextsTable;
    SELECT myTextsTable_id, myTextsTable_text
    INTO #myTextsTable
    FROM myTextsTable
    WHERE myTextsTable_text LIKE '%[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(11) + CHAR(12) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(127) + ']%';

    UPDATE #myTextsTable
    SET myTextsTable_text = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(myTextsTable_text, CHAR(0), ''), CHAR(1), ''), CHAR(2), ''), CHAR(3), ''), CHAR(4), ''), CHAR(5), ''), CHAR(6), ''), CHAR(7), ''), CHAR(8), ''), CHAR(11), ''), CHAR(12), ''), CHAR(14), ''), CHAR(15), ''), CHAR(16), ''), CHAR(17), ''), CHAR(18), ''), CHAR(19), ''), CHAR(20), ''), CHAR(21), ''), CHAR(22), ''), CHAR(23), ''), CHAR(24), ''), CHAR(25), ''), CHAR(26), ''), CHAR(27), ''), CHAR(28), ''), CHAR(29), ''), CHAR(30), ''), CHAR(31), ''), CHAR(127), '')

    UPDATE myTextsTable
    SET myTextsTable_text = new.myTextsTable_text
    FROM myTextsTable
    INNER JOIN #myTextsTable new ON new.myTextsTable_id=myTextsTable.myTextsTable_id

    DROP TABLE #myTextsTable;

    COMMIT TRANSACTION [Tran1];
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION [Tran1];
    --PRINT ERROR_MESSAGE();
END CATCH;

However, the result is same. Works perfectly fine in SQL Server 2012, but not in SQL Server 2008 R2. I found that the UPDATE query was still executing even after two hours (the records were saved into the temp table (#myTextsTable) in a few minutes, I checked this later to make sure which part is taking longer).

As the aforementioned two ways weren't working, I have tried using this using TABLE variables just to check if it makes any difference, but the result was same (i.e. works fine in SQL Server 2012 but not in SQL Server 2008 R2)

BEGIN TRANSACTION [Tran1]

BEGIN TRY
    DECLARE @myTextsTable TABLE (myTextsTable_id INT, myTextsTable_text VARCHAR(MAX))
    INSERT INTO @myTextsTable(myTextsTable_id, myTextsTable_text)
    SELECT myTextsTable_id, myTextsTable_text
    FROM myTextsTable
    WHERE myTextsTable_text LIKE '%[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(11) + CHAR(12) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(127) + ']%';

    UPDATE @myTextsTable
    SET myTextsTable_text = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(myTextsTable_text, CHAR(0), ''), CHAR(1), ''), CHAR(2), ''), CHAR(3), ''), CHAR(4), ''), CHAR(5), ''), CHAR(6), ''), CHAR(7), ''), CHAR(8), ''), CHAR(11), ''), CHAR(12), ''), CHAR(14), ''), CHAR(15), ''), CHAR(16), ''), CHAR(17), ''), CHAR(18), ''), CHAR(19), ''), CHAR(20), ''), CHAR(21), ''), CHAR(22), ''), CHAR(23), ''), CHAR(24), ''), CHAR(25), ''), CHAR(26), ''), CHAR(27), ''), CHAR(28), ''), CHAR(29), ''), CHAR(30), ''), CHAR(31), ''), CHAR(127), '')

    UPDATE myTextsTable
    SET myTextsTable_updated = GETDATE()
        ,myTextsTable_updatedby = 'As per V87058'
        ,myTextsTable_text = new.myTextsTable_text
    FROM myTextsTable
    INNER JOIN @myTextsTable new ON new.myTextsTable_id=myTextsTable.myTextsTable_id

    COMMIT TRANSACTION [Tran1];
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION [Tran1];
    --PRINT ERROR_MESSAGE();
END CATCH;

Could anyone explain why this would happen? How to make this SQL query work in SQL Server 2008 R2?

Note: I know that the string manipulations in database server/layer are not ideal and it would be recommended to do string manipulations in application layer and then save it in DB. But, I am trying to understand why this would be a problem in one version and why not in another version.

SQL Server 2012
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

SQL Server 2008 R2
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

解决方案

This is a known issue on SQL Server 2008 with LOB datatypes and certain collations.

It is easy to reproduce

/*Hangs on 2008*/

DECLARE @VcMax varchar(max)= char(0) + 'a'

SELECT REPLACE(@VcMax COLLATE Latin1_General_CS_AS, char(0), '')

Whilst hung it is CPU bound and seems to be in an infinite loop through these functions.

And the fix is easy too. Either use a non MAX datatype...

... or a binary collation 

/*Doesn't Hang*/
DECLARE @VcMax varchar(max)= char(0) + 'a'

SELECT REPLACE(@VcMax COLLATE Latin1_General_100_BIN2, char(0), '')

这篇关于SQL 查询在 SQL Server 2012 中工作正常，但在 SQL Server 2008 R2 中无法执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆