SQL Server: DELETE TOP Statement

In this post explains how to use the DELETE TOP statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) DELETE TOP statement is used to delete records from a table in SQL Server and limit the number of records deleted based on a fixed value or percentage.

Syntax

The syntax for the DELETE TOP statement in SQL Server (Transact-SQL) is:

DELETE TOP (top_value) [ PERCENT ] 
FROM table
[WHERE conditions];

Parameters or Arguments

table

The table that you wish to delete records from.

WHERE conditions

Optional. The conditions that must be met for the records to be deleted.

TOP (top_value)

It will delete the top number of rows in the result set based on top_value. For example, TOP(10) would delete the top 10 rows matching the delete criteria.

PERCENT

Optional. If PERCENT is specified, then the top rows are based on a top_value percentage of the total result set (as specfied by the PERCENT value). For example, TOP(10) PERCENT would delete the top 10% of the records matching the delete criteria.

Note

• You do not need to list fields in the SQL Server DELETE statement since you are deleting the entire row from the table.

Example - Using TOP keyword

Let's look at a SQL Server example, where we use the TOP keyword in the DELETE statement.

For example:

DELETE TOP(10)
FROM employees
WHERE last_name = 'Mark';

This SQL Server DELETE TOP example would delete the first 10 records from the employees table where the last_name is 'Mark'. If there are other records in the employees table that have a last_name of 'Mark', they will not be deleted by the DELETE TOP statement.

Example - Using TOP PERCENT keyword

Let's look at a SQL Server example, where we use the TOP PERCENT keyword in the DELETE statement.

For example:

DELETE TOP(25) PERCENT
FROM employees
WHERE first_name = 'Julie';

This SQL Server DELETE TOP example would delete the first 25% of the records matching the DELETE TOP criteria. So in this example, the DELETE TOP statement would delete the top 25% of records from the employees table where the first_name is 'Julie'. The other 75% of the records matching this criteria would not be deleted by the DELETE TOP statement.