SQL Server - Delete Large table data with Where clause

If we want to delete data from large table with some where caluse, the best way to do is :

Select COUNT(*)  FROM [Server].[Database].[MyTable] WHERE ...

 By doing above we can know the count of records to be deleted.

DECLARE @RowsDeleted INTEGER
DECLARE @RowsToDeleteAtATime
SET @RowsDeleted = 1
SET @RowsToDeleteAtATime = 50000

 WHILE (@RowsDeleted > 0)
    BEGIN
        DELETE TOP (@RowsToDeleteAtATime) FROM [Server].[Database].[MyTable] WHERE ...
        SET @RowsDeleted = @@ROWCOUNT
    END

 And by doing :

Select COUNT(*)  FROM [Server].[Database].[MyTable] WHERE ...

We can know the status.

Comments

Post a Comment

Popular posts from this blog

public vs protected vs default access modifiers - Java

| Class | Package | Subclass | Subclass | World | | |(same pkg)|(diff pkg)| ————————————+———————+—————————+——————————+——————————+———————— public | + | + | + | + | + ————————————+———————+—————————+——————————+——————————+———————— protected | + | + | + | + | ————————————+———————+—————————+——————————+——————————+———————— no modifier | + | + | + | | ————————————+———————+—————————+——————————+——————————+———————— private | + | | | | + : accessible blank : not accessible
Read more

Class, Reference and Object

If you like housing metaphors a  class  is like the blueprint for a house. Using this blueprint, you can build as many houses as you like. each house you build (or instantiate, in OO lingo) is an  object , also known as an  instance . each house also has an address, of course. If you want to tell someone where the house is, you give them a card with the address written on it. That card is the object's  reference . If you want to visit the house, you look at the address written on the card. This is called  dereferencing . You can copy that reference as much as you like, but there's just one house -- you're just copying the card that has the address on it, not the house itself. Java methods are always pass-by-value, but the value could be an object's reference. So, if I have: Foo myFoo = new Foo (); // 1 callBar ( myFoo ); // 2 myFoo . doSomething () // 4 void callBar ( Foo foo ) { foo = new Foo (); ...
Read more