How does SQL Server handle different type of errors?

Please find the below sample code

    drop table  if exists #myTest
    create table #myTest (
        id int,
        name varchar(30)
    )    
    
    insert into #myTest values
        (1, 'John'),
        (2, 'Somu')
    
    --select 1/0                  -- Query 1 throws error
    --select cast('ABC' as int)   -- Query 2 throws error
    
    insert into #myTest values
        (3, 'Bela'),
        (2, 'Steve')
    
    select * from #myTest

When I uncomment 'Query 1' it throws below error but successfully inserts all 4 rows.

Msg 8134, Level 16, State 1, Line 62 Divide by zero error encountered.

When I uncomment 'Query 2' it throws below error but this time inserts no rows.

Msg 245, Level 16, State 1, Line 63 Conversion failed when converting the varchar value 'ABC' to data type int.

Looking at the error message it is not clear why one error allowed insertion of rows but second one did not. Could someone please explain what is the difference between the two?

Solution

There are two primary classes of errors in SQL Server: batch-aborting and statement-aborting (there are also other classes, but they are not usually relevant).

Statement-aborting errors behave rather oddly, as you have observed: they terminate the entire statement, but continue to the next line, as long as there is no BEGIN CATCH block. Effectively, every such line behaves like this

SAVE TRAN save1;

BEGIN TRY
    -- do the statement here
END TRY
BEGIN CATCH
    ROLLBACK TRAN save1;
END CATCH;

Batch-aborting errors are much more obvious, they simply abort the whole batch, rolling back the transaction at the same time.

Having said that, you are highly advised to always run with SET XACT_ABORT ON; because that upgrades statement-aborting errors to be batch-aborting, and generally making error-handling much more sane.

You can set it ON for all connections using

DECLARE @opts int;
SELECT @opts = CAST(value AS int) | 16384  -- bitwise OR the value for XACT_ABORT
FROM sys.configurations
WHERE name = 'user options'

EXEC sp_configure N'user options', @opts;
GO
RECONFIGURE

See also this series of articles for many more details on SQL Server's idiosyncratic error-handling.