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
This means that for your
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
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
Here you can find examples of Batch Abortion with Rollback.