Today's question (by Kathi Kellenberger):

Using TOP with ROW_NUMBER

SELECT TOP(10) SalesOrderID, ProductID, OrderQty, 
ROW_NUMBER() OVER(ORDER BY SalesOrderID) 
        + MAX(SalesOrderID) OVER() AS NewOrderID
FROM Sales.SalesOrderDetail AS SOD
ORDER BY NEWID();

WITH SalesID AS (
SELECT TOP(10) SalesOrderID,  ProductID, OrderQty,  
            MAX(SalesOrderID) OVER() AS MaxID
FROM Sales.SalesOrderDetail AS SOD 
ORDER BY NEWID())
SELECT TOP(10) SalesOrderID, ProductID, OrderQty,  
        ROW_NUMBER() OVER(ORDER BY SalesOrderID) 
        + MaxID AS NewOrderID
FROM SalesID;

SELECT SalesOrderID, ProductID, OrderQty, 
ROW_NUMBER() OVER(ORDER BY SalesOrderID) 
        + MAX(SalesOrderID) OVER() AS NewOrderID 
FROM Sales.SalesOrderDetail 
WHERE ROW_NUMBER() OVER(ORDER BY SalesOrderID) <= 10;

Think you know the answer? Click here, and find out if you are right.

XACT_STATE(), @@TRANCOUNT and ROLLBACK

Let’s assume we have an empty table in the TestDB database with the following structure:

USE TestDB
GO

CREATE TABLE TestTable
(
ID INT PRIMARY KEY,
Val INT
)
GO

CREATE UNIQUE INDEX UIX_TestTable_Val ON TestTable (Val)
GO

There are transactions in the code sections below that will fail because the duplicate values are inserted:

Section 1

USE TestDB
GO

BEGIN TRY

BEGIN TRANSACTION

INSERT INTO TestTable(ID, Val)
VALUES(1, 100)

INSERT INTO TestTable(ID, Val)
VALUES(2, 100)

INSERT INTO TestTable(ID, Val)
VALUES(1, 100)

COMMIT

END TRY
BEGIN CATCH

IF XACT_STATE() = -1
BEGIN
ROLLBACK
PRINT 'Transaction is rolled back'
END

END CATCH

Section 2

USE TestDB
GO

BEGIN TRY

BEGIN TRANSACTION

INSERT INTO TestTable(ID, Val)
VALUES(1, 100)

INSERT INTO TestTable(ID, Val)
VALUES(2, 100)

INSERT INTO TestTable(ID, Val)
VALUES(1, 100)

COMMIT

END TRY
BEGIN CATCH

IF XACT_STATE() = 1
BEGIN
ROLLBACK
PRINT 'Transaction is rolled back'
END

END CATCH

Section 3

USE TestDB
GO

BEGIN TRY

BEGIN TRANSACTION

INSERT INTO TestTable(ID, Val)
VALUES(1, 100)

INSERT INTO TestTable(ID, Val)
VALUES(2, 100)

INSERT INTO TestTable(ID, Val)
VALUES(1, 100)

COMMIT

END TRY
BEGIN CATCH

IF XACT_STATE() > 0
BEGIN
ROLLBACK
PRINT 'Transaction is rolled back'
END

END CATCH

Section 4

USE TestDB
GO

BEGIN TRY

BEGIN TRANSACTION

INSERT INTO TestTable(ID, Val)
VALUES(1, 100)

INSERT INTO TestTable(ID, Val)
VALUES(2, 100)

INSERT INTO TestTable(ID, Val)
VALUES(1, 100)

COMMIT

END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK
PRINT 'Transaction is rolled back'

END CATCH

In three of these sections of code, the situation is correctly handled, allowing the transactions to be rolled back successfully. Which three?

Answer: 2, 3, 4

Explanation: In all four cases, the duplicate value violation is caught and handled by the CATCH block. Additionally, in all of these statements, the ROLLBACK command is under the IF condition. The IF condition is true in the second, third and fourth queries because there is an active transaction and, therefore, @@TRANCOUNT=1 and XACT_STATE()=1 (and obviously >0 ). In the first query, however, the IF condition is false. This is because XACT_STATE()=-1 when there is an uncommittable transaction. In our case, we do not have an uncommittable transaction. Thus, XACT_STATE() is not equal to -1. Consequently, the IF statement is false and, therefore, the ROLLBACK command is not issued in the first query, leaving the transaction open. Ref: https://dbprofi.com/xact_state-vs-trancount/

Discuss this question and answer on the forums

Here's a few of the new posts today on the forums. To see more, visit the forums.