Two and a half ways to do "Safe" delete.

Sometimes you have to manually delete some data in your database and sometimes things go wrong.

Once I had to delete the latest period from production database, but simply did not highlighted WHERE clause and executed statement for deletion of ALL data within the table.
Fortunately for me, table was huge and after 5 minutes of waiting I realized the problem and cancelled the transaction. Since then I'm using ONLY "Safe" deletes.

I put word "safe" in quotes because there is no safe way to delete. If you crazy enough you can skip all safety rules and still make a damage, but if you know what you are doing couple of my hints can save your day and maybe your resume from update.

The simplest way to make your deletion safe is to make sure you are deleting what you want before the action. That means you just do a select with the same WHERE clause parameters as you plan do it in the delete statement.

For example:

USE [AdventureWorks2014]; GO SET NOCOUNT OFF GO  SELECT * -- DELETE FROM dbo.DatabaseLog WHERE DatabaseLogID = 1;

At first, you highlight the select statement and only if it returns what you want to delete you highlight it starting from the word DELETE. That you will be 100% sure that you are using the exactly same parameters in the WHERE clause.

For medium data sets you might use record count to ensure correct deletion. For that reason I specified "SET NOCOUNT OFF" in case it turned ON.

The second "Safe" way to delete is using TRANSACTIONS. In this case you verify your deletion after the fact and COMMIT your transaction if everything is OK and ROLLBACK it if not.

USE [AdventureWorks2014]; GO SET NOCOUNT OFF GO  BEGIN TRAN DELETE FROM dbo.DatabaseLog OUTPUT DELETED.* WHERE DatabaseLogID = 1 -- COMMIT -- ROLLBACK

OUTPUT clause in that command returns you the list of deleted records. Then you make the decision if you like it or not and then execute the following COMMIT or ROLLBACK.

That method is little safer than the first, but still not perfect.
OUTPUT clause has some limitations. It can't run if:
- Table has enabled triggers;
- Table is involved in Foreign Key relationships on either side;
- Table has check constraints or enabled rules.

In these cases you would have to combine first and second method together like this:

USE [AdventureWorks2014]; GO SET NOCOUNT OFF GO  BEGIN TRAN SELECT * FROM dbo.DatabaseLog   WHERE DatabaseLogID = 1; -- DELETE FROM dbo.DatabaseLog WHERE DatabaseLogID = 1 -- COMMIT -- ROLLBACK

There were the demonstrations of two safety tactics. What is "a half"?

The "half" was embedded into these queries and maybe you already noticed that.
Just as a general rule: Put double dashes in front of all irreversible actions  such as DELETE, UPDATE, COMMIT.

That will prevent any unexpected unfortunate event ещ happen in case you run the script in the window without selection.