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

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:

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:

Comparing the execution plan:

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:

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