Menu
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.
347
Like the CREATE statement, the DROP statement automatically executes a COMMIT before and after dropping the table. This makes permanent all changes to the database since the last COMMIT or ROLLBACK.
It completely removes the table structure and associated indexes, statistics, permissions, triggers and constraints. You might have SQL Views and Stored procedures referencing to the SQL table. SQL Server does not remove these stored procedures and views.
After wasting a lot of time on non-solutions to the problem, I found the real solution is to commit immediately after dropping the index. When commit is issued, the table is unlocked and the insert transactions complete successfully.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
