CODEWITHSUNDEEP
sql-server-2012truncate
Nim J
Nim J

Reputation: 1033

How to rollback delete command without using transaction

How to rollback delete command without using transaction ? if we can't , then what's the difference between Truncate & delete ?

Upvotes: 8

Views: 42594

Answers (3)

SQL Police
SQL Police

Reputation: 4206

You cannot rollback in this case, but when you are using the Full Recovery Model, then you can turn your database back to the moment before you issued the delete command.

Upvotes: 4

nvogel
nvogel

Reputation: 25534

You can roll back DELETE or TRUNCATE (and most other operations) if and only if they are part of a transaction that's not yet committed. Alternatively you could restore the deleted/truncated data from a backup.

There are several differences between TRUNCATE and DELETE. Most notably TRUNCATE can only empty a table whereas DELETE deletes just the rows you specify. TRUNCATE deallocates and logs data at the page level instead of row level, which typically makes TRUNCATE a more efficient method than DELETE for deleting the entire content of a table.

Upvotes: 2

dan radu
dan radu

Reputation: 2782

You cannot ROLLBACK an operation without a transaction. You could probably use implicit transactions, but you still need to call COMMIT or ROLLBACK explicitly. However, for better control, it's better to wrap the statement(s) in a BEGIN TRANSACTION...COMMIT / ROLLBACK block anyway. This way you'll avoid any confusion and the need to use the IMPLICIT_TRANSACTION setting.

Upvotes: 2

Related Questions