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.
Note: To learn more about how to use SQL Delete statement, please read The Best Ways to Use SQL Delete Statement in a SQL Table article.
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.
|
1 2 3 |
USE Adventureworks2014; GO DELETE FROM [Person].[Person]; |
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
|
1 2 3 4 |
USE Adventureworks2014; GO DELETE FROM [Person].[Person] WHERE businessEntityID > 30000; |
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
|
1 2 3 4 |
USE Adventureworks2014; GO DELETE TOP(50) FROM [Person].[Person] WHERE BusinessEntityID between 30000 and 40000 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
EATE TABLE tb_spaceused (database_name NVARCHAR(128), database_size VARCHAR(18), [unallocated space] VARCHAR(18), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18) ); INSERT INTO tb_spaceused EXEC sp_msforeachdb @command1 = "use ? exec sp_spaceused @oneresultset = 1"; SELECT * FROM tb_spaceused order by database_name |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
WITH CTE AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY database_name, database_size, [unallocated space], reserved, data, index_size, unused ORDER BY database_name ) AS Row_Num FROM tb_spaceused) SELECT * FROM CTE WHERE Row_Num <> 1; |
Replacing the Select statement with a Delete removes all the duplicates of the table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
WITH CTE AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY database_name, database_size, [unallocated space], reserved, data, index_size, unused ORDER BY database_name ) AS Row_Num FROM tb_spaceused) --SELECT * --FROM CTE --WHERE Row_Num <> 1; DELETE FROM CTE WHERE Row_Num <> 1; |
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.
|
1 2 3 4 5 6 7 8 |
DELETE FROM Sales.SalesPersonQuotaHistory WHERE BusinessEntityID IN ( SELECT BusinessEntityID FROM Sales.SalesPerson WHERE SalesYTD > 4900000.00 ); GO |
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.
|
1 2 3 4 5 |
DELETE sq FROM Sales.SalesPersonQuotaHistory sq INNER JOIN Sales.SalesPerson sp ON sq.BusinessEntityID = sp.BusinessEntityID WHERE sp.SalesYTD > 4500000.00; GO |
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
|
1 2 3 |
DELETE FROM [hqdbt01].AdventureWorks2014.[HumanResources].[Shift] WHERE ShiftID = 2; |
The following example, the remote table is queried by specifying the OPENQUERY rowset function along with the delete command.
|
1 2 3 |
DELETE OPENQUERY (hqdbt01, 'SELECT * FROM AdventureWorks2014.HumanResources.Department WHERE DepartmentID = 18'); |
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:
- 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
- Always use a unique identifier to locate the rows that you need to delete
- 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
- By default, DELETE statements induce an exclusive (X) lock on the table, and it holds the lock until the transaction completes
- 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
- 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
- It is good practice to create a clustered index on the heap table before executing a delete statement
- 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.
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
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021