Tags


My article TRUNCATE command can be Rolled Back?? created a lot discussion in many forums. Many agreed with my logic and many denied to accept this fact that DDL command can be rolled back. But some of the discussions were quite interesting with all the experts bringing their knowledge and expertise together to discuss about this.

Today I am going to extend it further and I am going to show that in SQL Server we can ROLL BACK each and every DDL commands. Interesting isn’t it??

Lets begin with CREATE statement.

— Changing the Database to Tempdb

USE tempdb

— Explicitly beginning the Transaction

BEGIN TRAN

— Creating the table

       CREATE TABLE [dbo].[My_First_Table]

              (

                     Name_id INT IDENTITY(1,1),

                     First_Name VARCHAR(20)

              )

— Inserting the records into the table created above

               INSERT INTO My_First_Table

                  SELECT (‘Subhro Saha’)

                  UNION ALL

                  SELECT (‘Smith‘)

–Lets check the existence of our table in the database

SELECT * FROM SYSOBJECTS WHERE [name]=’My_First_Table

— Selecting the Inserted Records

SELECT * FROM My_First_Table

 

— So Data is entered into the created table. Cool!!. Now the real FUN part begins.

— Lets Rollback the Transaction and see the effect.

ROLLBACK

— Lets again check the existence of the previously created table.

SELECT * FROM SYSOBJECTS WHERE [name]=’My_First_Table


 — Hhmmm… So it is not returning any rows which shows that the table does not exist any more. Did the ROLLBACK really work??

— Lets try to select the Inserted records from the table

SELECT * FROM My_First_Table


 Oh my god!! Is it really happening then, so in SQL Server we can ROLLBACK the CREATE command. What do you say about this??

Ok, now moving on to our next example. Now I will take DELETE command.

— Changing the Database to Tempdb

USE tempdb

— Creating the table

       CREATE TABLE [dbo].[My_First_Table]

              (

                     Name_id INT IDENTITY(1,1),

                     First_Name VARCHAR(20)

              )

— Inserting the records into the table created above

               INSERT INTO My_First_Table

                  SELECT (‘Subhro Saha’)

                  UNION ALL

                  SELECT (‘Smith’)

–Lets check the existence of our table in the database

SELECT * FROM SYSOBJECTS WHERE [name]=’My_First_Table

— Selecting the Inserted Records

SELECT * FROM My_First_Table

— So everything is fine till here. Now lets start the TRANSACTION part.

— Explicitly begining the Transaction

BEGIN TRAN

— Dropping the created table

       DROP TABLE My_First_Table      

–Lets again check the existence of our table in the database

SELECT * FROM SYSOBJECTS WHERE [name]=’My_First_Table


 — So DROP command worked and now the table does not exist anymore. Lets try to select the records from the table.

SELECT * FROM My_First_Table


— As expected it is throwing an error saying “invalid object name.” Till now everything is going as planned. Now let’s do some Magic.

— Rollback the Transaction

ROLLBACK

–Now check the existence of the table in the database

SELECT * FROM SYSOBJECTS WHERE [name]=’My_First_Table

–Hey..watch the magic. The DROPPED table is BACK. Amazing. Lets see if the inserted records still exist or not??

SELECT * FROM My_First_Table

So basically what happened we dropped the table first inside the Transaction and then ROLLED back the transaction, and we got the Table back with all the inserted records. Truly fascinating.

I can say that in Oracle we cannot rollback the DDL commands but in SQL server it is possible.

I do not know whether this is a good feature or a bad feature. It is for you to decide and please let me know about your decision with valid reasoning!!

Till then, keep coding and keep learning..

Cheers,

Subhro Saha

Advertisements