SQL Server: Does 'DROP TABLE' inside transaction causes an implicit commit?

sql transactions drop-table
Mauro Bilotti picture Mauro Bilotti · Jul 18, 2014 · Viewed 17.4k times · Source

My question is kind of easy but i'm still doubting after I created this transaction. If I execute the following code:

BEGIN TRANSACTION
      DROP TABLE Table_Name

Can I perform a ROLLBACK TRANSACTION that recovers the dropped table? I'm asking because I don't know what happens in the 'Object Explorer' and I didn't found any question of this topic, so I think that it could be a useful issue.

Answer

Sean Lange picture Sean Lange · Jul 18, 2014

This is incredibly easy to test.

create table TransactionTest
(
    ID int identity primary key clustered,
    SomeValue varchar(20)
)

insert TransactionTest
select 'Here is a row'

begin transaction
    drop table TransactionTest
rollback transaction

select * from TransactionTest

Related questions

Correct use of transactions in SQL Server

I have 2 commands and need both of them executed correctly or none of them executed. So I think I need a transaction, but I don't know how to use it correctly. What's the problem with the following script? BEGIN TRANSACTION […

Read More
SQL Server - transactions roll back on error?

We have client app that is running some SQL on a SQL Server 2005 such as the following: BEGIN TRAN; INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); COMMIT TRAN; It …

Read More
Best way to work with transactions in MS SQL Server Management Studio

Let's say I have an SQL statement that's syntactically and semantically correct so it executes. In Management Studio (or any other query tool) how can I test SQL statements, and if I notice that they broke something, rollback (in a …

Read More