Rolling Back a DDL

Not all the RDBMS are same .  Even though , there have  similar syntax / features  , they differ how they handle transactions  / locking / DDLs . 

In Oracle , DDLs  does implicit commit;  But in SQL Server , the DDLs can be rollback , if necessary. 

Moral of the story - Every RDBMS has own way to doing things.  We can't assume , if a particular feature works in one database  , it will work in other database in the same way. 

Sometimes , it helps us to unlearn some of the stuff you have learnt for one database and go from basics. 

Here is an example . 

In Oracle 

SQL> Create Table emp
  2  ( id int ,
  3    name varchar(50)
  4    ) ;

Table created.

SQL>     alter table emp add sal numeric(9,2) ;

Table altered.

SQL> rollback;

Rollback complete.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(50)
 SAL                                                NUMBER(9,2)


In SQL Server


C:\>sqlcmd -W
1> use test
2> go
Changed database context to 'test'.
1> Create Table emp
2> ( id int ,
3>   name varchar(50)
4>   ) ;
5> go
1> sp_help emp
2> go
Name Owner Type Created_datetime
---- ----- ---- ----------------
emp dbo user table 2016-04-04 12:27:01.283


Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
----------- ---- -------- ------ ---- ----- -------- ------------------ -------------------- ---------
id int no 4 10    0     yes (n/a) (n/a) NULL
name varchar no 50             yes no yes SQL_Latin1_General_CP1_CI_AS

Identity Seed Increment Not For Replication
-------- ---- --------- -------------------
No identity column defined. NULL NULL NULL

RowGuidCol
----------
No rowguidcol column defined.

Data_located_on_filegroup
-------------------------
PRIMARY

The object 'emp' does not have any indexes, or you do not have permissions.

No constraints are defined on object 'emp', or you do not have permissions.

No foreign keys reference table 'emp', or you do not have permissions on referencing tables.
No views with schema binding reference table 'emp'.
1>
2>  begin transaction
3>     alter table emp add sal numeric(9,2) ;
4> go
1> sp_help emp
2> go
Name Owner Type Created_datetime
---- ----- ---- ----------------
emp dbo user table 2016-04-04 12:27:01.283


Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
----------- ---- -------- ------ ---- ----- -------- ------------------ -------------------- ---------
id int no 4 10    0     yes (n/a) (n/a) NULL
name varchar no 50             yes no yes SQL_Latin1_General_CP1_CI_AS
sal numeric no 5 9     2     yes (n/a) (n/a) NULL

Identity Seed Increment Not For Replication
-------- ---- --------- -------------------
No identity column defined. NULL NULL NULL

RowGuidCol
----------
No rowguidcol column defined.

Data_located_on_filegroup
-------------------------
PRIMARY

The object 'emp' does not have any indexes, or you do not have permissions.

No constraints are defined on object 'emp', or you do not have permissions.

No foreign keys reference table 'emp', or you do not have permissions on referencing tables.

No views with schema binding reference table 'emp'.



1> rollback
2> go
1> sp_help emp
2> go
Name Owner Type Created_datetime
---- ----- ---- ----------------
emp dbo user table 2016-04-04 12:27:01.283


Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
----------- ---- -------- ------ ---- ----- -------- ------------------ -------------------- ---------
id int no 4 10    0     yes (n/a) (n/a) NULL
name varchar no 50             yes no yes SQL_Latin1_General_CP1_CI_AS

Identity Seed Increment Not For Replication
-------- ---- --------- -------------------
No identity column defined. NULL NULL NULL

RowGuidCol
----------
No rowguidcol column defined.

Data_located_on_filegroup
-------------------------
PRIMARY

The object 'emp' does not have any indexes, or you do not have permissions.

No constraints are defined on object 'emp', or you do not have permissions.

No foreign keys reference table 'emp', or you do not have permissions on referencing tables.
No views with schema binding reference table 'emp'.