Implicit COMMIT After DDL Statement - Oracle to SQL Server Migration

Oracle issues an implicit COMMIT before and after any data definition language (DDL) statement. This does not happen in SQL Server.

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

Last Revision: Oracle 11g R2

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'.

Last Revision: Microsoft SQL Server 2008 R2

• Oracle to SQL Server Migration

Oracle 11g R2 SQL Language Reference

• COMMIT Statement

Dmitry Tolpeko, dmtolpeko@sqlines.com - June 2012