Prashanth Jayaram

Overview of the SQL Delete statement

October 25, 2018 by

This article on the SQL Delete is a part of the SQL essential series on key statements, functions and operations in SQL Server.

To remove a row from a table is accomplished through a Data Manipulation Language, aka DML statement, using the delete keyword. The SQL delete operation is by far the simplest of all the DML commands. On execution of the delete command, we don’t have to worry about getting any form of data from the table, and we don’t have to worry about working with any data that we get back from the table(s). We just simply tell the database to delete a specific record, and it either does or it doesn’t. It’s that simple. 

First, let’s quickly review what an SQL delete statement looks like.  We need to tell the database and table from where it should delete the data. It’s a good idea to add a condition clause to set the scope of data deletion. Otherwise, it will delete everything in the table. 

Let’s take a look at our table and removing some records. 

How to delete rows with no where clause

The following example deletes all rows from the Person.Person the table in the AdventureWorks2014 database. There is no restriction enforced on the SQL delete statement using a WHERE clause.

How to delete rows with where clause

The following example deletes rows from the [Person].[Person] table in the AdventureWorks2014 database in which the value in the businessEntityID column is greater than 30,,000

Note: An unfortunate mistake that may occur is to accidently run a SQL Delete with no Where clause and inadvertently delete all of your data. To prevent this from happening consider using the Execution guard feature in ApexSQL Complete, to warn against such potentially damaging actions, before you execute them. Learn more: Execution alerts

How to delete rows using Top with where clause

The following example deletes 50 random rows from the Person.Person table in the AdventureWorks2014 database. The value in the BusinessEntityID must be in between 30,000 and 40,000

Note: The when the TOP (n) clause is used with the SQL Delete statement and any DML statement (i.e. Select, Insert, Delete and Update), the operation is performed on a random selection of a number of rows specified in the Top clause.

How to delete duplicate rows

In the real-world, we tend to gather data from different sources; it’s not uncommon to have duplicate records. One approach to the duplicate problem is first to identify where the duplicates have occurred. And run a select query on those columns.

The following example uses the PARTITION BY argument to partition the query result set by all the columns of tb_spaceused table. The Row_Number (), a window function, which means it operates over an ordered set. The ORDER BY clause specified in the OVER clause orders the rows in each partition by the entire columns tb_spaceused table.


Replacing the Select statement with a Delete removes all the duplicates of the table.


How to delete rows using SQL sub-queries

In the following example, the rows in one table are deleted based on data in another table. In the examples, the rows from the SalesPersonQuotaHistory table are deleted based on the SalesYTD column of the SalesPerson table.

How to delete rows using SQL Joins

In this section, we will use the SQL Delete statement to delete the data from the Adeventureworks2014 database. Deleting data, at first sight, sound trivial, but once we get into a large database design things might not be same and easy anymore.

In many cases, the tables are related via a primary and foreign key relationship. In the following example, we can see a use of joins to delete the data from the Sales.SalesPersonQuotaHistory.

How to delete rows from a remote table using linked servers and OpenQuery

The following example uses the SQL delete statement to delete rows from a remote table using the linked server named, hqdbt01. Then query the remote table using four-part object naming convention to delete the rows from the remote table

The following example, the remote table is queried by specifying the OPENQUERY rowset function along with the delete command.

How to delete rows using SSMS

Using the SQL Server Management Studio (SSMS), Graphical User Interface (GUI) to delete rows involves a manual search. In reality, it will be much easier and quicker to delete records with a SQL query. 

Let’s go ahead and locate the table to use a SQL delete statement, in this case, table dbo.cities is selected. Now, right-click and choose Edit Top 200 rows. This option opens up a query designer window. Next, right-click the window and select Execute SQL and write a new query that will delete rows from the dbo.cities table.

 

In the result pane, make sure that SELECT Statement is pulling up the correct targeted records before start deleting rows.  Select the rows and right-click the rows and choose Delete to remove the rows from the table.

Summary

Thus far, we’ve seen many different ways use the SQL delete statement to remove data. But, there is a list of the consideration to be followed while using the delete statement, and it as follows:

  1. It is always recommended to start with a SELECT statement before you delete anything to make sure that you’re targeting the correct records. So the delete statement is used to delete rows from a table using the where clause to select only the rows to be deleted. 
  2. Always use a unique identifier to locate the rows that you need to delete.
  3. To delete all the rows in a table, always use TRUNCATE TABLE. TRUNCATE TABLE which is faster than a SQL delete statement and it uses fewer system and transaction-log resources.
  4. By default, DELETE statements induce an exclusive (X) lock on the table, and it holds the lock until the transaction completes.
  5. An exclusive lock prevents other transaction from modifying the data; read operations are allowed with the use of NOLOCK hint or read uncommitted isolation level.
  6. It is recommended to specify the TABLOCK hint in the delete statement. This process allows page de-allocation and associated space available for reuse by other objects in the database.
  7. It is good practice to create a clustered index on the heap table before executing a delete statement.
  8. Although very simple and very powerful, and the result of a Delete statement is destructive. Deleted rows cannot be easily recovered. 

Note: To recover deleted rows see SQL Server disaster recovery – How to quickly recover data lost due to an Inadvertent delete operation

That’s all for now… Hope you enjoy reading this article on the SQL delete statement. If you have any questions, feel free to comment below.


Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram
DML, T-SQL

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

146 Views