Delete large amount of data in sql server

Question

Suppose that I have a table with 10000000 record. What is difference between this two solution?

1. delete data like :

   DELETE FROM MyTable
   

2. delete all of data with a application row by row :

   DELETE FROM MyTable WHERE ID = @SelectedID
   

Is the first solution has best performance? what is the impact on log and performance?

Answer 1

If you need to restrict to what rows you need to delete and not do a complete delete, or you can't use TRUNCATE TABLE (e.g. the table is referenced by a FK constraint, or included in an indexed view), then you can do the delete in chunks:

DECLARE @RowsDeleted INTEGER
SET @RowsDeleted = 1

WHILE (@RowsDeleted > 0)
    BEGIN
        -- delete 10,000 rows a time
        DELETE TOP (10000) FROM MyTable [WHERE .....] -- WHERE is optional
        SET @RowsDeleted = @@ROWCOUNT
    END

Generally, TRUNCATE is the best way and I'd use that if possible. But it cannot be used in all scenarios. Also, note that TRUNCATE will reset the IDENTITY value for the table if there is one.

If you are using SQL 2000 or earlier, the TOP condition is not available, so you can use SET ROWCOUNT instead.

DECLARE @RowsDeleted INTEGER
SET @RowsDeleted = 1
SET ROWCOUNT 10000 -- delete 10,000 rows a time

WHILE (@RowsDeleted > 0)
    BEGIN
        DELETE FROM MyTable [WHERE .....] -- WHERE is optional
        SET @RowsDeleted = @@ROWCOUNT
    END