When is an Insert committed?

If I highlight/select both the statements below, without a GO stmt between them, will the Insert be committed once it shows ‘Rows Affected 1234’ in results pane?
If so, can I then cancel the Rebuild proc without rolling back the Insert?

Insert into tablexx
Exec RebuildDisableIndexes,'Rebuild', tablexx

These are the results so far:

(2000000) rows affected;
2 of 7 indexes have been rebuilt by the proc RebuildDisableIndexes (alter index...Rebuild step 2 of 7).

But something is bogging step 3 down.

If I cancel the query will it roll back the insert of 2000000 rows? I don’t think it will, but seeking confirmation.

I just ran a test to see what behavior would persist. See below, this is sql server 2012

create table kevin_test (
id int
)
go

insert into kevin_test values (1)
insert into kevin_test values (2)
exec kevin_test1

kevin_test1 sp looks like this

create procedure kevin_test1 as 
begin

WAITFOR DELAY '00:00:05'

select * from kevin_test

end

When you run that, once the code hits the stored procedure, I cancel the call to the sp, I added that waitfor delay so I can actually cancel it otherwise it would be too fast.

What I found is that even after cancelled the call to the proc, the values were still committed to the kevin_test table. Therefore yes, I think the values are committed by the insert and if you cancel the proc call after the insert, the changes made by the insert are still committed.

This would not be the case if you did something like this

begin tran
insert into table values(...)
exec proc
commit tran

If you cancelled the proc call, it would rollback the insert too. This a generic test, you can confirm with testing your specific code.

If you don’t open transaction explicitly, you operate in
auto-commit mode

This means that for your INSERT SQL Server opens a transaction on its own and commits it after INSERT is completed. At this point it can show you “rows affected”. And once committed, INSERT cannot be rolled back anymore.

Only after that your sp starts and any error in this sp / your manual interruption cannot affect any previously committed transaction.

The fact that you was executing these 2 statements in one batch (“without a GO “) can influence only if the error happens in the first statement: the second may be still executed or the batch can be aborted, but in your case the first statement was already committed, so nothing can make it rollback after.

Here you can find examples of Batch Abortion with Rollback.