I found similar queries on stack overflow but was not able to find exact answer.
When I use DDL commands like drop and truncate on SQL Server 2012, I am able to rollback the changes? In theory, I have always read that you can rollback DML commands but not DDL as they are not logged.
My question is, if DDL commands can also be rolled back? Or this some special feature or settings change in my SSMS which is causing it.
What about the other SQL platforms like Oracle and Postgres? Can we also rollback DDL commands on them?
DDL rolls back.
Example:
BEGIN TRANSACTION
CREATE TABLE a ( Id INT NOT NULL )
SELECT * FROM a -- returns an empty row
ROLLBACK TRANSACTION
SELECT * FROM a -- throws an error, object does not exist
I always include my CREATEs, ALTERs, DROPs and TRUNCATEs inside transactions for that exact reason. That way, if there is an error, I don't get some objects, but not others. Test the DDL you have questions about.