I expected the answer to be that there was an error because tbl did not exist. Since that wasn't an option, I did some experimenting around the open transactions and got a surprise.
Here is my full code:
create table dbo.tbl (x char(5000));
INSERT INTO dbo.tbl VALUES
( REPLICATE('a',5000) )
select count(*) from dbo.tbl;
select count(*) From dbo.tbl;
drop table dbo.tbl;
Unsurprisingly, the first select returns 100. But the second returns 0! In fact any number below 100 on the GO after the commit results in an empty table. Somehow the COMMIT is being ignored. When I do use 100 then the rollback generates an error that there are no open transactions. Can anybody explain? I also tried just using GO 3 after the insert and the commit only had any effect when it was also 3. 2 left an empty table.
All 100 transactions are nested transactions. That means the only "real" transaction is the first one. Until the very first transaction is committed, nothing is "really" committed.
With a nested transaction, each COMMIT "closes" the innermost transaction, but nothing is really committed. Once 100 COMMIT statements have been executed, the outermost "real" transaction commits. At that point, other connections can see the 100 committed rows. In the code posted, it's not possible to commit some of the rows and rollback the rest.
However, ROLLBACK doesn't work like COMMIT on nested transactions. Any ROLLBACK statement executed in a nested transaction context applies to the outermost transaction. While it takes 100 commits to commit those 100 rows, it only takes one rollback to undo all of it.
This particular scenario can cause headaches when stored procedures that open explicit transactions call other stored procedures that also open explicit transactions. If the nested stored procedure rolls back, then the transaction in the calling stored procedure is also rolled back. It's why you might see a check of @@TRANCOUNT or XACT_STATE() before the stored procedure calls ROLLBACK (to find out if the transaction was already rolled back).