SQL Server does not support a true sense of nested transactions. When you issue a ROLLBACK TRAN command within a transaction, SQL Server rolls back all activity performed as of the outermost BEGIN TRAN. If you issue a BEGIN TRAN statement within an existing transaction, you don’t really open a new transaction. Rather, SQL Server simply increments an internal counter that you can query via the @@trancount function. A COMMIT TRAN statement decrements the counter by one, and only the outermost COMMIT TRAN, which decrements the counter to zero, really commits the transaction. SQL Server will limit the number of levels you can open with BEGIN TRAN statements to 32.

SQL Server supports save points, which allow you to undo some partial activity within a transaction. To do so, you need to mark a save point by issuing a SAVE TRAN <savepoint name> statement and later issue a ROLLBACK TRAN <savepoint name> to undo the activity that was performed as of that save point.

As an example, remember that in the previous chapter I demonstrated how you can maintain your own custom sequence (autonumbering mechanism). I demonstrated a solution that maintains a synchronous sequence. That is, when a transaction modifies the sequence value to increment it, it acquires an exclusive lock and keeps it until the transaction terminates. Other transactions attempting to increment the sequence value will be blocked–in other words, the synchronous sequence queues requests for new sequence values. That’s exactly what you want to establish in some cases where you want to prevent gaps in the sequence–for example, when you use the sequence to generate invoice IDs.

There might be cases, though, in which you don’t care about gaps in the sequence and simply want to generate unique values–for example, to maintain keys across tables that would not overlap. In these cases, you’re after an asynchronous sequence that will not block. You want the sequence value to be locked for a fraction of time so that you can increment it and prevent multiple processes from acquiring the same sequence value. But you don’t want it to be locked for the duration of the whole transaction.

You can achieve such a sequence by creating a sequence table with an IDENTITY column like this:

USE tempdb;
GO
IF OBJECT_ID('dbo.AsyncSeq') IS NOT NULL
  DROP TABLE dbo.AsyncSeq;
GO
CREATE TABLE dbo.AsyncSeq(val INT IDENTITY);

Remember that an IDENTITY value increment is not considered part of an external transaction. That is, if within a transaction you insert a row to a table with an IDENTITY column, the identity increment is not rolled back if the transaction rolls back. Furthermore, the identity resource is not locked past the individual increment. To obtain a new sequence value, you simply load a row to the AsyncSeq table and return the value from the SCOPE_IDENTITY function. At that point, you have an asynchronous sequence.

However, keep in mind that the sequence table will keep on growing larger and larger. From time to time, you will probably want to clean it. If you clean it with a TRUNCATE TABLE command, the IDENTITY seed will be reset, which is not a good thing. If you clear it with a DELETE statement, the activity will be fully logged; therefore, it will take a while. During the deletion, SQL Server most likely will escalate the fine-grained exclusive locks to a full-blown exclusive table lock. This means that during the clearing activity, processes will not be able to obtain new sequence values.

You can use save points to get around this issue. Run the following code to create the stored procedure usp_AsyncSeq, which solves the problem:

IF OBJECT_ID('dbo.usp_AsyncSeq') IS NOT NULL
  DROP PROC dbo.usp_AsyncSeq;
GO
CREATE PROC dbo.usp_AsyncSeq
  @val AS INT OUTPUT
AS
BEGIN TRAN
  SAVE TRAN S1;
  INSERT INTO dbo.AsyncSeq DEFAULT VALUES;
  SET @val = SCOPE_IDENTITY()
  ROLLBACK TRAN S1;
COMMIT TRAN
GO

The stored procedure’s code issues a BEGIN TRAN statement so that it can define a save point. The code defines the save point S1 and inserts a row into the AsyncSeq table, generating a new IDENTITY value. The code continues by assigning the newly generated IDENTITY value (via the SCOPE_IDENTITY function) to the output parameter @val. The code then issues a rollback to the save point S1. The rollback will not affect an external transaction if one was open when the procedure was invoked, because it reverts to the save point. The code finally issues a COMMIT TRAN statement that doesn’t have any changes to commit, but just terminates the BEGIN TRAN statement.

Whenever you need to get a new sequence value, invoke the usp_AsyncSeq procedure, which returns the output parameter’s value to a local variable like so:

DECLARE @key AS INT;
EXEC dbo.usp_AsyncSeq @val = @key OUTPUT;
SELECT @key;

As you can see, save points can really come in handy.