Go to Top

Avoid number conflicts with SQL Sequences

Avoid number conflicts with 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:

Use the following statement to retrieve the next sequence number:

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:

To get the next value of a sequence from an Access VBA program, you can use the following statement to read the next value into an ADODB recordset.

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.

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.

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.)

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

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.

However the following will not work using parameters in a stored procedure.
So you need to construct the string statement with the parameter values pasted in.
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.

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

Blog082316Code2
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

About Robert Aaron

I have been working with Access since Version 2. I have always loved how flexible it is and can be adapted to so many situations. Since I joined the company a year ago I have expanded my skills into SQL Server. I think Access and SQL Server is a perfect combination for many things. Over the years I have also done programming in other areas including writing 3 computer games that have been published. I am continuing to expand my knowledge in Access and SQL Server, and I hope to bring you more interesting things along that journey.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us