Click here to Skip to main content
15,868,016 members
Articles / Database Development / SQL Server

Flexible numbering sequences in T-SQL

Rate me:
Please Sign up or sign in to vote.
4.00/5 (12 votes)
19 Apr 2018CPOL5 min read 50.4K   353   44   16
A flexible way to generate unique numbers for things like invoice numbers

Introduction

Often, an application needs a robust way to generate unique numbers. A few examples would be invoice numbers, order numbers, and donation receipt numbers. These numbering sequences often contain a non-numeric part, for example, online donation receipt numbers like 'WD-00000121'.

Background

In the 'old' days (when we used paper), there were pads of paper pre-printed with a sequence of numbers, e.g., bank account cheques. When you pull off the top-most sheet (or cheque), you are effectively claiming that sequence number--you remove it from the pad, and no one else can use it. In this flexible SQL solution, when we claim the next number in the sequence, we are essentially doing the same thing.

There are several ways to solve this in an application. I do not know all of them, but I have seen a few good ones. In this article, I will show a simple and a more flexible way.

One Caveat

Although the code for the article is written for Microsoft SQL Server TSQL, this technique can be applied to most modern relational databases. For example, Oracle has a feature called 'Sequence' which is good but is not covered in this article (I have used the Oracle database product and have great respect for it as a product, but of late, all my work has been in MS SQL Server).

The Simple Solution

A simple solution is to insert into a table that has an IDENTITY column defined, and SQL Server will automatically assign the next number in the sequence to your new record, e.g.:

SQL
--This is partial code, do not try to execute this snippet of code it is just for 
illustration purposes and is not part of the 'flexible solution':

CREATE TABLE WebDonation (
       DonationNumber INT NOT NULL IDENTITY(1,1),
       DonationDate DATETIME NOT NULL DEFAULT(GETDATE()),
  …
)

The advantage of this sequence is that it is automatic, easy to implement, and guaranteed by SQL Server to be unique. I have nothing against using IDENTITY columns per se; and although I have used them often where it made sense to, I do prefer a meaningful, human-readable, human-relevant key when possible.

The disadvantages with using an IDENTITY column here is that the numbering cannot be controlled by the administrators of the application, and it is an internal number that is not meaningful to the users.

Also, for example, if the application required that online donation receipt numbers begin at 00001 every month, an identity column would be hard to use; e.g., February 2009's donations would begin at "W-200902-00001", March 2009's would begin at "W-200903-00001".

The Flexible Solution

A more flexible solution to this problem would need to maintain the actual sequence outside of the table where it is being used. Some meta data for the sequence would also need to be stored somewhere. Another requirement for some applications is that they need more than one sequence or type of sequence.

To do this, create a table as follows:

SQL
CREATE TABLE SequenceControl (
        SequenceKey      VARCHAR(25) NOT NULL PRIMARY KEY CLUSTERED,
        LastSequence     INT NOT NULL 
                         CHECK ( LastSequence<2000000000 AND LastSequence > -1),
        SequenceFormat   VARCHAR(20) NOT NULL DEFAULT('[#]')
                         CHECK ( CHARINDEX( '[#]', SequenceFormat)>0),
        ZeroPadToDigits  INT NOT NULL DEFAULT(0) 
                         CHECK ( ZeroPadToDigits>-1 AND ZeroPadToDigits<11),
        IncrementBy      INT NOT NULL DEFAULT(1) CHECK ( IncrementBy>0),
        LongDescription  VARCHAR(200) NULL
)

First, I want to point out that this table's DDL (above) contains some business rules that I applied, such as:

  • sequence numbers cannot be less than zero
  • sequence numbers cannot be more than two billion, and
  • the final output has a maximum of 20-3+10=27 characters in length ([#], the Stored Procedure CASTs this as a VARCHAR(30)).

Your requirements may differ, and so you would need to make those adjustments to the table's DDL and to the Stored Procedure.

Explanation of the SequenceControl Table

The first column, "SequenceKey", is the unique identifier for a sequence; this table can hold the sequence definitions for any number of sequences.

The column "LastSequence" holds the last sequence number that was used. It gets incremented in the Stored Procedure when it calculates and then claims the next number. To start a sequence off at 1 (and incrementing by 1), the initial value would be zero.

The column "SequenceFormat" contains a pattern for how we want this sequence to look in the end. It can contain a number of replaceable tags. It is mandatory to have the "[#]" tag because this is where the incrementing integer will be placed. Other possible tags that you could be added are date and time parts or the SQL User.

The column "ZeroPadToDigits" tells the Stored Procedure how many digits to make the incrementing number, padded on the left with zeros; e.g., an eight digit zero padded sequence would be "00000001", "00000002", "00000003", etc...

Using the example of online donation receipts, we would start our sequence like this:

SQL
INSERT INTO SequenceControl ( SequenceKey, LastSequence, SequenceFormat, ZeroPadToDigits)
VALUES ( 'online donation', 0, 'WD-[#]', 8)

This would produce the first donation receipt in the sequence: "WD-00000001".

The Stored Procedure code is:

SQL
SET QUOTED_IDENTIFIER OFF

GO 
SET ANSI_NULLS OFF
GO 

CREATE PROCEDURE DBO.p_GetNextInSequence 
    @SequenceKey VARCHAR(20) -- e.g. 'online donation'
AS 
BEGIN 
SET NOCOUNT ON 
DECLARE @LASTNUMBER INT
DECLARE @NEXTNUMBER INT
DECLARE @FORMATTEDNUMBER VARCHAR(40)
DECLARE @MYKEY VARCHAR(30)
--if the sequence does not exist, we return a '' and zero
IF NOT EXISTS(SELECT 'X' 
              FROM SequenceControl 
              WHERE SequenceControl.SequenceKey = @SequenceKey )
BEGIN 
    SELECT    @SequenceKey AS SequenceKey, 
              CAST('' AS VARCHAR(30)) AS NextSequenceFormatted, 
              CAST(0 AS INT) AS NextSequenceInt 
    RETURN 
END 

DECLARE @SEQFORMAT VARCHAR(30), @IncrementBy INT, 
                   @IncrementStep INT, @zeropadtodigits INT

SELECT @SEQFORMAT = RTRIM(LTRIM(SequenceFormat)),
       @IncrementBy = IncrementBy, 
       @zeropadtodigits = ZeroPadToDigits 
FROM    SequenceControl 
WHERE    SequenceControl.SequenceKey = @SequenceKey 

DECLARE @THEROWCOUNT INT 
SET @THEROWCOUNT    = 0 

BEGIN TRANSACTION T1
    WHILE ( @THEROWCOUNT =0 ) 
    BEGIN 
        SELECT   @LASTNUMBER= LastSequence 
        FROM     SequenceControl
        WHERE    SequenceControl.SequenceKey= @SequenceKey 

        UPDATE     SequenceControl 
        SET        LastSequence = @LASTNUMBER + @IncrementBy 
        WHERE      SequenceKey = @SequenceKey AND    
                   LastSequence = @LASTNUMBER --this guarantees that no one has
                                              --updated it in the meantime 

        SELECT @THEROWCOUNT = @@ROWCOUNT
        --if its zero, then we need to get the next number after that and try again 
    END 
COMMIT TRANSACTION T1 

--here, we format the number according to the pattern for this sequence 
DECLARE @FMTNUM VARCHAR(20) 
SET @NEXTNUMBER = @LASTNUMBER + @IncrementBy 
IF ( @zeropadtodigits>0) 
    SET @FMTNUM = RIGHT( REPLICATE('0', @zeropadtodigits) + 
                    CAST(@NEXTNUMBER AS VARCHAR(20)), @zeropadtodigits) 
ELSE 
    SET @FMTNUM = CAST(@NEXTNUMBER AS VARCHAR(10)) 


SET @FORMATTEDNUMBER = REPLACE(@SEQFORMAT,'[#]', @FMTNUM ) 

SELECT    @SequenceKey AS SequenceKey, 
          CAST(@FORMATTEDNUMBER AS VARCHAR(30)) AS NextSequenceFormatted, 
          @NEXTNUMBER AS NextSequenceInt 

END 

GO 
SET QUOTED_IDENTIFIER OFF
GO 
SET ANSI_NULLS ON    
GO

If we need to produce a new sequence every month, we would code it to attempt an INSERT into the SequenceControl table for that month (e.g., for February 2009), and then would proceed to use it.

SQL
INSERT INTO SequenceControl ( SequenceKey, LastSequence, SequenceFormat, ZeroPadToDigits)
VALUES ( 'donations 2009 02', 0, 'WD-200902-[#]', 8)

For this example (every month, a sequence from 00001), it would not matter whether or not the INSERT failed because of a primary key violation; either way, the key would be in the table and we would just use it:

SQL
EXEC DBO.P_GetNextInSequence 'donations 2009 02';

Points of Interest

It would be easy now to create a UI to administer the sequences; one UI could be used to maintain all the sequences in the application. The person administering them could choose at what number to begin.

The Stored Procedure can also be refactored into a database function (UDF) that simply returns the final formatted, varchar sequence number. This would be useful if you need to claim the next number from within your SQL code, for example, if you have a Stored Procedure to add the new donation instead of from [your favorite programming language] code.

Inside a UDF, one cannot make changes to the database, so this stored procedure cannot be used as a database user defined function. Apparently, there are hacks for doing this anyways, but as hacks go, it is probably a bad idea for many reasons to defeat this rule in SQL Server. Thank you to drbarriesmith for asking a good question.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Canada Canada
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralI Prefer generated primary keys Pin
Klaus Luedenscheidt19-Apr-18 19:42
Klaus Luedenscheidt19-Apr-18 19:42 
QuestionAbout concurrency Pin
bluland2-Jun-14 5:22
bluland2-Jun-14 5:22 
AnswerRe: About concurrency Pin
Michael Abramovitch2-Jul-14 16:06
Michael Abramovitch2-Jul-14 16:06 
SuggestionIdentity hard to use for monthly change to identity number Pin
Member 883399719-Nov-12 6:00
Member 883399719-Nov-12 6:00 
GeneralRe: Identity hard to use for monthly change to identity number Pin
Michael Abramovitch21-Nov-12 12:48
Michael Abramovitch21-Nov-12 12:48 
QuestionConvert Stored Procedure into Function Pin
drbarriesmith13-Nov-12 4:14
drbarriesmith13-Nov-12 4:14 
AnswerRe: Convert Stored Procedure into Function Pin
Michael Abramovitch13-Nov-12 12:14
Michael Abramovitch13-Nov-12 12:14 
GeneralRe: Convert Stored Procedure into Function Pin
drbarriesmith13-Nov-12 22:05
drbarriesmith13-Nov-12 22:05 
GeneralMy vote of 5 Pin
RaymondMusa12-May-12 21:53
RaymondMusa12-May-12 21:53 
GeneralGreat Work Pin
Kavan Shaban28-May-09 20:39
Kavan Shaban28-May-09 20:39 
GeneralRe: Great Work Pin
Jasmine250119-Nov-12 8:13
Jasmine250119-Nov-12 8:13 
GeneralRe: Great Work Pin
Michael Abramovitch21-Nov-12 13:36
Michael Abramovitch21-Nov-12 13:36 
GeneralRe: Great Work Pin
Jasmine250126-Nov-12 5:31
Jasmine250126-Nov-12 5:31 
GeneralRe: Great Work Pin
Michael Abramovitch26-Nov-12 8:14
Michael Abramovitch26-Nov-12 8:14 
GeneralRe: Great Work Pin
Jasmine250126-Nov-12 8:46
Jasmine250126-Nov-12 8:46 
GeneralCombining identity with an offset Pin
supercat911-Feb-09 6:21
supercat911-Feb-09 6:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.