Rotating Header Image

SEQUENCE… explain why and win a prize

In SQL Server 2012 there is a new feature that should be very familiar to Oracle folks called SEQUENCE.

(my wife LOVES this game)

“Creates a sequence object and specifies its properties. A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted. Sequences, unlike identity columns, are not associated with specific tables. Applications refer to a sequence object to retrieve its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables.”

MSDN Link for CREATE SEQUENCE

So what did I get from this:

  • It’s like Identity but not
  • Can reuse values once a limit has been reached
  • Sequence can be “shared” between many tables as it is it’s own object
  • Has to be assigned to a schema

There are plenty of great examples on how to create and reference a SEQUENCE on the MSDN link above but where I’m struggling is why I would want to use this new functionality?

Some problems/questions I have right off the bat:

  • Inserting conflict – what if 2 transactions ask for the next sequence (NEXT VALUE FOR) and both try and commit the same “OrderID”?
  • IDENTITY columns can’t be updated “accidentally” … other then the use of a trigger I don’t see how you could prevent an update to a column referencing a sequence
  • Reusing sequences just sounds dangerous

I did a quick test of the “Inserting Conflict” issue and created a SEQUENCE:

CREATE SEQUENCE dbo.TestSequence1 AS int
START WITH 1
INCREMENT BY 1

Then I opened 2 mgmt studio query windows and ran the following:

DECLARE @NextSequence as int
 
BEGIN TRAN
SELECT @NextSequence = NEXT VALUE FOR dbo.TestSequence1
 
SELECT @NextSequence

and I’m happy to report that getting the “NEXT VALUE” increments the sequence so as long as you do this within a transaction you should be good to go without worrying about an INSERT conflict.

So where does that leave me?

It leaves me still asking WHY… so where is where the “win a prize” comes in…

HOW TO WIN A PRIZE

In the comments section if you can give me a reason or a use-case as to why/how you would use this over IDENTITY you could win a prize 🙂

Now the winner is going to be the reason/use-case/comment that best explains to ME how/why you would use SEQUENCE and why it would be a better solution than an IDENTITY.

Contest end date of: Feb 10th

If you’d like to chat with me about this or anything else (SQL or other) please leave a comment or hit me up on my Twitter: @ColinStasiuk

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

12 Comments

  1. I’ll have a go.
    I think sequences are an improvement over identity columns because it allows for more flexibility. Sequences can be used more than just to provide values for an column, but let’s just take a look at that use case: Providing identity values.

    * You no longer have to worry about SET IDENTITY_INSERT ON/OFF. I can think of a few use cases. For example, For good or bad, I work with some tables that need identity columns, but also need to store static data.

    * If I remember correctly, you can’t remove identity columns from a table. But you can remove a default on a column that happens to be defined like this: DEFAULT (NEXT VALUE FOR dbo.seq1) For example, I have a datawarehouse whose identity columns are no longer being managed by the database, but by an external ETL process. Had I used a sequence, this would not be a problem.

  2. One use case is to be able to have a single sequence number the data in multiple tables. Example: financial transactions of different types (invoice, payment, receipt, etc.) may work well if partitioned into different tables by type, with different columns, but still be numbered in a single sequence. It’s not the only way to implement that design, but it’s nice and simple.

  3. Simon says:

    You can only have one identity on a table. We have a table that has an Identity column but also has a second id column. Due to various legacy systems being imported and clients already having been assigned an ID we had to use a new column. Right now to generate the second ID we use a second table with just an Identity column which was seeded above the max id we already had. We then use this statement to generate the next ID:

    INSERT INTO [Table]
    DEFAULT VALUES

    Using a sequence would be cleaner on the schema. Note we cannot use the repeating functionality of sequence in this case.

    Other examples could be to assign a client to a sales rep on a round robin method – just a thought.

  4. Karen Lopez says:

    Some of what others have said, but why one might use a SEQUENCE:

    – to have one set of sequences used across more than one table. Think of this usage not as a surrogate key, but as a user-viewable number like Control Number or Document Number on several tables.

    – to allow tables to have more than one incremented number. Think of Order Number and Cancellation Number, both on the Order table. Can’t do that with IDENTITY.

    – since sequences are generated completely outside a table, you can chose to store the resulting value in a different format, say VARCHAR or CHAR. Or, heaven forbit, you might want to append data to it or do other kinds of evil to it before storing it.

    – since sequences are generated completely outside a table, you can store the results in a NULLable column.

    I think the confusion sets in because we tend to think of autogenerated number as useful only as surrogate keys. But we have real data needs for generating numbers other than just row identifiers, even if that’s the primary use for this. In fact, one could have a table with a PK that uses IDENTITY and an unlimited number of columns that use a SEQUENCE to set their values.

    So SEQUENCE is a feature that just generates numbers based on some characteristics. A designer has many ways to use the number once it is generated and tables are not dependent on that method to get that number, nor do they even “know” about how that value was provided.

    This separate of the COLUMN from how the data is populated is a feature of using SEQUENCEs. How one uses this separation depends on the technical and business requirements for the data.

  5. Merrill Aldrich says:

    From a coding standpoint, you can also “grab” one or several values without performing an insert, then compose inserts for a whole collection for rows targeting different related tables, and insert insert the rows in a separate operation.

  6. I withdraw my wishy-washy answer and endorse Karen’s. (well put!)

  7. Sequences are not bound in transactions. If you ask a sequence no. in a transaction and the transaction is rolled back, you lose your sequence value. So you might have gaps in your sequence.

    It is good if you need a unique ID but don’t want to be bound by transactions and can have gaps.

    To have an Identity, you first need to insert the records. With sequences you have a unique ID WITHOUT inserting the record.

    I worked with Progress +-15 years ago and it had sequences. We used it (and abused it too by using one for assigning invoice numbers that cannot have gaps…) a lot.

  8. Karen Lopez says:

    It’s also possible to have gaps with the IDENTITY property, so that’s not a real difference between the two options.

    I tell my teams that if they need a guaranteed sequential, no gaps number for a value, they are going to have to manage it like any other type of data with complex requirements. Stuff happens during transactions, so you can’t rely on either IDENTITY or SEQUENCE to guarantee no gaps.

  9. Jim says:

    In Oracle selecting a sequence generator value is an atomic operation. The SQL Server version implies the same thing:
    “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.”

    Perhaps this is another attempt by the SQL Server team to catch up with Oracle. Better, but still not close.

  10. Ricardo Vieira says:

    Sorry I came across this really late but I notice one important reason was not discussed: concurrency. If you are processing dozens of transactions a second, would you really want each transaction to wait until all previous ones get committed in order to acquire the next unique number. I guess not (or switch to a single thread, single processor system).

  11. When it comes to changes, T-Mobile’s official changelog mentions the update brings Device anti-theft feature, but apart from that nothing else is confirmed.

  12. Apple says battery life improves by an hour or two, thanks to higher capacity and efficiencies. No battery is ever going to be enough, but Apple promises 12 to 15 hours of internet use.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.