john.straver (5/23/2012)

---

the answer is 0 rows when xact_abort is set to on
the answer is 2 rows when xact_abort is set to off

Neil Thomas (5/23/2012)

---

So that means that when xact_abort is set to off then

BEGIN TRANSACTION
...
COMMIT TRANSACTION

does not do as expected.

What's the default setting on a fresh install of SQL?

Nakul Vachhrajani (5/22/2012)

---

Good question with a couple of learning points.

1. By default SQL does not abort the entire transaction if it encounters a statment-level failure. In this case, the primary key violation is a statement level failure and hence, only the statement terminated, not the entire transaction

If the requirement is to cause a failure of the entire transaction (i.e. abort), then the following SET option needs to be set to ON:

SET XACT_ABORT ON

Setting XACT_ABORT to ON will cause SQL Server to abort the transaction even if it encounteres the statement level failure.

2. Referring one of my blog posts, row constructors (introduced in SQL Server 2008) process the entire batch at once. Hence, had this example used row constructors, the correct answer would have been 0 rows affected (Reference post: http://beyondrelational.com/modules/2/blogs/77/Posts/14434/0159-sql-server-row-constructors-triggers-process-the-entire-batch-at-once.aspx)

Thank-you!