9

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?

3 Answers 3

17

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.

0
13

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;
2

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: enter image description here

[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
7
  • No, you can't insert into the new column if you are doing this in the same batch where you are creating the column. More generally, you can't statically reference the new column(s) in the same batch after you just created it/them. The IF EXISTS trick doesn't work in this case. Either invoke the DML dynamically or do it in another batch.
    – Andriy M
    Commented May 13, 2019 at 13:28
  • @AndriyM sorry, incorrectly made a statement about dbfiddle. But did you try this on your Instance? It works on 2017 SP1. I will upload a gif, but did you test this on your systems?
    – clifton_h
    Commented May 13, 2019 at 13:49
  • i.imgur.com/fhAC7lB.png You can actually tell that it won't compile based on the wavy line under b in the insert statement. I'm using SQL Server 2014
    – Andriy M
    Commented May 13, 2019 at 13:55
  • @AndriyM interesting. I had seen this affect work before, and it appears to work on some versions of SQL Server, like the SQL Server 2017 I mentioned.
    – clifton_h
    Commented May 13, 2019 at 14:15
  • @AndriyM see new edit to post
    – clifton_h
    Commented May 13, 2019 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.