Does SQL Server allow (make visible) DDL inside a transaction to the transaction prior to commit?

Question

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?

Answer 1

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.

Answer 2

Is this what you're looking for?

BEGIN TRANSACTION;
  ALTER TABLE foo ADD b varchar;
  EXEC sp_executesql N'UPDATE foo SET b = CAST( a AS varchar )';
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

Answer 3

To the "generally no" statement on Paul White's answer, the following I hope offers a direct answer to the question but also serves to show the systemic limitations of such a process and steers you away from methods that do not lend to easy management and expose risks.

It can be mentioned many times not to make DDL changes the same time you are making DML. Good programming separates these functions to maintain supportability and avoid spaghetti stringing changes.

And as Paul succinctly pointed out, SQL Server works in batches.

Now, For those who doubt this works, it probably does not on your instance but some versions like 2017 it can actually work! Here is the proof:

[TEST CODE - MAY not work on many versions of SQL Server]

USE master
GO
CREATE TABLE foo (a VARCHAR(11) )
GO
BEGIN TRANSACTION;
    INSERT INTO dbo.foo (a)
    VALUES ('entry')
/*****
[2] Check Values
*****/
    SELECT a FROM dbo.foo
/*****
[3] Add Column
*****/
    ALTER TABLE dbo.foo
        ADD b VARCHAR(11)
/*****
[3] Insert value into this new column in the same batch
-- Again, this is just an example. Please do not do this in production
*****/
    IF EXISTS (SELECT * FROM sys.columns WHERE object_ID('foo') = object_id
            AND name = 'b')
        INSERT INTO dbo.foo (b)
        VALUES ('d')
COMMIT TRANSACTION;
/*****
[4] SELECT outside transaction
-- this will fail
*****/
    --IF EXISTS (SELECT * FROM sys.columns WHERE object_ID('foo') = object_id
    --      AND name = 'b')
    --  SELECT b FROM dbo.foo
-- this will work...but a SELECT * ???
IF EXISTS (SELECT * FROM sys.columns WHERE object_ID('foo') = object_id
            AND name = 'b')
        SELECT * FROM dbo.foo

DROP TABLE dbo.foo

[CONCLUSION]

So yes you can perform DDL and DML in the same batch for certain versions or patches of SQL Server as @AndriyM - dbfiddle on SQL 2017 points out, but not all DML is supported and there is no guarantee this will always be the case. If it works, that may be an aberration of your version of SQL Server and this could cause dramatic problems as you patch or migrate to new versions.

• Plus, in general your design should anticipate changes. I understand the concerns of modifying/adding columns can have on a table, but you can design properly around this in batches.

[EXTRA CREDIT]

As for the EXISTS statement, like Paul stated, there are plenty of other means to validate code before moving onto the next step in your code.

• The EXISTS statement can help you create code that works on all versions of SQL Server
• It is a Boolean function that allows complex checks in one statement