I'm wondering if it is possible to run multiple DDL statements inside a transaction. I'm specially interested on SQL Server, even though answers with other databases (Oracle, PostgreSQL at least) c...
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.
So my suggestion is before creating a table in a pl/sql program always check if there is any table with the same name already exists in the database or not. You can do this check using a Data dictionary views /system tables which store the metadata depending on your database type.
DDL to create a table with the same structure as the query results, into which I can insert the data. However, my Export Wizard does not show any DDL options: Many of the online examples I have found show DDL options in the Export Wizard: This is what I want. Why does SQL Developer not give me DDL options when I try to export the query results?
In Oracle a DDL statement is a transaction in its own right simply because an implicit COMMIT is issued before the statement is executed and again afterwards. TRUNCATE is a DDL command so it doesn't need an explicit commit because calling it executes an implicit commit. From a system design perspective a transaction is a business unit of work.
I think "DDL commands are AutoCommit" concept is in Oracle and i was getting confuse by mixing it with SQL Server. Now can please tell me that is there any diff between DDL and DML related to transaction,commit,rollback...
If you are running SQL Server in read-committed isolation mode (pessimistic), which is the default mode, you are able to use transactional DDL SQL Server supports transactional DDL.
1 DDL statements always performs auto commit,after the execution. If you want it to rollback in case of failure (in server side), then you can set certain flags to indicate the failure and take the appropriate action. eg: if you have created a table table1. and at the same time you are inserting a record in other table.
