Across both engines: porting and concurrency interview questions

The statements move into BEGIN TRY ... END TRY and the handlers into one BEGIN CATCH ... END CATCH block that follows it. PL/SQL picks a handler by exception name; T-SQL has a single CATCH block that reads ERROR_NUMBER(), ERROR_MESSAGE() and the other error functions, the counterparts of SQLCODE and SQLERRM, and branches on the number, 8134 for example where PL/SQL had WHEN ZERO_DIVIDE, with a last branch for WHEN OTHERS. An application error raised with RAISE_APPLICATION_ERROR and a number from -20000 to -20999 becomes THROW with a number of 50,000 or more, and a RAISE that re-raises inside a handler becomes THROW; with no arguments.

A package variable keeps its value for the whole session and across calls, and T-SQL has neither packages nor variables that outlive a batch. Small values that belong to one session, such as a counter or the current user's id, move to SESSION_CONTEXT: sp_set_session_context stores a key with a value of up to 8,000 bytes, 1 MB in all, and SESSION_CONTEXT reads it in later batches of the same session. Values that must last beyond the session, or that several sessions share, move to a table. Package constants become literals, functions or rows of a settings table.

The engines serve readers in different ways. Oracle answers each query from the data committed when the query began: when a row has been changed and not committed, it rebuilds the old version from undo data, so a reader takes no lock and a writer's lock never stops it; only SELECT ... FOR UPDATE asks for the row lock. SQL Server's default, READ COMMITTED with READ_COMMITTED_SNAPSHOT off, takes a shared lock on each row it reads, and a shared lock waits for a writer's exclusive lock until the writer's transaction ends, or until SET LOCK_TIMEOUT ends the statement with error 1222. Turning READ_COMMITTED_SNAPSHOT on makes SQL Server readers use row versions, and they stop waiting.