Lagrange a minimalist Jekyll theme

Using DDL and DML inside a transaction in SQL Server

Have you ever faced any problem like this? Using DDL and DML inside a transaction is simply not possible. If you run this code:

    BEGIN TRANSACTION
    
    ALTER TABLE dbo.PointTransaction 
        ADD PointBaseId INT NULL CONSTRAINT PointTransaction_PointBaseId REFERENCES dbo.PointBase(Id);
    
    UPDATE t SET t.PointBaseId = r.PointBaseId
    FROM dbo.PointTransaction t
    INNER JOIN dbo.PointRule r ON t.PointRuleId = r.Id;
    
    ALTER TABLE dbo.PointTransaction
        ALTER COLUMN PointBaseId INT NOT NULL;
    
    ROLLBACK TRANSACTION

This will throw an error: Invalid column name 'PointBaseId'.

It seems SQL Server tries to process all statements in one session but fails. A simple workaround is to create another session inside first one by using EXECUTE statement:

    BEGIN TRANSACTION
    
    ALTER TABLE dbo.PointTransaction 
        ADD PointBaseId INT NULL CONSTRAINT PointTransaction_PointBaseId REFERENCES dbo.PointBase(Id);
    
    EXECUTE('UPDATE t SET t.PointBaseId = r.PointBaseId
    FROM dbo.PointTransaction t
    INNER JOIN dbo.PointRule r ON t.PointRuleId = r.Id;');
    
    ALTER TABLE dbo.PointTransaction
        ALTER COLUMN PointBaseId INT NOT NULL;
    
    ROLLBACK TRANSACTION

EXECUTE statement still runs under parent transaction scope, so everything is fine.