DELETE/UPDATE techniques (T-SQL , DELETE/UPDATE)

Data manipulation can be really sensitive in terms of data modifications and loosing data. Data Manipulation Language (DML) is consisted of 3 basic operations in SQL Server: INSERT, UPDATE and DELETE.

Like any other data manipulation there is always risk of data loss and human error due to data manipulation operations in SQL server. I've seen so many times that DBA's updated/deleted certain columns on the entire table instead of on some criteria defined in DML query. Sometimes WHERE clause can be forgotten so easily and in these situations can cause catastrophic damage on our data and in some cases the original data cannot be restored in any way. Things can go even worse if there is no backup or existing backup plans don't include the most recent data that we have before executing fatal DELETE or UPDATE commands in SQL Server. INSERT command in the most cases is not so affected like the other 2 commands, but if there is no date stamp in the table or any other validation constraints this command can be fatal as well.

For broader considerations it would be useful to discover all 3 DML commands together with OUTPUT clause with DELETED and INSERTED options.


DATA MANIPULATION TECHNIQUE

I will demonstrate very simple method that I prefer to use in order to ensure accurate data modifications with no losing any data. The general rule can be "Always think twice!" but there is always the risk of human's error and it's good to have some good validation tools for that. I developed this technique from past experiences with data manipulation and here are 3 required and 2:

1. Consider what exactly needs to be deleted
2. Create query with the same criteria like that one which will be used for deletion/update. Always check for existence of WHERE clause 2 times and check WHERE criteria as well! Missing WHERE in query should be always potential treat to data loss!
3. Optional: save results of the query into staging or temporary table for later use
4. Delete/Update record taking the same query from step 2 and adding OUTPUT clause to it. Think twice before pressing "F5", Ctrl+E or "Execute" button!
5. Optional: Output deleted/updated records direct into staging or temporary table

In this way data affected prior the query execution can always be restored from temporary or staging tables and even if they don't exist you can easily grab data from OUTPUT result pane, copy them into excel and import into SQL in order to restore data.

Additional advice would be to use ID and date modification/date stamp columns whenever is that possible. That can be helpful in restoring data. Of course, a good DB backup technique is always advantage.

DML EXAMPLE

Here is the example that can be applied to all of 3 commands using OUTPUT clause.

USE AdventureWorks2012

GO

/*CREATING SAMPLE DATA*/ SELECT * FROM Production.Product

ORDER BY ProductID

/*
Let's take 10 rows from Product table
*/

IF (OBJECT_ID('Production.TempProducts')) IS NOT NULL

   DROP TABLE Production.TempProducts

SELECT

TOP 10

  ProductID,

  Name,

  ProductNumber

INTO Production.TempProducts

FROM Production.Product

ORDER BY ProductID

/*Checking our newly created table*/

SELECT * FROM Production.TempProducts

Results:

(10 row(s) affected)

ProductID Name ProductNumber
----------- -------------------------------------------------- -------------------------
1 Adjustable Race AR-5381
2 Bearing Ball BA-8327
3 BB Ball Bearing BE-2349
4 Headset Ball Bearings BE-2908
316 Blade BL-2036
317 LL Crankarm CA-5965
318 ML Crankarm CA-6738
319 HL Crankarm CA-7457
320 Chainring Bolts CB-2903
321 Chainring Nut CN-6137

(10 row(s) affected)

　
/*STEP #1: We want to delete all products with product numbers starting with "A" and "B"*/

/*STEP #2: Query for getting all products with product numbers starting with "A" and "B"*/
SELECT *

FROM Production.TempProducts

WHERE ProductNumber LIKE 'A%' OR ProductNumber LIKE 'B%'

Results:

ProductID Name ProductNumber
----------- -------------------------------------------------- -------------------------
1 Adjustable Race AR-5381
2 Bearing Ball BA-8327
3 BB Ball Bearing BE-2349
4 Headset Ball Bearings BE-2908
316 Blade BL-2036

(5 row(s) affected) 

/*STEP #3: Store data into staging table*/
IF (OBJECT_ID('Production.TempDeletedProducts')) IS NOT NULL

  DROP TABLE Production.TempDeletedProducts

SELECT *

INTO Production.TempDeletedProducts

FROM Production.TempProducts

WHERE ProductNumber LIKE 'A%' OR ProductNumber LIKE 'B%'

/*4. Delete/Update record taking the same query from step 2 and adding OUTPUT clause to it.*/
DELETE FROM Production.TempProducts

OUTPUT DELETED.*

WHERE ProductNumber LIKE 'A%' OR ProductNumber LIKE 'B%'

Results:

ProductID Name ProductNumber
----------- -------------------------------------------------- -------------------------
1 Adjustable Race AR-5381
2 Bearing Ball BA-8327
3 BB Ball Bearing BE-2349
4 Headset Ball Bearings BE-2908
316 Blade BL-2036

(5 row(s) affected)

/*STEP #5: Output deleted/updated records direct into staging or temporary table*/

IF (OBJECT_ID('tempdb..#tempDeletedProducts')) IS NOT NULL

   DROP TABLE #tempDeletedProducts

/*Creating temporary table*/
CREATE TABLE #tempDeletedProducts

(  ProductID int,

  Name nvarchar(50),

  ProductNumber nvarchar(50)

)

/*DELETING THE REST OF RECORDS IN STAGING DATABASE - this should be always checked because there is WHERE is missing, but in this case that's exactly that we want to delete all existing records from the table  */ 

DELETE FROM Production.TempProducts

OUTPUT DELETED.ProductID,

  DELETED.Name,

  DELETED.ProductNumber

INTO #tempDeletedProducts

/*Our deleted records are now stored in the table and they can be restored easily*/

SELECT * FROM #tempDeletedProducts

Results:

(5 row(s) affected)
ProductID Name ProductNumber
----------- -------------------------------------------------- --------------------------------------------------
317 LL Crankarm CA-5965
318 ML Crankarm CA-6738
319 HL Crankarm CA-7457
320 Chainring Bolts CB-2903
321 Chainring Nut CN-6137

(5 row(s) affected)

OTHER USEFUL DML TECHNIQUES

Another automated techniques for tracking deletion could be:
1. Using triggers
2. Using new feature in SQL 2012: Change Data Capture



Mike C.