Click on the banner to learn about and purchase my database training on Azure

SQL Server and Azure SQL: How to delete or update data in large tables

Reading Time: 4 minutes

Introduction

Speak guys!
In this article I would like to share with you a little code that I needed to use today to UPDATE a relatively large table (55M+ records) in an Azure SQL Database base and, after 1h and 30mins waiting, I got a connection error and I had to do it all over again.

Not only in this case, but it can happen that the log overflows and the operation fails as well, and, as we know, when an error occurs during UPDATE or DELETE, the automatic rollback is initiated and no rows are actually changed. Breaking this single operation into smaller, segmented operations will allow the log to be flushed and thus minimize possible log overflows.

You may also want to break these large and time-consuming operations into smaller pieces to be able to track progress or your maintenance window is not enough to process the UPDATE/DELETE on all the necessary lines and you want to continue in another window.

I've also seen cases of tables with triggers that can end up generating a very large overhead when executing an UPDATE/DELETE that changes many rows. And we also have to remember locks in the table, which by breaking into smaller pieces, can be released quickly while the next batch is starting to process.

There are several reasons that can influence the decision to participate in a large UPDATE/DELETE and in this article I will show you some easy ways to do that.

Important: In case you just want to delete or change the first or last records of a table, you can use the solution I shared in the article SQL Server - How to UPDATE and DELETE with TOP x records.

How to delete or update data in large tables

UPDATE partitioned by integer field
In the example below, I'm using an autoincrement column of the integer type to assemble the ranges of the values ​​I'm going to update. The @Aumento variable defines the number of lines of each batch that will be updated and I defined in this example that the number of lines updated at a time will be 1 million lines.

UPDATE partitioned by integer field

Result:

UPDATE partitioned by date field
In the example below, I'm using a date column to assemble the ranges of the values ​​I'm going to update. The @Aumento variable defines the number of days of each batch that will be updated and I defined in this example that this number of days that will be updated by each block will be 30 days.

UPDATE partitioned by date field

Result:

DELETE TOP(N) partitioned using percentage
In the example below, I am deleting 10% of my table with each iteration. As the data is erased, I don't need to control intervals, I just erase it from 10 to 10%.

DELETE TOP(N) partitioned using percentage

Result:

Observation: This solution can present problems in very large tables, as 10% can represent a very large volume of rows. And when few records are left, the 10% can take many iterations to erase.

DELETE TOP(N) partitioned using number of rows
In the example below, I'm deleting 500k rows from my table with each iteration. As the data is erased, I don't need to control intervals, I just erase from 500 to 500 thousand lines until there are no more lines that meet the criteria of the filters.

DELETE TOP(N) partitioned using number of rows

Result:

And that's it folks!
Hope you like it and a big hug!