SQL Server - Does @@RowCount automatically commit transaction?

Question

I'm reviewing a developer's script to delete all data from a large table that is older than a specified date.

SET @R = 1;
WHILE @R > 0
    BEGIN
        ----Begin Transaction
        DELETE TOP (100000) FROM
        TBL_MYTABLE
        WHERE dateCreated < @DELETEDATE;

        ----Commit Transaction;
        SET @R = @@ROWCOUNT;

    END;

One thing that stood out to me is that he is checking the value of @@ROWCOUNT without doing anything with the @R variable. Does that statement automatically commit the transaction? Or would the entire WHILE loop have to complete first?

EDIT: As a followup question, how would I determine if each iteration of the loop is committing, or if it is waiting until the end?

Answer 1

The short answer is No it doesn't. because there isn't COMMIT TRANSACTION keyword

The sample is batch delete data.

Here is a sample we can see I set a BREAK; when T count amount smaller than or equal to 2.

When it exits from the loop we select the data from T, we can see only two rows.

CREATE TABLE T(
    i int

);
INSERT INTO T values (1);
INSERT INTO T values (2);
INSERT INTO T values (3);
INSERT INTO T values (4);
INSERT INTO T values (5);

DECLARE @R INT = 1
SET @R = 1;
WHILE @R > 0
    BEGIN

        DELETE TOP (1) 
        FROM T


        SET @R = @@ROWCOUNT;

        IF((SELECT COUNT(*) FROM T)<= 2)
          BREAK;

END;

SELECT * FROM T

sqlfiddle