Implicit COMMIT After DDL Statement

Oracle issues an implicit COMMIT before and after any data definition language (DDL) statement.

Let's create a table in Oracle and insert a row:

Oracle:

  -- Create a table and insert a row
  CREATE TABLE states
  (
      abbr CHAR(2),
      name VARCHAR2(90)
  );
 
  -- Transaction will be in progress after this insert
  INSERT  INTO states VALUES ('CA', 'California');

Now let's create another table and perform ROLLBACK operation:

Oracle:

  -- Create another table table and insert a row
  CREATE TABLE cities
  (
      name VARCHAR2(90),
      state CHAR(2)
  );
 
  INSERT INTO cities VALUES ('San Francisco', 'CA');
 
  ROLLBACK;

You can see that even after ROLLBACK, the table states and row in this table exist because CREATE TABLE cities statement committed the transaction.

Table cities also exists, but the inserted row was rolled back:

Oracle:

  -- Table states exists and contains 1 row
  SELECT COUNT(*) FROM states;
  -- Result: 1
 
  -- Table cities also exists, but the inserted row was rolled back
  SELECT COUNT(*) FROM cities;
  -- Result: 0

By default, SQL Server works in AUTOCOMMIT mode so COMMIT is issued after each statement, but when a explicit transaction is started, SQL Server does not issue a COMMIT after a DDL statement:

Let's start a transaction, create a table in SQL Server and insert a row:

SQL Server:

  -- Start a transaction explicitly
  BEGIN TRANSACTION;
 
  -- Create a table and insert a row
  CREATE TABLE states
  (
      abbr CHAR(2),
      name VARCHAR(90)
  );
 
  INSERT  INTO states VALUES ('CA', 'California');
  -- 1 row(s) affected

Now let's create another table, insert a row and perform ROLLBACK operation:

SQL Server:

  -- Create another table table and insert a row
  CREATE TABLE cities
  (
      name VARCHAR(90),
      state CHAR(2)
  );
 
  INSERT INTO cities VALUES ('San Francisco', 'CA');
  -- 1 row(s) affected
 
  ROLLBACK;

You can see that after ROLLBACK, none of the tables exists:

SQL Server:

  SELECT COUNT(*) FROM states;
  -- Msg 208, Level 16, State 1, Line 1
  -- Invalid object name 'states'.
 
  SELECT COUNT(*) FROM cities;
  -- Msg 208, Level 16, State 1, Line 1
  -- Invalid object name 'cities'.