SQL Server batch delete using WHILE loop does not work

sql sql-server rowcount
JCB picture JCB · Mar 21, 2013 · Viewed 56.3k times · Source

I have a very large table, so I am using the following to delete older entries:

WHILE (@@ROWCOUNT > 0)
BEGIN
    DELETE TOP (5000) FROM myTable
    WHERE date < 20130103
END

I have run this a few times using different dates. Sometimes it works fine (takes about 20 minutes), but other times the query finishes immediately and nothing has been deleted. When that happens, I just do a simple SELECT statement from that table, and then try the above WHILE statement again, and then it works! Anyone know why this is? I need to automate this query to run on a regular basis to control the table size, but I want to make sure it actually deletes properly when it runs. Thank you.

Answer

Michael Fredrickson picture Michael Fredrickson · Mar 21, 2013

What are you running before this block of code? @@ROWCOUNT will be set to whatever statement proceeded it.. if you run some other command beforehand, it could be 0.

Instead, you could force the initial count to be 1:

DECLARE @Rows INT
SET @Rows = 1

WHILE (@Rows > 0)
BEGIN
    DELETE TOP (5000) FROM myTable
    WHERE date < 20130103

    SET @Rows = @@ROWCOUNT
END

Related questions

How to fetch the row count for all tables in a SQL SERVER database

I am searching for a SQL Script that can be used to determine if there is any data (i.e. row count) in any of the tables of a given database. The idea is to re-incarnate the database in case …

Read More
CTE, ROW_NUMBER and ROWCOUNT

I am trying to return a page of data and also row count of all data in one stored procedure which looks like following: WITH Props AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY PropertyID) AS RowNumber FROM Property WHERE PropertyType = @…

Read More
Total Row Count in sql query---sql server 2008

My query is as follows BEGIN WITH MyCTE AS ( SELECT T.MusicAlbumTitle ,D.musicTitle ,D.mVideoID ,D.musicFileName ,T.ReleaseDate AS ReleasedDate ,D.MusicLength ,D.musicSinger ,D.MusicVideoID ,D.ExternalLink ,D.CoverImg ,ROW_NUMBER() OVER ( PARTITION BY D.MusicVideoID ORDER …

Read More