分批删除大量数据

-- specify the number of rows you want 
--    to delete per 'gulp':
DECLARE @count int
SET @count = 2000

-- keep track of the number of rows
--    impacted by each gulp... once it
--  drops below the intended number of rows
--    then you're done... 
DECLARE @rowcount int
SET @rowcount = @count

-- keep date same through entire operation
DECLARE @cutoff datetime
SET @cutoff = GETDATE() - 60 

WHILE @count = @rowcount BEGIN

    -- archiving logic goes here... 
    -- (or it can take place BEFORE you 
    --    start with this 'nibbling operation')
    
    -- remember, nibbling deletes aren't the 
    --    only thing you can do, you can also do
    --    UPDATEs, INSERTs, MERGEs, etc. 

    DELETE FROM LorryLocations
    WHERE locationId IN (
        SELECT TOP (@count) locationId 
        FROM LorryLocations WITH(NOLOCK)
        WHERE
            [timestamp] < @cutoff
    )
    
    -- update the number of rows modified
    -- if it's less than @count, then this was
    -- our last pass... and the WHILE loop
    -- will break
    SET @rowcount = @@ROWCOUNT
            
    -- a few milliseconds is typically all you'll
    -- need for most operations, but toggle this
    -- value as needed
    WAITFOR DELAY '000:00:00.400'
END

转载于:https://www.cnblogs.com/geass/archive/2011/05/12/2043950.html