06 February 2014

SQL Server SEQUENCE Basics

The SEQUENCE statement introduced in SQL Server 2012 brings the ANSI SQL 2003 standard method of generating IDs. This is a great relief to database professionals as it solves some problems what are awkward to solve with the IDENTITY property. Joe Celko explains the basics of using a SEQUENCE

SQL Server now has the ANSI/ISO Standard CREATE SEQUENCE statement. Hooray! The bad news is that most programmers will not be aware of the significance of the underlying math. A SEQUENCE is not an IDENTITY. That propriety feature in T-SQL is a table property inherited from the old Sybase/UNIX days. It counts the number of insertion attempts, not even successes, on one particular machine. This goes back to how files were managed on UNIX systems in the 1970s. In those systems, you needed record numbers to locate the data, so T-SQL exposed this count as IDENTITY. Also, when a record was deleted, a bit flag on each record might be set to signal a garbage collection routine to remove this data during a file utility clean up.

Obviously, IDENTITY increments its value from physical insertion to physical insertion; no big jumps, no gaps. Again, IDENTITY is not a column; it is a table property. That means it cannot be a key by definition. Remember a key is a subset of columns that are unique in every row of a table. It is not an integer; you cannot do meaningful math on it. It is non-deterministic. If you insert a set of more than one row into a table, the rows will be inserted one at time in an order determined by the indexes, internal state of the hardware and number of sessions open at the time.

We have, for a long time, created a table of integers that has all sorts of uses. It starts at 1 and goes to some sufficiently large value. Its name depends on the author of the table. You will see “Numbers”, “Sequence”, “Tally_Table” and “Series” among others. Obviously, we can no longer use “Sequence”; it is a reserved word! I used it myself, and so I’ll have to re-write my books from now on. Darn. I always thought that “Numbers” was too generic; which numbers?? I never liked “Tally”, since a tally is a current score or amount, a running total, or count. It is not a set, but a process. I settled on “Series” for my stuff since a series is a completed (infinite) set, but it is not the right word.

Now, let’s do math.

What is the Difference Between a Sequence and a Series?

A sequence is a list of numbers. The order in which the numbers are listed is important, so for instance {1, 2, 3, 4, 5, …} is one sequence, and {2, 1, 4, 3, 6, 5, …} is an entirely different sequence. This is important. It means we can talk about the i-th member of the sequence.

A series is a sum of numbers. A classic example is: 1 + 1/2 + 1/4 + 1/8 + 1/16 + … which converge to 2 (or you can loosely say it will equal 2 at infinite). The order of terms can change the limit to which the series converges or it might not (“absolutely convergent”). Okay, too much math for a database article.

Sequences and series are closely related to each other. But a sequence is not summed.

Tag Numbers versus Sequence

A tag number is string, usually of fixed length, made up of digits. The digits are in sequence to make them easy to sort and to generate. Think of a serial number on a manufactured product. The most common example is a vehicle identification number (VIN). This is a unique code is used by the automotive industry to identify individual motor vehicles,  as defined in ISO 3833. The positions 12 to 17 are a sequence prefixed by codes for the Manufacturer Identifier, some vehicle attributes, a check digit, model year, and plant code.

The check digit is a major reason we like tag numbers. This is a topic in itself, but the idea is that we can take each digit in the tag number and run it thru a formula as an integer. We get a result, usually another digit, and we attach it to the tag number. For example, the Luhn algorithm is a common method

defined by ISO/IEC 7812-1. We like it because it is simple to put in hardware.

1.      Compute the sum of the digits .

2.      Take the units digit from this total.

3.      Subtract it from 10.

It’s not a great check digit, but it catches most of the common input errors – missing digits, extra digits, wrong digits and pairwise transposes.

Now let’s into the SQL.


This statement creates a schema level object that is accessible to any user. It is not part of a table. It is not a procedure that belong to one user. The idea is that a user can invoke the SEQUENCE with a special syntax that is used wherever an integer value of the SEQUENCE data type would work.

If you want a physical model, imagine you are in the butcher store. You walk in and pull a service ticket number from a roll of tickets on the counter. Sequence numbers are generated outside the scope of the current transaction, just like the tickets. The numbers are consumed whether the transaction using the ticket number is actually served (committed) or they walk out (rolled back).

I will explain the OVER() clause later; just keep the simple butcher store model in mind for now. Let’s look at the roll of tickets (i.e. CREATE SEQUENCE). It has to be declared with parameters that define behavior.

Let’s go thru the BNF in detail. The <sequence_name> and its qualifiers explains itself. This is pure SQL. Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction using the sequence number is committed or rolled back. Think about the butcher shop ticket that gets dropped on the floor.

A sequence can be defined as any integer type: That means TINYINT, SMALLINT, INTEGER and BIGINT; but it also allows DECIMAL (s, 0) and NUMERIC(s, 0) data types. You can also a user-defined data type that is based on one of the allowed types. Do not do that if you value portability.

If no data type is provided, BIGINT is the default. Do not use this unless you really need  a range of -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807), which makes ‘Big Data’ seem like note on the back of the hand. As with most defaults, this is the largest, safest value in the problem space.

This is the first value returned by the sequence. The START value must be a value between the minimum and maximum values. The default start value for a new sequence is the minimum value for an ascending sequence and the maximum value for a descending sequence. Note this is a constant; no function calls.

Value used to increment (or decrement if negative) the value of the sequence for each call to the NEXT VALUE FOR. If the increment is a negative value, the sequence is descending; otherwise, it is ascending. The increment cannot be 0, obviously. The default increment for a new sequence is 1. Again, this is a constant and not a function call.

Specifies the bounds for the sequence. The default minimum value for a new sequence is the minimum value of the data type of the sequence. This is zero for the TINYINT data type and a negative number for all other data types.

NO MINVALUE is probably a really bad design choice . You probably wanted zero or one.

Specifies the bounds for the sequence. The default maximum value for a new sequence is the maximum value of the data type of the sequence.

NO MAXVALUE is probably another bad design. If you are going to use this to create a tag number, you have to worry about overflowing the length of your string.

If you are casting the numeric data type to strings for tag numbers, you will want to be sure that the numbers do not overflow in the conversion. And you probably do not need the upper limit of a data type.

Property that specifies whether the sequence should restart from the minimum value (or maximum for descending sequences) or throw an exception when its minimum or maximum value is exceeded. The default cycle option for new sequences is NO CYCLE. Note that cycling restarts from the minimum or maximum value, not from the start value.

I am not going to explain Cache management. This is an implementation detail and I do programming.

In many financial applications, you cannot afford gaps. For example in Italy, invoice numbers have to be in a strict sequence by law. But you can lose all of the numbers in cache in the event of a major failure, so while it is faster, it is also dangerous. Remember that today “ROI” means “Risk of Incarceration”

OVER (ORDER BY ..) Clause

 Remember earlier in this article when I mentioned that how IDENTITY in assigned to each element of a a set insertion was not deterministic? SEQUENCE has a way to fix this problem; you can order the sequence! If another process is accessing the sequence object at the same time, the numbers returned could have gaps.

An OVER clause applied to the NEXT VALUE FOR function does not support the PARTITION BY or the [ROW | RANGE] subclauses for obvious reasons. The following additional rules apply when using the NEXT VALUE FOR function with the OVER clause:

Multiple calls to the NEXT VALUE FOR function for the same sequence generator in a single statement must all use the same OVER clause definition. Again, this is obvious.

Multiple calls to the NEXT VALUE FOR function that reference different sequence generators in a single statement can have different OVER clause definitions. They are separate schema objects.

If all calls to the NEXT VALUE FOR function in a SELECT statement specifies the OVER clause, an ORDER BY clause may be used in the SELECT statement. Again, the SEQUENCE is a separate schema object.

The OVER clause is allowed with the NEXT VALUE FOR function when used in a SELECT statement or INSERT ... SELECT ... statement. The NEXT VALUE FOR function is not allowed in UPDATE or MERGE statements.

Programming Examples

To get a feel for how this works, create a simple sequence and play with it. 

Now, just play with it. Just hit  ”SELECT NEXT VALUE FOR Invoice_Seq;” a few times, or in SSMS execute …

After you hit 99 and then invoke the next value, you will get this message.

We did not talk about this “restart” option yet. It is a straightforward ALTER statement.

 The WITH option lets you assign the new starting value. If you do not give a specific value, the default is the original starting value. In this example, we can use:

 this will return 3. Most of the situations where you want to re-start a sequence can be done with the CYCLE clause.

 The use of the ORDER BY clause can let you build groupings. Create a sequence groups of ten:

Now use it in a query that breaks the students into groups of ten

 Converting the sequence into, say, a four digit invoice serial number is pretty simple. It is important to use VARCHAR(n).

 SELECT ‘IN:’ + RIGHT (‘0000’ + CAST (NEXT VALUE FOR Invoice_Seq AS VARCHAR(5)), 4);

 But a fun example is that of using the same sequence in more than one table. Let’s go back the original service ticket example. When you come into the shop, you pull a ticket.  

We have two departments in this delicatessen, meats and fish. If you have been to  a Kosher market, you know that these two areas are kept separate. A ticket can be used in only one department.

 Now, let’ call the procedure a few times:

 And now let’s see how this works.













 If I UNION the two tables, I get a complete sequence and I am sure their intersection is empty. Doing this without a SEQUENCE is a lot harder. But I did resort to if-then-else flow control because I have two different tables. UGH!

I can put a SEQUENCE in the DEFAULT clause of the DDL for table:

Now play with this code.

 That is cool! But do not stop here. Let’s re-do the Meats and Fish tables with this feature, because we can then do inserts without the need for a procedure and still be sure of an empty intersection:

Now, to get you started,  try these statements. I dropped the kosher theme 🙂

 There are no guarantees as to how the sequence numbers will be assigned; it is pretty much first-come, first-served in the system. Did you notice that the sequences are the PRIMARY KEY?

will give us:

And this is all declarative code!

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 97762 times – thanks for reading.

  • Rate
    [Total: 90    Average: 4/5]
  • Share

Joe Celko

View all articles by Joe Celko

Related articles

Also in Basics

SQL Server System Functions: The Basics

Every SQL Server Database programmer needs to be familiar with the System Functions. These range from the sublime (such as @@rowcount or @@identity) to the ridiculous (IsNumeric()) Robert Sheldon provides an overview of the most commonly used of them.… Read more

Also in Database

Relational Algebra and its implications for NoSQL databases

With the rise of NoSQL databases that are exploiting aspects of SQL for querying, and are embracing full transactionality, is there a danger of the data-document model's hierarchical nature causing a fundamental conflict with relational theory? We asked our relational expert, Hugh Bin-Haad to expound a difficult area for database theorists.… Read more

Also in Learn SQL Server

SQL Server System Views: The Basics

When maintaining or refactoring an unfamiliar database, you'll need a fast way to uncover all sorts of facts about the database, its tables, columns keys and indexes. SQL Server's plethora of system catalog views, INFORMATION_SCHEMA views, and dynamic management views contain all the metadata you need, but it isn't always obvious which views are best to use for which sort of information. Many of us could do with a simple explanation, and who better to provide one than Rob Sheldon?… Read more

Also in SQL

The Comeback of Migrations-Based Deployments

With database deployments, not all script-based processes are equal. Some use change scripts in a free-and-easy way, and some, which are normally called 'migrations-based approaches', have more discipline around them. In this article, Redgate Product Manager Elizabeth Ayer covers 'migrations', and shows some of the benefits that have come with new tooling which is specifically designed to assist the change script processes.… Read more
  • Laren Hagen

    Quick Question
    Just a minor question to make sure I’m not missing something. When you say: "… a query that breaks the students into groups of ten.", do you mean "…a query that breaks the students into ten groups."?

    The way I’m reading the SQL is that the student_grouping column would get IDs from 1 to 10, based on the SEQUENCE (since it cycles). Therefore (assuming you have 10 or more students), you’ll get a total of ten groups.

    P.S. – Excellent article! Integrating SEQUENCE into DDL is something I hadn’t considered… looks very powerful (in the right hands!)

  • Celko

    I agree that ten groups is right ..
    Ten groups can be of any size, as written. Want to try to do it the other way, where each group has ten or fewer students? We will need a grouping sequence ..

  • Bruce W Cassidy

    No triggers!
    I always struggled with Oracle and sequences, as to use them for assigning a synthetic key, I also had to define a trigger (or include them in every DML). It seems that life has moved on, and the inclusion of sequences in DDL will make life a lot easier!

    Thanks Joe, a great introduction to the topic.

  • Anonymous

    Excellent article. It would have been helpful had you compared this to sequence in Oracle with an example.
    It would have highlighted the merits of sequence, as you have clearly illustrated, and weaknesses of Oracle sequences as is currently implemented.

  • puzsol

    From MSDN:

    Use sequences instead of identity columns in the following scenarios:
    a) The application requires a number before the insert into the table is made.
    b) The application requires sharing a single series of numbers between multiple tables or multiple columns within a table.
    c) The application must restart the number series when a specified number is reached. For example, after assigning values 1 through 10, the application starts assigning values 1 through 10 again.
    d) The application requires sequence values to be sorted by another field. The NEXT VALUE FOR function can apply the OVER clause to the function call. The OVER clause guarantees that the values returned are generated in the order of the OVER clause’s ORDER BY clause.
    e) An application requires multiple numbers to be assigned at the same time. For example, an application needs to reserve five sequential numbers. Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. Calling sp_sequence_get_range can retrieve several numbers in the sequence at once.
    f) You need to change the specification of the sequence, such as the increment value.

    I didn’t see a) or e) in the article anywhere, which I think is important to understand why you would use this…. Especially when you consider that using a sequence as a default value for a primary key does not stop someone from inserting or updating a row that does not come from the sequence… eg INSERT INTO Meats VALUES (18, ‘Cat’); is fine… until the sequence-primary-key gets to 18… MSDN suggests an update trigger to roll any changes back (ouch!)….identity locks it down much better than that.

    Not to mention that the Meats table is currently limited to 100 rows… do we add Tiger or Elephant in the last slot? I think for the most part I’ll be sticking with identity or row_number()

  • Celko

    More thoughts on SEQUENCE
    a) The application requires a number before the INSERT INTO the table is made.

    Frankly, I never understood this one. If I have a sequence element, it has to be somewhere. It might have a status of “to be issued”, “removed or recalled”, etc. But it has to exist in some sense. Status is another concept that translates “a state of being over a temporal duration”, and other problems (you cannot be “DEAD” before “BORN”, etc).

    Think about VIN numbers. They have a sequence subfield in them, which is tied to a particular auto factory (Factory X is expecting to produce 1700 Hupmobile sedans this year, numbered ‘0001’ to ‘1700’). If they fail to meet their quota, the application has no Factory X sedans to track after a certain number of actual vehicles. Deciding whether to give the unused numbers to Factory Y is easy with a SEQUENCE. In fact they could have been sharing this.

    If they meant that I need to do something to sequence element, like use it to add a check digit (I love check digits!), then I can do this in the insertion with an expression that has the “NEXT VALUE FOR MySeq” in it; the expression will be ugly. But one of the goals of declarative programming is to get rid of any local variables.

    e) An application requires multiple numbers to be assigned at the same time. For example, an application needs to reserve five sequential numbers. Requesting IDENTITY values could result in gaps in the series if other processes were simultaneously issued numbers. Calling Sp_Sequence_Get_Range can retrieve several numbers in the sequence at once.

    This one I should covered! The cache can drop values, so it is dangerous. The better way to allocate a block of sequence elements within a transaction.

    >> Especially when you consider that using a sequence as a default value for a primary key does not stop someone from inserting or updating a row that does not come from the sequence… eg INSERT INTO Meats VALUES (18, ‘Cat’); is fine… until the sequence-primary-key gets to 18… MSDN suggests an update trigger to roll any changes back (ouch!) … IDENTITY locks it down much better than that. <<

    I find the automatic limits and checking for duplicate values to be a huge advantage.

    I hate IDENTITY since it is a physical count of insertion attempts, and has nothing whatsoever RDBMS. It is magnetic tape files on disk. UGH!!

    I hate triggers, too. More procedural code shoved into my beautiful declarative language for historical reasons.

    A sequence can be part of a PRIMARY KEY, but not the key itself. Again, back to the VIN, the sequence is a subfield. The sequence number on my check is meaningless without my account number.

  • Anonymous

    You said:

    "A sequence can be part of a PRIMARY KEY, but not the key itself. Again, back to the VIN, the sequence is a subfield. The sequence number on my check is meaningless without my account number."

    But in you code above you have:

    "ticket_seq INTEGER DEFAULT NEXT VALUE FOR Service_Ticket_Seq PRIMARY KEY"

    That looks like the SEQUENCE *is* the PRIMARY KEY. Am I missing something?

  • Celko

    I agree
    I was sloppy and trying to keep the skeleton code short.

    We do not do math on a ticket number, so it needs to be cast as a string. This is what the " RIGHT (‘0000’ + CAST (NEXT VALUE FOR <something>_Seq AS VARCHAR(<n+1>)), <n>)" trick is for.

    But the grouping sequence number does do math, like MOD(), division, addition, etc.

  • Anonymous

    Surrogate Keys
    I’m working with a data warehouse and need surrogate keys for a lot of tables. That should be obvious.

    These keys ‘mean’ nothing besides making the record unique within the table. Gaps don’t matter, uniqueness across tables doesn’t matter, etc.

    Where does SEQUENCE give me any benefit over IDENTITY?

  • Anonymous

    Surrogate keys in dwh
    Hi anonymous 🙂

    In a DWH I would use the sequence the same way as with invoices: to guarantee that your audit trail doesn’t have gaps in it. So your audit ID’s would be sequences and any issues could be spotted quickly.

  • Anonymous

    Another use for SEQUENCE
    An unmentioned and very useful application of the SEQUENCE would be as a surrogate key in a partitioned table, especially if its windowed and the partitions are being swapped in and out.
    We have a few instances of this application of SEQUENCE. During implementation we initially tried using this as the default in DDL for the tables and in a high volume application had issues with the sequence number coming back. This was resolved calling the NEXT VALUE FOR in a procedure

  • JNeville

    Typo in line 3
    Should "propriety" be "proprietary"?

    Otherwise it seems to imply that Microsoft’s continuted support of IDENTITY is "appropriateness to the purpose or circumstances"*

    Which turns the whole article on its head 😉


    * the definition of "propriety" from dictionary.com

  • wbiesty

    Prime Number Increments and Cycling
    In order to achieve "GUID Like" integer behavior in past systems I’ve worked on, I wanted a cyclical sequence like the following, using SQL Server 2012 Syntax:

    create sequence PROD.IDS start with 1 increment by 13 maxvalue 50 cycle;

    Numbers are small for comprehension purposes. 13 was specifically chosen as a prime number.

    My expectation is that the first few sequence numbers are 1, 14, 27, 40. What’s next? It can’t be 53, that’s over the max.

    Is it 1 again? or preferably 4 [(53 – 50) + 1], ie "next" number minus max number adding the difference to the min number.

    Thus allowing 13 cycles through the 50 numbers. What is the behavior? All your examples use increments of one only.

  • harveysburger

    about this comment:
    "NO MAXVALUE is probably another bad design. If you are going to use this to create a tag number, you have to worry about overflowing the length of your string."

    I understand the thinking there but from what I can tell if you set NO MAXVALUE internally the maximum value for the type will be used. If you script an integer sequence that was created with NO MAXVALUE you’ll see…
    MAXVALUE 2147483647

    So I guess NO MAXVALUE is fine… you won’t overflow more or less than if you explicitly set 2147483647


    Teaching all over the world
    ‘He has taught SQL in the US, UK, the Nordic countries, South America and Africa.’

    Were you banned from Europe and/or Australia?

  • Celko

    Europe and/or Australia?
    I need to update that; I did a class in Munich, Germany last year. Switzerland for conference decades ago (no classes).

    But I would love to get Australia; nobody has asked me yet (hint, hint).

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up

See what's happening behind the scenes

Take a peek at the bowels of the ship – the lower decks – the actual servers of SQL Server Central itself.

See what's happening