Friday, 3 July 2015

Safer way to delete a record in SQL Server

Use DELETE TOP


The problem with a typical SQL Delete transaction is that you only know how many records you deleted until after the transaction is complete:

 

(1023 row(s) affected)

 

There are of course ways in which you can “Commit” changes to your database after a preview of data, but such organization defeats the purpose of quickly modifying the database and is certainly excessive if the intention to make a one time “Delete”


To avoid disasters with DELETE statements, try using the “TOP” clause to restrict potential hazards:


DELETE TOP (2)
FROM            TRANSACTIONS
WHERE        (Transaction_Alias = 'S42CN35')

 

I recommend using it to restrict the results to one more than the intended deletion of records. After executing the statement, if you see that the records affected matches the clause – you have a problem with the criteria in your statement – and you can then thank me for not losing a whole table worth of data in the process.


Regards,

Twitter

Page Hits