Avoid Number Conflicts with Microsoft SQL Sequences

Note: I’ll be presenting on this topic at the Access with SQL Server group online. Please join me on September 13th at 6:30 PM CST, join the group to get an email with all of the meeting details, it’s free!

• Do you need to guarantee that a number in a field will only be used once and never duplicated by another user?
• Have you had a situation where you needed more than one autonumber in a table?
• Have you ever needed a lower and an upper limit of sequential numbers, and you could not go beyond it?
• Do you sometimes have a list of numbers that you want to recycle after you get past the last one?

In SQL Server, there is a feature that can handle this quite easily, and it is called a sequence. It is available starting in SQL Server 2012.

Like an autonumber, it can assure that a unique number will be given out each time, unless it recycles.

Recently I was asked to implement a sequence for a client, where multiple users will create new records and have to “fetch” the next number in a specific sequence. We could not use an autonumber because the customer was limited to a certain range, not to exceed an upper threshold. When the numbers where exhausted, management would replenish the sequence anew.

Why using an Access table does not work

Prior to upgrading to SQL Server, users would share a table that would keep tabs on what is the next number to use, the problem with this approach is that it’s not fool proof, two users may request the same number at the exact same time, violating the business rule.

Creating and using a SQL Server Sequence

Before you can use a sequence, it must be created with the following syntax in SQL Server, you only need to do this once:
CREATE SEQUENCE dbo.seqPolicyNumber AS int MIN 50005000 MAX 50005999;
Use the following statement to retrieve the next sequence number:
SELECT NEXT VALUE FOR dbo.seqPolicyNumber as NextValue
Your users will need update permissions to use the sequence, but they should not be able to alter the range of the sequence. Update permissions can be given using this syntax:
GRANT UPDATE ON dbo.seqPolicyNumber TO [MyDatabaseUserOrRole];
To get the next value of a sequence from a Microsoft Access VBA program, you can use the following statement to read the next value into an ADODB recordset.
strSQL = "SELECT NEXT VALUE FOR dbo.seqPolicyNumber as NextValue"
OpenMyRecordset rs, strSQL
NextValue = rs("NextValue")
This is how we typically open an ADODB recordset in our firm. For more information on how you can use OpenMyRecordset, you can click on another article in our blog:

Easy ADODB Recordsets and Commands in Access

The nice thing about the syntax to get the next sequence number is that it is very easy to use in T-SQL. You just substitute NEXT VALUE FOR <sequence name> where you would normally get a value from a field name, parameter, or a constant. The following shows how it can be used in an Insert statement.
INSERT dbo.Orders (OrderID, Name, Qty)
VALUES (NEXT VALUE FOR dbo.OrderNumberSequence, 'Tire', 2);

More flexibility than Autonumber

A sequence can offer more flexibility than an autonumber in Access, or an IDENTITY field in SQL Server. First, you can only have one autonumber or identity field in a table. Although you can reseed an IDENTITY field, you cannot recycle values. IDENTITY fields are still useful for primary keys, when we want some arbitrary number to identify the record, and it has no meaning. Sequences ranges however can have embedded meaning.

You are also not restricted to using integers like an IDENTITY, but sequence numbers can also be decimal or numeric. Also you can increment down in your sequence instead of just up.

Also a sequence is not tied to any specific table and can be used across tables as new sequence numbers are needed for a particular table.

Replenish the Sequence

When you want to change the range for a sequence, like when you need a new range of policy numbers, it should be done with a stored procedure. The following is a stored procedure that can do this.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_AlterPolicySequence] (
@SeqName AS sysname,
@InpMin AS int,
@InpMax AS int
) WITH EXECUTE AS OWNER AS
BEGIN
SET NOCOUNT ON;

DECLARE @sql nvarchar(MAX),
@err nvarchar(MAX);

IF NOT EXISTS (
SELECT NULL
FROM sys.sequences AS s
WHERE s.name = @SeqName
AND s.schema_id = SCHEMA_ID(‘dbo’)
)
THROW 50000, ‘The sequence name is not valid.’, 1;

IF @InpMin IS NULL OR @InpMax IS NULL
THROW 50000, ‘The values cannot be null.’, 1;

SET @sql = CONCAT(N’ALTER SEQUENCE [dbo].’, QUOTENAME(@SeqName), N’ RESTART WITH ‘, @InpMin, N’ INCREMENT BY 1′, N’ MINVALUE ‘, @InpMin, N’ MAXVALUE ‘, @InpMax, N’ NO CYCLE NO CACHE;’);
EXEC sys.sp_executesql @sql;

;

END

There are some things worth noting in this stored procedure. First we are running it
WITH EXECUTE AS OWNER AS.

We do not want the everyday user to be able to alter a sequence. But we want to give them limited capability to alter it only through a stored procedure. (Users only need rights to the stored procedure.)
GRANT EXECUTE ON dbo.usp_AlterPolicySequence TO [MyDatabaseUserOrRole];
This stored procedure can be run from an Access front end, whenever a new range in the sequence needs to be installed, and that would normally be by an admin user, who might have more SQL Server privileges than a normal user.

However this stored procedure could also be run when a new range of numbers is waiting to be loaded into the sequence, right after the current sequence is used up. In this case the stored procedure could be called by any user who needs the first policy number for the new range. So we use WITH EXECUTE AS OWNER AS to give them more rights just for this limited use.

Another thing to notice is that it is necessary to construct a SQL string, and then use
EXEC sys.sp_executesql


on that string, if we are using parameters.

The following statement will work if typed into an SSMS query window, or used in a stored procedure.

ALTER SEQUENCE dbo.seqPolicyNumber
RESTART WITH 50005000
INCREMENT BY 1
MINVALUE 50005000
MAXVALUE 50005999
NO CYCLE
NO CACHE
However the following will not work using parameters in a stored procedure.
ALTER SEQUENCE dbo.seqPolicyNumber
RESTART WITH @InpMin
INCREMENT BY 1
MINVALUE @InpMin
MAXVALUE @InpMax
NO CYCLE
NO CACHE
So you need to construct the string statement with the parameter values pasted in.
SET @sql = CONCAT(N'ALTER SEQUENCE [dbo].', QUOTENAME(@SeqName), N' RESTART WITH ', @InpMin, N' INCREMENT BY 1', N' MINVALUE ', @InpMin, N' MAXVALUE ', @InpMax, N' NO CYCLE NO CACHE;');

EXEC sys.sp_executesql @sql;
This string @sql is constructed using the functions CONCAT and QUOTENAME. It will also work if you used plus signs to make your final string, but it is better to do it like the example which is Null safe.

This stored procedure will produce (throw) an error if you provide missing or bad values, and you won’t be allowed to continue. It will automatically generate an error if all the sequence numbers are used up.

Your front end Access procedure should check to see that an error has not occurred, which should only occur if the sequence runs out of numbers, if you are providing proper parameter inputs. If an error is seen, then the front end will need to cancel its operation somehow.

There are some other capabilities you can set with arguments. CYCLE will allow the sequence to cycle again after it reaches the end, and then go to the MINVALUE. You can even explicitly restart it in the middle of a sequence by giving it a RESTART value.

You can also give it a CACHE, for example you can ask for 50 sequence numbers at a time, and it updates the system sequence tables once every 50 numbers, which can be faster, but it also adds a risk if there is a power failure, since these numbers cannot be re-used

The last thing worth noting in this stored procedure is that you can pull information (meta-data) about your sequences from a system view called sys.sequences. It contains the following information.

Some useful columns you might like to read and convey to a user are minimum_value, maximum_value, and current_value.

If you are interested, the following pages on MSDN have very useful information on sequences.

Sequence Numbers
Describes sequences and has very good examples for typical use

CREATE SEQUENCE (Transact-SQL)

ALTER SEQUENCE (Transact-SQL)

NEXT VALUE FOR (Transact-SQL)

sys.sequences (Transact-SQL)
Describes the meta-data you can query on for on your sequences