T-SQL: Sequential Numbering

Introduction

It is as easy as 1, 2, 3. A generic numerical sequence which is incremented from a known value. In this article, we will look at the options that are available to create sequences which have primarily used identity, and more recently a new tool in the form of SEQUENCE  before finally looking at a custom solution.
 

1. IDENTITY (Property) (Transact-SQL)

The most familiar of concepts is that of the  IDENTITY (Property) (Transact-SQL) which can be specified on a column for a table. The code below shows how an identity property is applied with a seed value and an increment value. The example will start with an ID of 1 and then increment by 1 for each subsequent call, although these values can be changed as required.

DECLARE @d1 TABLE ([ID] BIGINT IDENTITY(1,1) PRIMARY KEY  CLUSTERED,[Name]VARCHAR(50));
INSERT INTO  @d1 ([Name]) VALUES  ('Jon');
INSERT INTO  @d1 ([Name]) VALUES  ('Jo');
INSERT INTO  @d1 ([Name]) VALUES  ('Bob');
SELECT * FROM @d1;

The 3 row insert above has occurred using the values 1,2 and 3.

ID

1

2

3

2. CREATE SEQUENCE (Transact-SQL)

Starting with SQL 2012 a new option was made available via a new construct called SEQUENCE (Transact-SQL). Unlike the identity property which is bound to a single column of a table, a sequence is a stand-alone object. This removes the coupling between a particular column of a table and allows the generation of sequences that could span multiple objects, or perhaps be cyclical in nature. 

A simple example is shown below to mimic the previous example, although there are several additional options that can be specified which affect not only the way the sequence behaves but also how it performs.

CREATE SEQUENCE  dbo.Seq2012 AS  BIGINT START WITH 1 INCREMENT BY 1;
--ALTER SEQUENCE dbo.Seq2012 RESTART WITH 1;--Restarting the sequence
DECLARE @d2 TABLE ([ID] BIGINT PRIMARY  KEY CLUSTERED,[Name] VARCHAR(50))
INSERT INTO  @d2 ([ID],[Name]) VALUES  (NEXT  VALUE FOR  dbo.Seq2012,'Jon');
INSERT INTO  @d2 ([ID],[Name]) VALUES  (NEXT  VALUE FOR  dbo.Seq2012,'Jo');
INSERT INTO  @d2 ([ID],[Name]) VALUES  (NEXT  VALUE FOR  dbo.Seq2012,'Bob');
SELECT * FROM @d2;

3. Custom Sequence

If more control is needed over a sequence then the most appropriate solution in some cases is to produce a custom solution. This will need a location to store the values and a way to increment. The simplest way to achieve this is a dedicated table and stored procedure.

The table is best kept to a minimum with regards implementation as we do not want to introduce any performance degradation. The procedure is an update of the stored value based on a passed increment, however, the update itself uses syntax which may seem a little strange. The UPDATE syntax documentation shows this as

  @variable = column = expression.

USE [tempdb];
GO
IF OBJECT_ID('dbo.NumberSequence', 'U') IS NOT NULL
DROP TABLE  dbo.NumberSequence;
GO
CREATE TABLE  dbo.NumberSequence(NextNumber BIGINT NOT NULL);
GO
--Seed the table with an initial value of 0, so that the first increment is a value of 1.
INSERT INTO  dbo.NumberSequence(NextNumber) VALUES(0);
GO
CREATE PROCEDURE  [dbo].[GetNextNumber] 
@NextNumber  AS  BIGINT OUTPUT, 
@IncrementBy  BIGINT  = 1 
AS
SET NOCOUNT ON;
UPDATE [dbo].[NumberSequence] 
SET @NextNumber = [NextNumber] = [NextNumber] + ISNULL(@IncrementBy,0); 
GO
--Incrementing the custom sequence.
DECLARE @NextNumber BIGINT
EXECUTE [dbo].[GetNextNumber] @NextNumber OUTPUT,1
SELECT @NextNumber

The above solution will allow the increment of a numerical value based on a stored procedure call, in which we could increase the value by 1 or 100. This enables ranges of numbers to be requested as well as individual values.

**4. No stored sequence **

Another alternative is to use the highest values + 1 as the next in the sequence. However, as this relies on getting the max value of what could be a large table this may have performance/concurrency implications if the corresponding inserts do not happen in a timely fashion.

DECLARE @d3 TABLE ([ID] BIGINT PRIMARY  KEY CLUSTERED,[Name] VARCHAR(50))
INSERT INTO  @d3 ([ID],[Name]) VALUES  ((SELECT  COALESCE(MAX([ID]),0)+1 FROM  @d3),'Jon');
INSERT INTO  @d3([ID],[Name]) VALUES  ((SELECT  COALESCE(MAX([ID]),0)+1 FROM  @d3),'Jo');
INSERT INTO  @d3 ([ID],[Name]) VALUES  ((SELECT  COALESCE(MAX([ID]),0)+1 FROM  @d3),'Bob');
SELECT * FROM @d3;