How to rollback delete command without using transaction

Question

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

Solution 1

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.

Solution 2

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.

Solution 3

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.

Comments

[SQL Police]

If he is using a full recovery model, then he could turn back the database to the moment before the delete command was issued.