Início / dba / Perguntas / 330677
Accepted
adam.g
Asked: 2023-08-28 23:28:53 +0800 CST

Posso fazer transações para sql dinâmico que contém DESABILITAR rastreamento de alterações / TRUNCATE / PARTITION SWITCH

  • 772

Eu gostaria de criar uma transação que irá:

  1. DESATIVAR o rastreamento de alterações SE existir.
  2. Partição TRUNCATE para tabela.
  3. MUDE A PARTIÇÃO de uma tabela diferente para a tabela principal.
  4. ATIVAR o rastreamento de alterações SE foi desativado antes.

Gostaria de criar um script com parâmetros, então usarei SQL dinâmico. Ideia:

/* OPS parameters */
DECLARE @schemaName sysname = 'dbo';
DECLARE @tableName sysname = 'TABLE';
DECLARE @partition INT = 90;  

/* DEV parameters */
DECLARE @tableNameSRP sysname = CONCAT(@tableName, '_SRP');
DECLARE @tableNameWithSchema sysname = CONCAT(QUOTENAME(@schemaName), '.', QUOTENAME(@tableName));
DECLARE @tableNameWithSchemaSRP sysname = CONCAT(QUOTENAME(@schemaName), '.', QUOTENAME(@tableNameSRP));
DECLARE @isCtReEnabled BIT = 0;
DECLARE @isDebug BIT = 1;


SET TRAN ISOLATION LEVEL READ UNCOMMITTED;
SET XACT_ABORT ON;
BEGIN TRAN;
BEGIN TRY
    IF EXISTS (
        SELECT
            1
        FROM sys.change_tracking_tables
        WHERE object_id = OBJECT_ID(@tableNameWithSchema)
    )
    BEGIN

        SET @statement = N'ALTER TABLE ' + @tableNameWithSchema + N' DISABLE CHANGE_TRACKING;';
        IF (@isDebug = 0)
        BEGIN
            EXEC sp_executesql @stmt = @statement;
        END;
        IF (@isDebug = 1)
        BEGIN
            RAISERROR('[INFO] SQL: %s', 0, 1, @statement) WITH NOWAIT;
        END;
        SET @isCtReEnabled = 1;
    END;

    SET @statement
        = N'TRUNCATE TABLE ' + @tableNameWithSchema + N' WITH (PARTITIONS (' + CAST(@partition AS NVARCHAR(5)) + N')) 
ALTER TABLE '        + @tableNameWithSchemaSRP + N' SWITCH PARTITION ' + CAST(@partition AS NVARCHAR(5)) + N' TO ' + @tableNameWithSchema + N' PARTITION '
          + CAST(@partition AS NVARCHAR(5));
    IF (@isDebug = 0)
    BEGIN
        EXEC sp_executesql @stmt = @statement;
    END;
    IF (@isDebug = 1)
    BEGIN
        RAISERROR('[INFO] SQL: %s', 0, 1, @statement) WITH NOWAIT;
    END;

    IF (@isCtReEnabled = 1)
    BEGIN
        SET @statement = N'ALTER TABLE ' + @tableNameWithSchema + N' ENABLE CHANGE_TRACKING;';
        IF (@isDebug = 0)
        BEGIN
            EXEC sp_executesql @stmt = @statement;
        END;
        IF (@isDebug = 1)
        BEGIN
            RAISERROR('[INFO] SQL: %s', 0, 1, @statement) WITH NOWAIT;
        END;
    END;
    COMMIT;

END TRY
BEGIN CATCH
    SET @errorMessage = ERROR_MESSAGE();
    RAISERROR('ERROR MESSAGE: %s', 0, 1, @errorMessage) WITH NOWAIT;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

Minhas perguntas:

  1. Operações como ENABLE/DISABLE change tracking, TRUNCATE e PARTITION SWITCH funcionarão em uma transação? (quer executar tudo ou nada)
  2. O SQL dinâmico é um problema neste caso?
t-sql

1 respostas

  1. Best Answer

    Sim, todo DDL no nível do banco de dados é transacional. Este não é o caso de DDL em nível de servidor, como CREATE DATABASEou BACKUP RESTORE, mas isso não é relevante aqui.

    SQL dinâmico não faz diferença. Você pode verificar isso com o seguinte código

    SELECT @@TRANCOUNT;
BEGIN TRAN;
SELECT @@TRANCOUNT;
EXEC sp_executesql N'SELECT @@TRANCOUNT;';
COMMIT;

    Mais preocupante é o uso de READ UNCOMMITTED. Embora geralmente não faça diferença para operações DDL e seja frequentemente recomendado para sysconsultas de esquema, eu não recomendaria isso ao fazer DDL real.

    Além disso, o seu CATCHé desnecessário e contraproducente. Desde que você tenha SET XACT_ABORT ON, todos os erros serão automaticamente ROLLBACKem qualquer caso. Tudo o que você está fazendo é engolir o erro e aumentá-lo para uma gravidade baixa, o que significa que ele não será registrado nem capturado pelo código do lado do cliente. Basta remover o BEGIN CATCHbloco completamente.

    Observe também que @tableNameWithSchemadeve ser realmente maior do que sysnamewhich é um alias para nvarchar(128); em vez disso, provavelmente deveria ser algo como nvarchar(500), o que também leva em conta colchetes extras.

    • 1

relate perguntas