Something that is a common problem in mssql server is deleting a very large number of rows from a table. Normally due to locking the table that the very long delete is running on. There is a few common ways of doing this. Mostly by setting row count and looping though a delete until it runs out of rows to delete. In sql server 2008 it is also possible todo this by moving the data from one table to another using a delete and the output clause.

SET ROWCOUNT 5000
WHILE (1 = 1)
BEGIN
        BEGIN TRAN
        DELETE FROM T WHERE N > 5000
        IF @@ROWCOUNT = 0
                BREAK
END
SET ROWCOUNT 0

Or like this

SET ROWCOUNT 5000
WHILE EXISTS(SELECT * FROM T WHERE N > 5000)
BEGIN
        DELETE FROM T WHERE N > 5000
END
SET ROWCOUNT 0

But what if we also wanted to take a backup of the data at the same time as we delete it. Just incase we have made any mistakes. Well this is possible using the OUTPUT DELETED clause in mssql server using the following code. Of course you will need to create the table we are going to backup to just prior to running this. In this example we are using T as the main table and TBackup as our backup table that we are moving data to.

SET ROWCOUNT 5000
WHILE EXISTS(SELECT * FROM T WHERE N > 5000)
BEGIN
        INSERT INTO TBackup
                SELECT * FROM (
                        DELETE FROM T
                                OUTPUT DELETED.*
                                WHERE N > 5000
                ) a
END
SET ROWCOUNT 0

So now we have all the data we wanted out of the main table into a second table. This may be purged later or we are simply moving things into an archive table.





Last Modified: 12 December 2016

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-09-24 - MSSQL - Checking Uptime
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-09 - MSSQL - Last Backup time and size
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-05 - MSSQL - Log Sizes
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - All temp tables are global
2011-02-20 - MSSQL - Unsigned int
2011-02-17 - MSSQL - Convert IP To big int
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-30 - MSSQL - Adding SHASum support
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-29 - MSSQL - TRIM Function