T-SQL SEQUENCE – An Application

Background

A customer requires product to be identified using serialized labels.  The current front end application only provides an interface to generate labels with product information using Crystal Reports as the underlying template for printing.  The customer requires the serial number to serve as a lot identification number for traceability purposes.

The Challenge

We are presented with two challenges.  (1) We need to integrate a SEQUENCE routine to generate unique serial numbers for each label and (2) we need to associate the products to their assigned serial number.

Situational Analysis

The front end is a third party commercial application.  After contacting the company, I learned that the ability to serialize labels won’t be added any time in the near future.  However, we can modify the label’s Crystal Report file by linking to an external SQL command that will generate a unique serial number and that can also use the data that is fed to the Crystal Report template to populate the columns of a database table.

Creating Sequences

Fortunately, starting with SQL Server 2012, we can easily generate a unique SEQUENCE number.  The SEQUENCE object will serve as our serial number generator.  The article titled “CREATE SEQUENCE (T-SQL)” on Microsoft’s web page offers more than enough information to start using the SEQUENCE object in our code.  The syntax is defined as follows:

CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH ] -- specify starting value 1
[ INCREMENT BY ] -- can be +ve or -ve values
[ { MINVALUE [ ] } | { NO MINVALUE } ] -- recommend at least 0 or 1
[ { MAXVALUE [ ] } | { NO MAXVALUE } ] -- specify to prevent overflow
[ CYCLE | { NO CYCLE } ] -- restarts at MINVALUE not START WITH
[ { CACHE [ ] } | { NO CACHE } ] -- automatically sized unless specified
[ ; ]

Another article, “SQL Server SEQUENCE Basics“, by Joe Celko offers unique tips and also demonstrates how to work with the SEQUENCE object in T-SQL.

Next Steps

Create a sequence number generator for our labels that can be linked to the database table used by Crystal Reports.  Using Microsoft’s SQL Server Management Studio, create a new query to enter the following code:

CREATE SEQUENCE dbo.LabelNo
AS int                  -- bigint is default value
START WITH 1            -- +ve or -ve
INCREMENT BY 1          -- +ve or -ve
MINVALUE 1              -- +ve or -ve
MAXVALUE 9999999        -- +ve or -ve
CYCLE                   -- NO CYCLE
CACHE 30;               -- NO CACHE


When creating a SEQUENCE, don’t rely on “system” defaults.  SEQUENCE can generate extremely large (bigint) and relatively small (tinyint) number ranges.  Select a range that is practical for your application.

Once the SEQUENCE is created, we can use the NEXT VALUE FOR dbo.LabelNo to get the serial number for our label.

Now we need to create a stored procedure that can be executed by Crystal Reports to update our “LabelNo” table from the parent report and also return the required serial number for the label as provided by the front end software application.

We’ll keep you posted …