Search code examples
sql-serverjoinsql-execution-plan

Why does a delete statement affect an additional row?

UPDATE:

I have found that including the actual execution plan was the culprit of adding that additional row affected warning.

ORIGINAL QUESTION:

I'm deleting a few hundreds of thousands of unnecessary records, on batches of 100 records.

My question is that I see a warning of (1 row affected) after every 100 rows delete instruction and I'm wondering what could it be.

My table doesn't have any trigger, and it doesn't have relationships with cascades. So, do you know of anything else that could be affecting an additional row ?.

DECLARE @LOOP bit = 1

WHILE @LOOP = 1
BEGIN
  BEGIN TRANSACTION 

  DELETE TOP (100) FICHERO_ASIGNACION
  FROM FICHERO_ASIGNACION 
       INNER JOIN FICHERO ON FICHERO.FIC_KEY = FICHERO_ASIGNACION.FIC_KEY
  WHERE FAT_KEY = 4 and 
        CLI_COD is null and
        FIC_BUC = 'prd.factura-venta'

  PRINT '100 unneeded assignments deleted'
  IF @@ROWCOUNT = 0 SET @LOOP = 0
  COMMIT TRANSACTION
END

This is the result :

(100 rows affected)

(1 row affected)
100 unneeded assignments deleted
(100 rows affected)

(1 row affected)
100 unneeded assignments deleted
(100 rows affected)

(1 row affected)
100 unneeded assignments deleted
...
...

Also, on the execution plan I don't see any reference to additional tables or other searches.

enter image description here

Thank you.

Solution

  • It looks like that the additional row warning was raised by showing the Execution Plan.

    I have removed the "Include Actual Execution Plan" and now it doesn't show any additional row affected.

    Thank you all.