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	Name
1	Jon
2	Jo
3	Bob

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 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 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;

http://social.technet.microsoft.com/wiki/contents/articles/25552.t-sql-sequential-numbering.aspx

T-SQL: Sequential Numbering

Introduction

Comments

Leave a Reply Cancel reply