Month: December 2016

SQL 2016 SP1

The recently released service pack(SP1) for SQL 2016 includes very big enhancements. The biggest of all is providing premium features such as inmemory, columnstore to standard edition. Of course, there are some restrictions but it is definitely good news for people using standard edition or looking to upgrade.

One interesting enhancement is support to “Create or Alter” syntax while creating objects such as functions\procedures\triggers.

Sample Syntax :

Create or Alter procedure usp_test
as
begin
select 1 as id
end

Advertisement

XACT_ABORT – Why it should be set to ON

Certain errors cannot rollback a transaction. That’s why XACT_ABORT ON should be included in the code to prevent unhandled scenarios erroring out without rolling back the transaction and leaving an open transaction. In the example below, in the first scenario – you can see the session 1 query errored out leaving an open transaction and it blocks session 2 query( under read committed isolation). In the second scenario with XACT_ABORT ON, it will rollback the transaction.

--Set up Test Table
Create Table Test(id int,Name varchar(20))

--Scenario 1 with XACT_ABORT OFF
--Session 1
Begin
Set XACT_ABORT OFF
Begin Try
Begin Tran
Insert into test values(1,'test')
Select * from Idonotexist
If (@@ERROR=0)
Commit
End Try
Begin Catch
If (@@TranCount>0)
Rollback
End Catch
End

--Session 2
select * from test

--Scenario 2 with XACT_ABORT ON
--Session 1
Begin
Set XACT_ABORT ON
Begin Try
Begin Tran
Insert into test values(1,'test')
Select * from Idonotexist
If (@@ERROR=0)
Commit
End Try
Begin Catch
If (@@TranCount>0)
Rollback
End Catch
End

--Session 2
select * from test

--Clean Up
Drop Table Test