Post reply

Rolling Off Data ... Nibbling with ROWCOUNT

  • December 2, 2004 at 8:33 am

    #87228

    I'm ready to roll off data from partitioned tables but the delete will cross partitions unioned by my view.   We have massive amounts of data so I want to do some data nibbling and saw a brief write up saying I should script using ROWCOUNT to delete every 1000 for example.  Anyone have an example simple script?

    Thanks!

  • December 3, 2004 at 6:10 am

    #532410

    Here's a simple query to clear out a table 1000 rows at a time.  The partitioned view shouldn't be any different

    DECLARE @count int

    SELECT @count = 1

    WHILE (@count > 0)

    BEGIN

     SET rowcount 1000

     DELETE FROM my_table

     SELECT @count =  @@ROWCOUNT

    END

    --don't forget to reset the rowcount

    SET rowcount 0

     

     

  • December 3, 2004 at 7:40 am

    #532439

    That should work. Be sure you add a WHERE clause based on your criteria.

  • December 3, 2004 at 10:42 am

    #532513

    I dont' need a begin tran or end tran?

  • December 3, 2004 at 10:49 am

    #532518

    You don't _need_ a begin/end tran, but you need to decide if you _want_ one in this case.

    It depends on your data for this type of action.  If another process may be modifying/reading data at the same time you're running your nibbler you'll be locking them out of the table(s) until all of the data is gone which may be a bad thing. 

    If the delete is going to get more complicated and you'll want it to rollback in case of an error then you may want transactions with some basic error checking.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply