server版本查詢 - sql server下載



SQL查詢在SQL Server 2012中正常工作,但無法在SQL Server 2008 R2中執行 (2)

可能問題在於替換中的嵌套,並且在執行時報告,而不是compilación檢查@@ nestlevel函數。 https://technet.microsoft.com/en-us/library/ms190607(v=sql.105).aspx

https://src-bin.com

我有一個名為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數據庫的備份(即數據完全相同)。

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;

只有135個記錄受到影響。 由於單個UPDATE查詢在SQL Server 2008中無法正常工作,因此我嘗試了使用臨時表的下列方法。

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;

但是,結果是一樣的。 在SQL Server 2012中工作得很好,但在SQL Server 2008 R2中沒有。 我發現UPDATE查詢甚至在兩個小時後仍然在執行(記錄在幾分鐘內被保存到臨時表( #myTextsTable )中,我稍後再檢查以確定哪個部分需要更長的時間)。

由於前面提到的兩種方法都不起作用,我試圖用TABLE變量來檢查它是否有所不同,但結果是一樣的(即在SQL Server 2012中工作正常,但在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;

任何人都可以解釋為什麼會發生? 如何使這個SQL查詢在SQL Server 2008 R2中工作?

注意:我知道數據庫服務器/圖層中的字符串操作並不理想,建議在應用程序層進行字符串操作,然後將其保存在數據庫中。 但是,我試圖理解為什麼這將是一個版本的問題,為什麼不在另一個版本。

SQL Server 2012
Microsoft SQL Server 2012 - 11.0.5058.0(X64)
標準版(64位)在Windows NT 6.3(內部版本9600:)(管理程序)

SQL Server 2008 R2
Microsoft SQL Server 2012 - 11.0.5058.0(X64)
標準版(64位)在Windows NT 6.3(內部版本9600:)(管理程序)


Answer #1

對於將來閱讀此內容的人來說,以下方法可以正常工作。

方法1.按照Martin Smith的建議(請參閱接受的答案),將UPDATE SQL查詢中的VARCHAR(MAX)列的COLLATION更改為BINARY COLLATION

REPLACE(myTextsTable_text COLLATE Latin1_General_100_BIN2,CHAR(0),...

解決方案如下:

GO
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 COLLATE Latin1_General_100_BIN2, 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

    DROP TABLE #myTextsTable;

    COMMIT TRANSACTION [Tran1];
END TRY

方法2:我創建了一個SQL function ,用STUFF替換這些字符,而不是使用REPLACE函數。

注意:請注意SQL函數寫入我的具體要求。 因此,它只取代以下範圍的字符。

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

-

Go
CREATE FUNCTION [dbo].RemoveASCIICharactersInRange(@InputString VARCHAR(MAX))
    RETURNS VARCHAR(MAX)
    AS
    BEGIN
        IF @InputString IS NOT NULL
        BEGIN
          DECLARE @Counter INT, @TestString NVARCHAR(40)

          SET @TestString = '%[' + NCHAR(0) + NCHAR(1) + NCHAR(2) + NCHAR(3) + NCHAR(4) + NCHAR(5) + NCHAR(6) + NCHAR(7) + NCHAR(8) + NCHAR(11) + NCHAR(12) + NCHAR(14) + NCHAR(15) + NCHAR(16) + NCHAR(17) + NCHAR(18) + NCHAR(19) + NCHAR(20) + NCHAR(21) + NCHAR(22) + NCHAR(23) + NCHAR(24) + NCHAR(25) + NCHAR(26) + NCHAR(27) + NCHAR(28) + NCHAR(29) + NCHAR(30) + NCHAR(31) + NCHAR(127)+ ']%'

          SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)

          WHILE @Counter <> 0
          BEGIN
            SELECT @InputString = STUFF(@InputString, @Counter, 1, '')
            SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
          END
        END
        RETURN(@InputString)
    END

    GO

然後, UPDATE SQL查詢(在我的臨時表方法)將如下所示:

UPDATE #myTextsTable 
SET myTextsTable_text = [dbo].RemoveASCIICharactersInRange(#myTextsTable_text)
Go

我個人喜歡的方式將是第一個。





sql-server-2012