significa - La consulta SQL funciona bien en SQL Server 2012, pero no se pudo ejecutar en SQL Server 2008 R2

restaurar base de datos sql server 2012 a 2008

Question

Tengo una tabla llamada MyTextstable (myTextsTable_id INT, myTextsTable_text VARCHAR(MAX)) . Esta tabla tiene alrededor de 4 millones de registros y estoy tratando de eliminar cualquier instancia de los caracteres ASCII en el (los) siguiente (s) rango (s) de la VARCHAR(MAX) myTextsTable_text .

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

Escribí la siguiente consulta SQL, que lleva menos de 10 minutos en SQL Server 2012, pero no se ejecutó en SQL Server 2008 R2 incluso después de dos horas (por lo que detuve la ejecución). Tenga en cuenta que he restaurado la copia de seguridad de una base de datos SQL Server 2008 R2 en SQL Server 2012 (es decir, los datos son exactamente los mismos).

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;

Solo hay 135 registros afectados. Como la única consulta de UPDATE no funcionaba en SQL Server 2008, he intentado el siguiente enfoque con una tabla temporal.

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;

Sin embargo, el resultado es el mismo. Funciona perfectamente bien en SQL Server 2012, pero no en SQL Server 2008 R2. Descubrí que la consulta de UPDATE aún se estaba ejecutando incluso después de dos horas (los registros se guardaron en la tabla temporal ( #myTextsTable ) en unos minutos, lo verifiqué más tarde para asegurarme de qué parte tarda más).

Como las dos formas mencionadas anteriormente no funcionaban, intenté usar esto usando las variables TABLE solo para comprobar si hace alguna diferencia, pero el resultado fue el mismo (es decir, funciona bien en SQL Server 2012 pero no en 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;

¿Alguien podría explicar por qué sucedería esto? ¿Cómo hacer que esta consulta SQL funcione en SQL Server 2008 R2?

Nota: Sé que las manipulaciones de cadena en el servidor / capa de la base de datos no son ideales y se recomienda hacer manipulaciones de cadena en la capa de aplicación y luego guardarla en DB. Pero, estoy tratando de entender por qué esto sería un problema en una versión y por qué no en otra versión.

SQL Server 2012
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
Edición estándar (64 bits) en Windows NT 6.3 (compilación 9600:) (hipervisor)

SQL Server 2008 R2
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
Edición estándar (64 bits) en Windows NT 6.3 (compilación 9600:) (hipervisor)

Answer 1

Probablemente el problema es el anidamiento en el reemplazo y se informa en la ejecución y no en la función compilación check @@ nestlevel. https://technet.microsoft.com/en-us/library/ms190607(v=sql.105).aspx

Answer 2

Este es un problema conocido en SQL Server 2008 con tipos de datos LOB y ciertas intercalaciones.

Es fácil de reproducir

/*Hangs on 2008*/

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

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

Mientras está colgado, está vinculado a la CPU y parece estar en un ciclo infinito a través de estas funciones.

Y la solución también es fácil. O use un tipo de datos no MAX ...

... o una colación binaria

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

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

Answer 3

Para cualquiera que lea esto en el futuro, las siguientes formas funcionaron bien.

Forma 1. Cambiando la COLLATION en la VARCHAR(MAX) en la consulta UPDATE SQL a la BINARY COLLATION como sugirió Martin Smith (consulte la respuesta aceptada).

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

La solución será la siguiente:

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

Modo 2: he creado una SQL function para reemplazar estos caracteres con STUFF lugar de usar la función REPLACE .

Nota: Tenga en cuenta que la función SQL está escrita según mis requisitos específicos. Como tal, solo reemplaza los caracteres en el siguiente rango.

• 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

Entonces, la consulta UPDATE SQL (en mi enfoque de tabla temporal) será algo como a continuación:

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

Mi manera preferida personal sería la primera.