Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performing a ALTER TABLE then modifying data in same transaction scope? Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 4:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 76, Visits: 439
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?
Post #1357888
Posted Wednesday, September 12, 2012 6:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, Visits: 140
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?
Post #1357935
Posted Wednesday, September 12, 2012 6:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1357940
Posted Wednesday, September 12, 2012 6:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 76, Visits: 439
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.
Post #1357942
Posted Wednesday, September 12, 2012 6:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 76, Visits: 439
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!
Post #1357944
Posted Wednesday, September 12, 2012 6:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1357945
Posted Wednesday, September 12, 2012 6:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 76, Visits: 439
GSquared,

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

Again, thank you!
Post #1357947
Posted Wednesday, September 12, 2012 7:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1357953
Posted Wednesday, September 12, 2012 7:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 76, Visits: 439
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.
Post #1357958
Posted Wednesday, September 12, 2012 7:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1357973
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse