SQL Server - How to UPDATE and DELETE with TOP x Records

Views: 6.669
Reading Time: 3 minutes

Hello people,
Good Morning!

Today I will show you how to update or delete a fixed number of records from a table, which can be used to manipulate table data without locking it for a long time while executing queries.

Generation of a simple base

I will demonstrate how to create the test base that we will use in this post

UPDATE TOP

A lot of people don't know, but the UPDATE statement supports using TOP to delimit the amount of records to update:

SQL Server - Update TOP 1

Although quite simple, this command can end up fooling you. This is because when you use a TOP command without using ORDER BY, the result is not deterministic. Every time you use a TOP, you must use an ORDER BY to tell the bank how to sort the results and then filter using the TOP. Even if the table has clustered index, this does not guarantee that the data will be sorted using the clustered index or with RowID for HEAP tables.

Although UPDATE supports TOP, it does not support ORDER BY. To resolve this situation, we can perform this same update as follows:

Or it can be done with CTE (Common Table Expressions), which is a more elegant and efficient way:

Result:
SQL Server - Update TOP 1 _2

Comparing the execution plan:
SQL Server - Update TOP 1 Execution Plan

SQL Server - Update TOP 1 Execution Plan 2

As indicated above, the solution using CTE is more elegant and performative, running faster and consuming less disk and CPU.

DELETE TOP

As with UPDATE, we can use TOP on DELETE itself, at the risk of deleting random records, and we can use ORDER BY on both JOIN on update and CTE to filter and limit the records that will be removed:

Example:
SQL Server - Delete TOP 1_3

That's it folks!
Regards and see you next post.