SQL Server: how transactions work

Question

In SQL Server, how many transactions will this produce?

DECLARE @deleted BIGINT
SET @deleted = 100000
WHILE @deleted = 100000
BEGIN
DELETE TOP(100000) FROM MYTABLE WITH (ROWLOCK)
where Col1 = 7048 and COL2 = 39727 and Col3 = 0
SET @deleted = (SELECT @@ROWCOUNT)
END

If I cancel after running this for 10 minutes will it need to roll back?

Would adding a being transaction and end transaction fix this if I don't want it to rollback past one iteration after a cancel?

Would it make any difference if I put it in a stored procedure?

Answer 1

I believe this will execute under a single transaction (which SQL Server creates for you in this case). You could run Profiler to validate this. Putting it in a stored proc will not make any difference. I might suggest you put a Begin Tran (and corresponding End Tran) for each pass through the loop. One thing this will help prevent is your transaction log getting too large.

Answer 2

It will be an implicit transaction. remember ACID? everything in SQL Server is a transaction either implicit or explicit otherwise you wouldn't be able to guarantee ACID

Answer 3

When you don't have the BEGIN TRANSACTION and COMMIT, you have implied transactions. And, each DELETE will be a separate transaction. So, if you cancel the script, it will rollback the current command. But, all previous DELETE steps are already committed.

If you add a BEGIN TRANSACTION before your code and a COMMIT after your code, then you get a single transaction. If you cancel the query, you leave an open transaction, where there is not commit or rollback. In this case, you must submit a ROLLBACK command to start the rollback process.