Deleting Data

The DELETE reservered word is part of the Data Manipulation Language (DML) defined by Codd and is used to remove existing data in one table in the database. Today, I would like to review the different ways to remove data using the AdventureWorks Sample database supplied by Microsoft. Each example has a SELECT statement to identify the number of rows affected before executing the DELETE.

1 – One way to purge data from a table is to use the DELETE statement without a where clause. Only use this syntax when the table size is small since every deleted row is recorded in the TRANSACTION LOG when the Recovery Model is FULL. A better way to clear all data from a table is the TRUNCATE TABLE statement which does minimal logging.


2 – The simplest form of the DELETE statement removes a single row from the table using one WHERE clause expression. The example below removes the first non-employee from the person table in the staging schema.


3 – A more complex form of the DELETE statement removes several rows from the table using several WHERE clause expressions. The following example finds all people who are employees or vendors. All other persons are removed from the staging table.


4 – The most complex form of the DELETE statement remove several rows using several join tables and where clauses to get the resulting purge set. Transaction SQL supports an non-standard FROM key word that makes the query simpler. However, this form is not ANSI compitable. If you are writing code that might have to be executed on another database platform in the future, do not use this form. The ANSI Standard syntax can be used with a NOT IN sub-query to achieve the same results.

Please see both the ANSI and TSQL versions of the UPDATE statement below.


5 – It is very important to consider what happens to the TRANSACTION LOG with DML statements. I am going to introduce an advance technique which changes the recovery model to simple and uses implicit transactions to execute check point to clear the transaction log after a batch size of data is deleted. The selected bactch size for this example is 100 rows but can be set to any number.


In summary, working with databases is vital in the information age. That said, removing data is a daily task that developers and administrators perform. Knowing the different variantions of the DELETE statement will help you select the correct syntax.

Related posts

One Thought to “Deleting Data”

  1. Hello. impressive job. I did not imagine this. This is a remarkable story. Thanks!

Leave a Comment