ronascentes

-- got from https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes
DECLARE @rows INT, @ErrorMessage NVARCHAR(4000);

SET NOCOUNT, XACT_ABORT ON;
SET @rows = 1;

BEGIN TRY
	WHILE (@rows > 0)
	BEGIN
		BEGIN TRANSACTION;

ronascentes

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;
        -- A FOREIGN KEY constraint exists on this table. This 
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;

    -- If the DELETE statement succeeds, commit the transaction.