Denali from McKinley Princess LodgeThe latest installment of Microsoft’s SQL Server database management system, codenamed Denali, is in the community technology preview stage, where it is available to developers for review.

Among the numerous new features of what will presumably eventually become SQL Server 2011 is the addition of “sequences”.  (In fairness, we would be remiss to not point out that Oracle has had sequences since SQL Server was only a gleam in the eye of Mr. Gates.)

In database parlance, a sequence is a server-side entity that provides an automatically incrementing number upon request.  This is a distinct concept from an “identity”, which is a unique automatically incrementing ID field for a particular table.  Sequences can be used across tables (for example, where you may wish to have a unique number for every item in inventory, even though sales inventory and internal use inventory are stored in separate tables).  Sequences can even be selected without actually creating a row (for example, you may need to reserve a customer ID before a customer completes the signup process).

A common need in a transactional system is to generate a “unique per day” record number.  For example, invoices may be numbered with the date followed by a sequential number, e.g. 2011010100001. Sequences now give us a non-messy way to accomplish this task.  First, we will create the sequence:

CREATE SEQUENCE invoicecounter
START WITH 1
INCREMENT BY 1

When we are ready to create an invoice, we select the next value for the sequence:

SELECT NEXT VALUE FOR invoicecounter

Nightly at midnight, we reset the sequence for the next day:

ALTER SEQUENCE invoicecounter
RESTART WITH 1

This, and other tips, tricks and special methods, are only a small sample of how our software application engineers can help make your applications easier to use for your staff and clients. Talk to our eBusiness Solutions team today.

image source: nic mcphee on flickr

Enjoy Your 30-day Free Trial!
inbound marketing software free trial Learn how Inbound Marketing Software can help your business grow!

Click here to get started with your free trial.

2 Comments

  1. Tweets that mention SQL Server Denali - Sequences | JASE Digital Media blog -- Topsy.com

    […] This post was mentioned on Twitter by JASE Digital Media. JASE Digital Media said: SQL Server Denali – Sequences http://bit.ly/hKD2GD […]

  2. SQL Server Denali – Sequences | JASE Digital Media blog

    […] is in the community technology preview stage, where it is available to developers for review :: http://blog.jasedigitalmedia.com/index.php/2011/01/12/sql-server-denali-sequences :: Among the numerous new features of what will presumably eventually become SQL Server 2011 is the […]

Comments are closed.