optimizing the DELETE statement with a count

Which of the following two statements would you consider to be the most effective for deleting large number of rows?

Statement #1:

DELETE TOP (@count) FROM ProductInfo WHERE productId = @productid

Statement #2: Derived table

DELETE t1 FROM (SELECT开发者_StackOverflow社区 TOP (@count) * from ProductInfo 
                WHERE productId = @productId v) t1

Both and neither. You need to delete in batches when dealing with a large number, because of single transaction log growth issues. Assuming you want to delete all records for a given @productId:

declare @batchSize int = 10000;
do while(1=1)
begin
   delete top(@batchSize) from ProductInfo where productId = @productId;
   if (0 = @@rowcount)
      break;
end

The two forms of DELETE you posted are basically identical, the important thing is the table is organized by a clustered key based on productId key. If this is not true and you have a NC index on productId then the @batchSize has to be calibrated as to avoid the index tipping point.

Since both queries perform the exact same task, I'd use the first one, because it's simpler to read and understand.

(Also, since both queries do the same work, I suspect that they'd generate the same execution plan--can you check this?)