Deleting the Information from Large Tables
Most of the time it happens for Developers or DBA’s or BI experts to delete all the information of a table consisting of several thousands of records.
To this end, there are various methods and in this article, we are going to briefly discuss all of the methods.
The first and simplest way to delete a record from a table is to use the Delete command in the usual way to delete all records in a table at once.
DELETE FROM [dbo].[tesrdel]
The limitations or disadvantages of using this method are:
- When we use this method to delete, we create a Lock on the table that is of XLock type , which creates long waits for other requests.
- If the Recovery Model of the database is Full (this happens in databases whose Recovery Model has been changed to Simple) increases the size of Log file (creation of many Log Records) and fills the disk related to Log file and makes the service down.
The second method, which is less expensive than the first method is using the Truncate Table command to remove records from the table at once, which has the following advantages:
- The number of Log Records generated by this method is less than the first method.
- In this method, very little Lock is created on the table and usually the execution of other Requests is accompanied by very short Waits.
Although this method has good advantages, it has some limitations too.
For instance, if the table in question is under the reference of another table, which means that the Primary Key of the table in question is Foreign Key for other tables, or if the table is bound to Stored Procedure, View or Function (in creating each of the objects, the phrase “With Schema Binding” is used while defining stored procedure, view and function), it is impossible to use Truncate Table command and the following error will occur if you use that command.
The third method is to use the method mentioned in the article “Deleting information from a table with a high number of records with the lowest log in SQL Server“.
As the author of this article points out; this method is only suitable for tables that are simply designed and do not contain complex or trigger indices.
The fourth method, which includes the advantages of the second method and the third method, which is to create the least number of logs when removing the record from the table, is that the Delete command is executed in groups not once on the table.
We can use the following script:
--Enable minimaly logged (careful for log chain and aoag)
DBCC TRACEON(610, -1);
DECLARE @myContinue BIT
DECLARE @myRowCount INT
DECLARE @myLoopCount INT
DECLARE @myBatchCount int
SET NOCOUNT ON;
SET @myContinue = 1
SET @myLoopCount=1
SET @myBatchCount=10000
WHILE @myContinue = 1
BEGIN
--Retrive First x Records only (Deleting Records in batch of x numbers)
SET ROWCOUNT @myBatchCount
PRINT CAST(@myLoopCount AS NVARCHAR(10)) + N' START:' + CAST(GETDATE() as nvarchar(50))
BEGIN TRANSACTION
DELETE FROM [dbo].[tesrdel]
--Count number of affected records for determining EOF
SET @myRowCount = @@rowcount
COMMIT
PRINT CAST(@myLoopCount AS NVARCHAR(10)) + N' END:' + CAST(GETDATE() as nvarchar(50))
SET @myLoopCount=@myLoopCount+1
--IF EOF
IF @myRowCount<@myBatchCount
BEGIN
SET @myContinue = 0
END
END
--Disable minimally logged
DBCC TRACEOFF(610, -1);
First, we activate Trace Flag, 610 to create a small number of Log Records in the Log file (similar to changing the Recovery Model database to Bulk Logged). We should remember disabling Trace Flag when the command is executed.
The script uses group deleting of records in a While loop. In the While loop, the Begin Transaction command and then the Delete command are used without the condition statement and then Commit command is executed. This command makes the transactions created for Delete from the table very short and quickly determines the status of a specific transaction and creates a small number of Log Records.
Also in the script above, you can see that the output of the @@RowCount command (i.e. the number of records that the Delete command has affected) is stored in a variable and then it is checked to see whether the number of records in a batch is less or no and the condition of While loop is established or not?
Now this question arises that how it is possible that the records of a table will be deleted in batches without any condition being considered or the Top operator used for it. The answer to this question lies in the use of the SET ROWCOUNT command. By default, the number in front of the SET ROWCOUNT command is zero. If another number other than zero is entered in front of the SET ROWCOUNT command, the number of records in the batch will be equal to the desired number, and the desired operation, such as Delete, will be performed on that batch.
