SQL DELETE Statement Examples and Best Practices

Problem

In this SQL tutorial, I demonstrate the use of the SQL DELETE statement to remove 1 row, a group of rows, or all the existing records from a Microsoft SQL Server table.

Before diving in too deep, understand that a SQL DELETE operation is dangerous. Once you run the DELETE, there is no recycle bin like in the Windows operating system where you can retrieve deleted records! To retrieve records, you must restore a database from a backup that occurred before the DELETE. To avoid having to restore the database, I included a best practice approach using the following basic transaction control statements in conjunction with the DELETE statement: BEGIN TRANSACTION, COMMIT, and ROLLBACK. These are more advanced topics, but putting these into practice as you learn to use DELETE can save your day!

Solution

SQL DELETE Statement

The syntax for a basic DELETE query includes the DELETE keyword and the table name with the schema name, which is sometimes optional depending on the database design. Also, it is important to consider including a WHERE clause. The WHERE clause is important to control the rows being deleted. Note: if a WHERE clause is not included, all rows of the entire table will be purged! Microsoft sample database, Adventureworks, will be used for this demonstration.

Delete Basic Syntax:

DELETE schema.TableName
WHERE Col1 = 1; -- WHERE Condition

First – Setup the Delete Command Examples with Test Tables

First, I will set up a test table called dbo.MySalesPerson to use for my delete examples, as I do not want to mess up my sample database’s integrity. Data will be loaded from existing tables to this table so we can issue DELETE statements throughout this demo.

Run the code below in the AdventureWorks database to set up the sample table for this demo.

USE [AdventureWorks]
GO
--A) CREATE TABLE statement to create our test table
CREATE TABLE [dbo].[MySalesPerson](
   [BusinessEntityID] [int] NOT NULL,
   [TerritoryID] [int] NULL,
   [SalesQuota] [money] NULL,
   [Bonus] [money]  NULL,
   [CommissionPct] [smallmoney]  NULL,
   [SalesYTD] [money]  NULL,
   [SalesLastYear] [money]  NULL,
   [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
   [ModifiedDate] [datetime] NOT NULL,
);
GO
 
--B) Load Test Data from [Sales].[SalesPerson]
INSERT [dbo].[MySalesPerson]
SELECT *
FROM [Sales].[SalesPerson];
GO
 
--C) Insert Additional Rows for Non- Sales Persons.
INSERT [dbo].[MySalesPerson]
SELECT e.BusinessEntityID 
      ,1 as [TerritoryID]
      ,NULL as [SalesQuota]
      ,0 as [Bonus]
      ,1 as [CommissionPct]
      ,0 as [SalesYTD]
      ,0 as [SalesLastYear]
      ,e.[rowguid]
      ,Getdate() as [ModifiedDate]
FROM [HumanResources].[Employee] e
WHERE JobTitle = 'Database Administrator';
 
--D) Explore the Test Data.
SELECT * from [dbo].[MySalesPerson];
GO

Example 1 – Basic DELETE SQL Statement

The following query shows a basic DELETE statement with a WHERE clause to control what records should be deleted i.e., only one record. A check query can show the record with BusinessEntityID = 271 no longer exists.

USE [AdventureWorks]
GO
 
--1) Basic Delete Statement
DELETE [dbo].[MySalesPerson]
WHERE BusinessEntityID = 271;
 
--Check Query
SELECT * FROM [dbo].[MySalesPerson]
WHERE BusinessEntityID = 271;
GO

Example 2 – DELETE Command with Join Criteria

The following query uses a Join Query to delete rows that are in another table (WHERE JobTitle = ‘Pacific Sales Manager’).

--2) Delete With a Query
DELETE [dbo].[MySalesPerson]
FROM [dbo].[MySalesPerson] m
   INNER JOIN [HumanResources].[Employee] e 
      ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Pacific Sales Manager';
 
--Check Query
SELECT JobTitle, m.*
FROM [dbo].[MySalesPerson] m
   INNER JOIN [HumanResources].[Employee] e 
      ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Pacific Sales Manager';
GO

Example 3 – SQL DELETE All Rows from a Table

For this example, I will demonstrate two ways to clear all the data from a table. The first option will use a DELETE statement without a WHERE clause. The second option uses TRUNCATE, which requires ALTER TABLE permissions or higher. Also, I am including a transaction control statement to show the results of DELETE and TRUNCATE statements to undo the transaction, so I do not need to reset and reload the demo data. Please see the link at the end of this article to learn more about Transaction Control Statements.

It is important to mention that once a “BEGIN TRAN” statement is issued, you must run a COMMIT or ROLLBACK statement. If not, a perpetual opened transaction will be left, potentially causing contention or blocking in the database. Note: if you run either DELETE or TRUNCATE without the “BEGIN TRAN”, you will need to rerun the “Load Test Data” previously provided in this article, because the data will be gone.

--3a) Option a: DELETE ALL Rows
BEGIN TRAN;
DELETE [dbo].[MySalesPerson];
 
--Check Query
SELECT * FROM [dbo].[MySalesPerson];
 
--Now Rollback as I do not want to lose my data.
ROLLBACK;
 
--Show that no records where removed!
SELECT * FROM [dbo].[MySalesPerson];
 
----------------------------------------------
--3b) Option b:  TRUNCATE Data
 
--faster and uses fewer system and transaction log resources. per Microsoft Documents.
BEGIN TRAN;
TRUNCATE TABLE [dbo].[MySalesPerson];  --requires ALTER TABLE permissions or higher
 
SELECT * FROM [dbo].[MySalesPerson];
 
--Now Rollback as I do not want to lose my data.
ROLLBACK;
 
SELECT * FROM [dbo].[MySalesPerson];
GO

Example 4 – SQL Delete Record Foreign Key Error

Occasionally, the database design may have defined constraints that prevent deleting data. This example shows what happens if you attempt to delete records from table Person.BusinessEntity that is referenced by a Foreign Key. This is a way to protect data to prevent orphaned records in the database.

--4) Delete Error because of Foreign Key
DELETE [Person].[BusinessEntity]
WHERE BusinessEntityID = 1;
GO

Note that the result of the above query is an error message (below) notifying that this DELETE cannot happen. To correct this issue, you must first delete the referenced records from the table Person.BusinessEntityAddress.

Msg 547, Level 16, State 0, Line 146 
The DELETE statement conflicted with the REFERENCE constraint "FK_BusinessEntityAddress_BusinessEntity_BusinessEntityID".
The conflict occurred in database "AdventureWorks", table "Person.BusinessEntityAddress",
column 'BusinessEntityID'.

Example 5 – Using Best Practices When Deleting Data

When deleting records, it is helpful to show the rows that will be affected. To do this, run SET NOCOUNT OFF. This command may be off by default. Next, perform a SELECT query that identifies the rows that could be deleted. Then you are ready for the DELETE. Again, Transaction Control Statements will be used to verify the delete results and to decide to COMMIT or ROLLBACK the DELETE transaction should the validation fail. This recommendation introduces the OUTPUT clause, which allows for an integrated way to see the deleted records to help with validation.

I recommend using the delete template below, which includes the pre-check query, transaction control statements, and the OUTPUT clause, which will return the deleted rows. Keep in mind these extra actions are included due to the dangers of a DELETE. Note that the OUTPUT clause does not work with TRUNCATE.

SET NOCOUNT OFF;
--1) Check Query shows rows to be deleted
SELECT * FROM schema.TableName
WHERE Col1 = 1;
 
--2) Run the DELETE in a Transaction
BEGIN TRAN ;
DELETE schema.TableName
OUTPUT deleted.*
FROM schema.TableName
WHERE Col1 = 1;
 
--3) Run ROLLBACK if the count does not match
ROLLBACK;
--Or Run COMMIT if row count matches
COMMIT;

Option 5A – Best Practice Using OUTPUT Clause

An advantage of using the OUTPUT clause option is that the rows that have been deleted will be returned, allowing for positive verification.

----------------------------------------------------------------------
--5 OPTION A: Best Practice Output Query w/ ROLLBACK or COMMIT
----------------------------------------------------------------------
--Run SET NOCOUNT OFF to show row count results.
SET NOCOUNT OFF;
 
--1) Check Query shows rows to be deleted
SELECT m.*
FROM [dbo].[MySalesPerson] m
   INNER Join [HumanResources].[Employee] e 
      ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Database Administrator';
 
--2) Run the DELETE in a Transaction
BEGIN TRAN;
DELETE [dbo].[MySalesPerson]
OUTPUT Deleted.*
FROM [dbo].[MySalesPerson] m
   INNER Join [HumanResources].[Employee] e 
      ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Database Administrator'
--Stop Here to Validate.
 
--Now either Commit or Rollback the transaction based on the Check Query results! 
COMMIT;
--OR 
ROLLBACK;

Option 5B – Best Practice Using Check Queries

This option may be preferred as the concept to validate the results is the same, but it uses queries instead of the OUTPUT clause.

----------------------------------------------------------------------
--5 Option B: Best Practice Check Table w/ ROLLBACK or COMMIT
----------------------------------------------------------------------
 
--Run SET NOCOUNT OFF to show row count results.
SET NOCOUNT OFF;
 
--1) Check Query shows rows to be deleted
SELECT m.*
FROM [dbo].[MySalesPerson] m
   INNER Join [HumanResources].[Employee] e
      ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Database Administrator';
 
--2) Run the DELETE in a Transaction
BEGIN TRAN;
DELETE [dbo].[MySalesPerson]
FROM [dbo].[MySalesPerson] m
   INNER Join [HumanResources].[Employee] e
      ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Database Administrator'
 
--3) Again Run the Check Query to verify Results
SELECT m.*
FROM [dbo].[MySalesPerson] m
   INNER Join [HumanResources].[Employee] e
      ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Database Administrator';
--RUN THE Query Above Up to Here to review the output of the Check Query.
 
--Now either Commit or Rollback the transaction based on the Check Query results!
COMMIT;
--OR
ROLLBACK;
GO

Wrap Up

Through this demo, you have an opportunity to see how to DELETE data from a SQL table and to understand the dangers of unintended deletes and the importance to validate the delete results. See the links below to learn more about basic SQL statements and new topics presented throughout this article.

Next Steps

Learn more about SQL Transaction Control Statements
Read about the Basic SQL INSERT Statement with Examples
Learn How to Write a SQL SELECT Statement
Visit Microsoft Docs to learn more about TRUNCATE
Get more on SQL SELECT INTO Examples
Read about SQL JOIN Types with Examples
Check out the following tips:

Resources

Community

Subscribe