Can I do transaction for dynamic sql which contains DISABLE change tracking / TRUNCATE / PARTITION SWITCH

Question

I would like to create transaction which will :

1. DISABLE change tracking IF exists.
2. TRUNCATE partition for table.
3. SWITCH PARTITION from different table to main table.
4. ENABLE change tracking IF was disabled before.

Would like to create script with parameters so I will be using dynamic SQL. Idea:

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

My questions:

1. Is operation like ENABLE / DISABLE change tracking , TRUNCATE and PARTITION SWITCH will be working in one transaction ? (want to execute all or nothing)
2. Is dynamic sql is a problem in this case ?

Answer 1

Yes, all DDL at a database level is transactional. This is not the case for server-level DDL, such as CREATE DATABASE or BACKUP RESTORE, but that is not relevant here.

Dynamic SQL does not make a difference. You can verify this with the following code

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

---

More concerning is your use of READ UNCOMMITTED. While it usually doesn't make a difference for DDL operations, and is often recommended for sys schema queries, I wouldn't advise it when doing actual DDL.

Furthermore, your CATCH is unnecessary and counter-productive. Since you have SET XACT_ABORT ON, all errors will automatically ROLLBACK in any case. All that you are doing is swallowing the error and raising it on a low severity, which means it won't get logged, nor caught by client-side code. Just remove the BEGIN CATCH block completely.

Note also that @tableNameWithSchema should really be larger than sysname which is an alias for nvarchar(128), instead it should probably be something like nvarchar(500), which also accounts for extra brackets.