Listing 1: Code That Inserts a Row and Retrieves New IDENTITY Value

DECLARE @i AS INT;
BEGIN TRAN;
SAVE TRAN S;
INSERT INTO Seq2 DEFAULT VALUES;
SET @i = SCOPE_IDENTITY();
ROLLBACK TRAN S;
COMMIT TRAN;
SELECT @i;