SQL Server – Visibility of DDL Inside a Transaction Before Commit

ddlsql serversql-server-2017transaction

In PostgreSQL I can create a table with some test data, and then in a transaction migrate it to a new column of a different type resulting in one table-rewrite upon COMMIT,

CREATE TABLE foo ( a int );
INSERT INTO foo VALUES (1),(2),(3);

Followed by,

BEGIN;
  ALTER TABLE foo ADD COLUMN b varchar;
  UPDATE foo SET b = CAST(a AS varchar);
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

However, that same thing in Microsoft's SQL Server seems to generate an error. Compare this working db fiddle, where the ADD (column) command is outside of the transaction,

-- txn1
BEGIN TRANSACTION;
  ALTER TABLE foo ADD b varchar;
COMMIT;

-- txn2
BEGIN TRANSACTION;
  UPDATE foo SET b = CAST( a AS varchar );
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

to this db fiddle which doesn't work,

-- txn1
BEGIN TRANSACTION;
  ALTER TABLE foo ADD b varchar;
  UPDATE foo SET b = CAST( a AS varchar );
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

But instead errors

Msg 207 Level 16 State 1 Line 2
Invalid column name 'b'.

Is there anyway to make this transaction visible, with regard to DDL, behave like PostgreSQL?

Best Answer

Generally speaking, no. SQL Server compiles the whole batch at the current scope before execution so referenced entities have to exist (statement-level recompilations may also happen later). The main exception is Deferred Name Resolution but that applies to tables, not columns:

Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.

Common workarounds involve dynamic code (as in Joe's answer), or separating the DML and DDL into separate batches.

For this specific case you could also write:

BEGIN TRANSACTION;

    ALTER TABLE dbo.foo
        ALTER COLUMN a varchar(11) NOT NULL
        WITH (ONLINE = ON);

    EXECUTE sys.sp_rename
        @objname = N'dbo.foo.a',
        @newname = N'b',
        @objtype = 'COLUMN';

COMMIT TRANSACTION;

You still will not be able to access the renamed column b in the same batch and scope, but it does get the job done.

With regard to SQL Server, there is a school of thought that says mixing DDL and DML in a transaction is not a great idea. There have been bugs in the past where doing this has resulted in incorrect logging, and an unrecoverable database. Nevertheless, people do it, especially with temporary tables. It can result in some quite hard-to-follow code.

Related Question