Performing a ALTER TABLE then modifying data in same transaction scope?

  • I'm writing a SQL change script and finding it difficult to get the changes performed in an atomic operation.

    We typically prefer to keep our change operations completely atomic. Here's a skeleton of the test script:

    --using SQLCMD mode

    :ON ERROR EXIT

    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    CREATE TABLE #tmp (

    ID int,

    A bit NOT NULL,

    B bit NOT NULL,

    C bit NOT NULL

    );

    INSERT INTO #tmp(ID, A, B, C)

    SELECT ID, A, B, C

    FROM dbo.tbl;

    ALTER TABLE dbo.tbl

    DROP CONSTRAINT DF_tbl_A,

    DF_tbl_B,

    DF_tbl_C;

    ALTER TABLE dbo.tbl

    ALTER COLUMN A datetime NULL;

    GO

    ALTER TABLE dbo.tbl

    ALTER COLUMN B datetime NULL;

    GO

    ALTER TABLE dbo.tbl

    ALTER COLUMN C datetime NULL;

    GO

    UPDATE t SET

    t.A = CASE WHEN x.A = 1 THEN GETDATE() ELSE NULL END,

    t.B = CASE WHEN x.B = 1 THEN GETDATE() ELSE NULL END,

    t.C = CASE WHEN x.C = 1 THEN GETDATE() ELSE NULL END

    FROM dbo.tbl AS t

    INNER JOIN #tmp AS x

    ON t.ID = x.ID;

    DROP TABLE #tmp;

    ROLLBACK;

    In my tests, the scripts fails on the UPDATE statement with error saying that implicit conversion from datetime to bit is not allowed. This error indicates to me that it hasn't picked up the fact that the table got changed already. I tried breaking up the UPDATE statement into 3 updates, between each ALTER TABLE ALTER COLUMN statement and found that it will work for column A and column B but simply dies on column C.

    AFAIK, the 3 columns are all identical in their properties so I'm not sure why script can't see the changes made to column C (which apparently succeeds) by time it tries to update the column C to correct default values. Only one significant difference was that column C has been a part of the table definition for a good while while column A and B were relatively recent additions.

    I'd rather not have to commit the transaction between the altering of table and modifying the data and would love to see if there's a solution to keep it all within a single transaction. Ideas?

  • What is the full definition for dbo.tbl

    I tried this

    CREATE TABLE dbo.tbl (

    ID int,

    A bit NOT NULL DEFAULT (0),

    B bit NOT NULL DEFAULT (0),

    C bit NOT NULL DEFAULT (0)

    );

    INSERT INTO dbo.tbl

    SELECT 1, 0,0,0 UNION ALL

    SELECT 2, 1,0,1 UNION ALL

    SELECT 3, 0,1,0

    and it all worked fine - so perhaps you have other constraints on your version?

  • Have you checked to make sure there are no triggers being fired by the updates, or anything like that?

    I'd have to see the table definition (create script) before I could suggest anything more detailed.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • The actual table is more complicated than that but that is in fact the gist of the definition of pertinent columns. The ID is an identity, and the actual table has a rowversion, plus few more columns. For this change script, only 3 columns I've mentioned will be affected.

    If I omit column C from the change script, it will succeed. But I need to alter column C atomically with its sibling column A and B and I can't think what else may be causing this to fail. The table as whole has about 10,000+ records, and I've indicated that column C is older than A and B. In the test database (where I was also testing the script), there are only 4 rows that has 1 for column C.

    Maybe I'm totally missing something else that would prevent the UPDATE statement to proceed. If ALTER TABLE ALTER COLUMN statement was failing, then I would know there's something wrong with my process in modifying the table structure but it is succeeding but UPDATE fails. Totally baffles me.

  • GSquared,

    I was going to say that I had disabled a trigger as part of the change script but you made me go and look again and turns out there's one more trigger that I didn't actually get to disable. Looking into it, it's copying data into history table which I didn't update and the reason it was working for column A & B is because that trigger wasn't moving those columns, only the older column C.

    When I saw that implicit conversion error, I got fixated on the fact that I needed to have a GO between the ALTER and UPDATE to ensure that the changes becomes "visible" which I did originally forget in my first draft.

    Mystery solved with egg on my face.

    Thank you!

  • I just set up this test harness:

    CREATE TABLE dbo.tbl (ID INT, A BIT, B BIT, C BIT);

    GO

    ALTER TABLE dbo.tbl ADD CONSTRAINT DF_tbl_A DEFAULT(0) FOR A;

    ALTER TABLE dbo.tbl ADD CONSTRAINT DF_tbl_B DEFAULT(0) FOR B;

    ALTER TABLE dbo.tbl ADD CONSTRAINT DF_tbl_C DEFAULT(0) FOR C;

    GO

    INSERT INTO dbo.tbl

    (ID, A, B, C)

    VALUES (1, 1, 1, 1),

    (2, 0, 0, 0);

    GO

    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    CREATE TABLE #tmp

    (ID INT,

    A BIT NOT NULL,

    B BIT NOT NULL,

    C BIT NOT NULL);

    INSERT INTO #tmp

    (ID, A, B, C)

    SELECT ID,

    A,

    B,

    C

    FROM dbo.tbl;

    ALTER TABLE dbo.tbl

    DROP CONSTRAINT DF_tbl_A,

    DF_tbl_B,

    DF_tbl_C;

    ALTER TABLE dbo.tbl

    ALTER COLUMN A DATETIME NULL;

    GO

    ALTER TABLE dbo.tbl

    ALTER COLUMN B DATETIME NULL;

    GO

    ALTER TABLE dbo.tbl

    ALTER COLUMN C DATETIME NULL;

    GO

    UPDATE t

    SET t.A = CASE WHEN x.A = 1 THEN GETDATE()

    ELSE NULL

    END,

    t.B = CASE WHEN x.B = 1 THEN GETDATE()

    ELSE NULL

    END,

    t.C = CASE WHEN x.C = 1 THEN GETDATE()

    ELSE NULL

    END

    FROM dbo.tbl AS t

    INNER JOIN #tmp AS x

    ON t.ID = x.ID;

    DROP TABLE #tmp;

    COMMIT;

    GO

    DROP TABLE dbo.tbl;

    Runs without errors. That means there's something else going on with your table. Possibly a trigger, either DDL or DML. That would be my first suspicion in a case like this.

    If, for example, there's a logging trigger on column C, but not on A or B, with an audit trail being generated by it, that would totally explain what you're running into.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared,

    Yes you nailed it as I explained in my subsequent discovery in my previous post just before your last reply.

    Again, thank you!

  • Banana-823045 (9/12/2012)


    GSquared,

    Yes you nailed it as I explained in my subsequent discovery in my previous post just before your last reply.

    Again, thank you!

    And of course, we overlapped post-timing.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • It'd have been nice to not have to use GO between certain DDL and DML statements. That would have made things more simpler. Oh, well.

  • Without a GO, the entire thing (alter and subsequent data modifications) gets parsed as a batch and the parsing happens before anything is run. Hence if the alter adds a column, the data modifications can't affect that column in the same batch, because at parse (and optimisation) time, the new column isn't there.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply