How to Delete Top 'n' Rows in SQL Server?

To demonstrate how to delete top 'n' rows in SQL Server, let's suppose we have the following table:

+----+--------+
| id |  name  |
+----+--------+
|  1 | foo    |
|  2 | bar    |
|  3 | biz    |
|  4 | baz    |
|  5 | qux    |
|  6 | quux   |
|  7 | quuz   |
|  8 | corge  |
|  9 | grault |
| 10 | garply |
+----+--------+

CREATE TABLE customer
(
    id INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
    name VARCHAR(6) NOT NULL
);

INSERT INTO customer(name)
VALUES ('foo'), ('bar'), ('biz'), ('baz'), ('qux'), ('quux'), ('quuz'), ('corge'), ('grault'), ('garply');

Deleting First 'n' Unordered Rows

If the ordering of the rows you wish to delete does not matter, then we can simply make use of DELETE TOP like so:

-- SQL2005+
DELETE TOP (5) FROM customer;

For earlier versions of SQL Server that do not support DELETE TOP, we can do the following:

DELETE FROM customer
WHERE id IN
(
  SELECT TOP (5) id
  FROM customer
);

Or, using the following altnerative SQL statement:

DELETE T FROM (
  SELECT TOP (5) id
  FROM customer
) T;

The queries above would result in:

+----+--------+
| id |  name  |
+----+--------+
|  6 | quux   |
|  7 | quuz   |
|  8 | corge  |
|  9 | grault |
| 10 | garply |
+----+--------+

We can also achieve the same result by deleting based on a percentage using TOP PERCENT. For example, to delete 50% of records, we can do the following:

DELETE TOP (50) PERCENT FROM customer;

Deleting First 'n' Ordered Rows

To delete a set of ordered rows, we can use TOP together with ORDER BY in a subselect statement like so:

DELETE T FROM (
  SELECT TOP (5) id
  FROM customer
  ORDER BY name
) T;

Or, alternatively:

DELETE FROM customer
WHERE id IN
(
    SELECT TOP (5) id
    FROM customer
    ORDER BY name
);

Please note that deleting ordered rows can't be achieved by simply using DELETE TOP because the rows referenced in the TOP expression are not arranged in any order.

Please be aware of using a non-key column in the subselect statement as it may result in the deletion of more than the expected number of rows. This might be so because the result may contain duplicate values.

The queries above would result in the following:

+----+--------+
| id |  name  |
+----+--------+
|  5 | qux    |
|  6 | quux   |
|  7 | quuz   |
|  9 | grault |
| 10 | garply |
+----+--------+

To delete based on percentage we can use TOP PERCENT in the subselect statement. For example, to achieve the same result as in the examples above (i.e. delete 50% of records), we can do the following:

DELETE FROM customer
WHERE id IN
(
    SELECT TOP (50) PERCENT id
    FROM customer
    ORDER BY name
);