主页 / dba / 问题 / 330677
Accepted
adam.g
Asked: 2023-08-28 23:28:53 +0800 CST

我可以为包含禁用更改跟踪/截断/分区开关的动态 SQL 执行事务吗

  • 772

我想创建交易，它将：

  1. 如果存在则禁用更改跟踪。
  2. 表的 TRUNCATE 分区。
  3. 将分区从不同的表切换到主表。
  4. ENABLE 更改跟踪 IF 之前被禁用。

想要创建带有参数的脚本，所以我将使用动态 SQL。主意：

/* 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;

我的问题：

  1. 启用/禁用更改跟踪、截断和分区切换等操作是否可以在一个事务中进行？（想要执行全部或不执行）
  2. 在这种情况下动态sql有问题吗？
t-sql

1 个回答

  1. Best Answer

    是的，数据库级别的所有 DDL 都是事务性的。CREATE DATABASE对于服务器级 DDL（例如或 ）来说，情况并非如此BACKUP RESTORE，但这与这里无关。

    动态 SQL 没有什么区别。您可以使用以下代码验证这一点

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

    更令人担忧的是您对READ UNCOMMITTED. 虽然它通常对 DDL 操作没有影响，并且通常建议用于sys模式查询，但在执行实际 DDL 时我不建议这样做。

    此外，你的做法CATCH是不必要的，而且会适得其反。既然你已经有了SET XACT_ABORT ON，在任何情况下所有错误都会自动发生ROLLBACK。您所做的就是吞掉错误并将其提升为低严重性，这意味着它不会被记录，也不会被客户端代码捕获。只需完全移除该BEGIN CATCH块即可。

    另请注意，@tableNameWithSchema确实应该大于 的sysname别名nvarchar(128)，相反，它可能应该类似于nvarchar(500)，这也考虑了额外的括号。

    • 1

相关问题