Transactional DDL in SQL Server: In Oracle, It Does Not Compute

Question

Transactional DDL in SQL Server: In Oracle, It Does Not Compute

dbakevlar

SSC-Addicted

Points: 473
More actions
July 23, 2025 at 5:00 am

#4625766

Comments posted to this topic are about the item Transactional DDL in SQL Server: In Oracle, It Does Not Compute

Viewing 5 posts - 1 through 5 (of 5 total)

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

Chris Wooding SSCarpal Tunnel Points: 4601 More actions · Answer 1

Interesting article, but TRUNCATE TABLE can be rolled back.

DROP TABLE IF EXISTS dbo.bob;
CREATE TABLE dbo.bob (bob_id INT IDENTITY(1, 1), bob_code VARCHAR(10) NOT NULL);

INSERT dbo.bob
(
    bob_code
)
VALUES
('Bob1')

BEGIN TRAN
SELECT 'Before Truncate', *
FROM dbo.bob;
TRUNCATE TABLE dbo.bob;
SELECT 'After Truncate', *
FROM dbo.bob;
ROLLBACK
SELECT 'After Rollback', *
FROM dbo.bob;

DROP TABLE IF EXISTS dbo.bob;

Connie SSC-Addicted Points: 493 More actions · Answer 2

I look forward to reading what you have to say about Flashback. It's something our Oracle DBA has set up on one of our systems, but I'd love to hear about it from someone who knows SQL Server.

Rod at work SSC-Dedicated Points: 34092 More actions · Answer 3

Thank you for this article. I've never worked with Oracle, so this brought up a lot of things I wasn't aware of, with Oracle.

There's one small subtopic from your article that I'd like to talk about, and that's utilizing DDL transactions and rollbacks, in DevOps. Here at work we are beginning to use DevOps more than I ever thought we would. This is something I am happy to see.

One thing that is odd about where I work is they never involve the DBAs in database design discussions. Database design planning and decisions are always done by developers and PMs. This is a part of a very long practice going back decades, that it is now sacrosanct. To suggest including DBAs in the planning and designing of databases is considered heresy. I cannot bring up the idea of including DBAs before I am cut off, so powerful is the notion that only developers and PMs can plan and design databases.

Anyway, on one of the projects I am involved in helping with DevOps, one of the developers has written some SQL scripts to make modifications to some of the table definitions. We're putting off including those scripts, because we just don't know how to include them. So where, here on SSC, should I go to learn how to include SQL DDL scripts?

Kindest Regards, Rod Connect with me on LinkedIn.

Mr. Brian Gale SSC-Insane Points: 24968 More actions · Answer 4

I'd also like to point out that you should be careful with transactions that change large amounts of data OR are long running - if a query runs for 5 minutes (for example), it often takes 5 minutes or more to roll it back.

I would also be very hesitant about rolling back index creation or removal. That feels like a recipe for a long running transaction (forgotten commit) or long running rollback (as it recreates the index). Just because you CAN, doesn't always mean you SHOULD.

I am also a little curious about the flashback feature and the recycle bin, but both of those sound like they would be large hits to disk I/O and storage. So not sure if it is a feature I'm actually "missing". Especially since any tables I'd want to use that on I already have audit tables created and triggers so I can capture changes to those tables myself so I can already see previous values. Maybe not as nice of a query as a point in time snapshot would be with flashback, but I can't imagine that storing the historical values of a table for near real-time lookup would be a small data set. Especially for quickly changing data...

@rod at work - My opinion is that DBA's should be in on database design decisions. They know all sorts of little tricks and some gotchas. At least good DBA's do. And having the database designed correctly at the start is much nicer than trying to fix it afterwards. For example, if the database is poorly normalized, that can hurt overall performance. Doesn't need to be fully 3rd normal form as that may not be the right solution for your situation, but having all of the data in one big fat table isn't nice to work with either. There needs to be a nice middle ground and the DBA can help determine what that middle ground should be.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.