The Sequence Object in SQL Server 2012

  • Sanil Mhatre

    SSC Journeyman

    Points: 83

    Comments posted to this topic are about the item The Sequence Object in SQL Server 2012

  • Abrar Ahmad_

    SSCarpal Tunnel

    Points: 4104

    [font="Verdana"]Thanks for the brief and meaningful article!

    Its long since MS-SQL Server has defended IDENTITY as a close alternative of Sequences objects, available in Oracle RDBMS.

    Now what was the actual requirement to implement Sequences in SQL Server as well?

    It will be great if any legitimate usefulness can be discussed here, other than the mere standards etc requirements.

    Many thanks![/font]

  • Koen Verbeeck

    SSC Guru

    Points: 258909

    This is another article about Sequences that just repeats everything on MSDN.

    What I really would like to see is an article showing us what we can actuallo DO with the sequence object.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • EKD

    SSC-Addicted

    Points: 442

    Thank you for the article. Some real world examples would be useful. It is not clear what sequence objects are actually for.

  • hakkie42

    SSC Enthusiast

    Points: 151

    Agreed with the previous posters.

    It's not so hard to go through MSDN looking for how to use things, the reason why one should use things is much more important.

    Real world arguments/examples would help here.

  • okbangas

    SSChampion

    Points: 11773

    Since this is more or less a copy of a previous article, I chose to copy my comment from that article:

    First of all, I don't think this article is comprehensive enough, and as pointed out, it does not explain when to use (or not use) sequences. I've seen test showing that identity is outperforming sequence, so you should obviously not use a sequence when you simply could use an identity column in your table. However, identity is limited to a single table, whereas sequences can be shared across multiple tables. There are numerous cases where you want a unique identifier across tables in the same database. Prior to SQL Server 2012 you would have to manage this yourself, use an identity column in a shared "identity-table", or use uniqueidentifiers (GUIDs), which have several drawbacks. In these cases, a sequence is a far better choice in my opinion.

    What is not mentioned in the article, but the MSDN article, is that you could use the next value for statement as default value in a table. The following simple code shows two tables sharing a sequence:

    use tempdb;

    go

    create sequence MyIDs as int

    start with 1

    increment by 1

    no cycle;

    go

    create table MyTable (

    id int default next value for MyIDs,

    value varchar(50)

    );

    create table MySecondTable (

    id int default next value for MyIDs,

    value varchar(50)

    );

    insert into MyTable (value) values ('Test');

    insert into MySecondTable (value) values ('Test2');

    select * from MyTable

    select * from MySecondTable

    go

    drop table MyTable;

    drop table MySecondTable;

    drop sequence MyIDs

    output:

    id value

    ----------- --------------------------------------------------

    1 Test

    id value

    ----------- --------------------------------------------------

    2 Test2



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • danl-971512

    SSC Veteran

    Points: 211

    My two cents: Microsoft has a lot of conversions that happen throughout the year from Oracle to SQLServer. The transition for Oracle folks to get to SQLServer is made easier by having a sequence object.

    However, in my opinion, this is a travesty. Separate Sequence Objects have long been the bane of existence for high speed, fully parallel bulk & batch loading paradigms. To introduce this to SQLServer raises serious concerns in these areas.

    It also makes it easier to "blow the lid" off of big-int based sequences especially if a single sequence is used in large or huge tables. I hope Microsoft thought about mitigation strategies for all of these things, including clusters, and parallel servers, and bulk loaders. If they didn't think about this, then those projects that take on the use of Sequence Objects rather than identity columns will struggle to adapt as their volumes grow, and their parallelism needs increase.

    I've long thought that Oracle should be the one to change, to add identity columns, rather than the other way around. If you look at other major RDBMS players, they pretty much all have identity columns these days: MySQL, Teradata, DB2 UDB, and so on.

    But this is just my opinion, however the article is good coverage on a Sequence Object.

    Thank-you,

    Dan Linstedt

  • Divine Flame

    SSCoach

    Points: 15941

    As many posters have said above, the article doesn't really provide the real world example on where to use sequences.


    Sujeet Singh

  • Abrar Ahmad_

    SSCarpal Tunnel

    Points: 4104

    okbangas (6/4/2012)


    use tempdb;

    go

    create sequence MyIDs as int

    start with 1

    increment by 1

    no cycle;

    go

    create table MyTable (

    id int default next value for MyIDs,

    value varchar(50)

    );

    create table MySecondTable (

    id int default next value for MyIDs,

    value varchar(50)

    );

    insert into MyTable (value) values ('Test');

    insert into MySecondTable (value) values ('Test2');

    select * from MyTable

    select * from MySecondTable

    go

    drop table MyTable;

    drop table MySecondTable;

    drop sequence MyIDs

    [font="Verdana"]If possible kindly share the output of the code segment and explain a bit, because we are still on the place of having no sequences in SQL SERVER 😀

    Thanks ![/font]

  • Sanil Mhatre

    SSC Journeyman

    Points: 83

    Thank You for all of your feedback. I do see how real life usage example for Sequences can prove useful here. I will try to post some examples here or may be write a new article covering this particular area of interest. I really appreciate all of the constructive criticism here and it will definitely help me write better articles in the future. Thanks.

  • sknox

    SSChampion

    Points: 12216

    danl-971512 (6/4/2012)


    It also makes it easier to "blow the lid" off of big-int based sequences especially if a single sequence is used in large or huge tables.

    And IDENTITY is better in this respect how...?

  • okbangas

    SSChampion

    Points: 11773

    sknox (6/4/2012)


    danl-971512 (6/4/2012)


    It also makes it easier to "blow the lid" off of big-int based sequences especially if a single sequence is used in large or huge tables.

    And IDENTITY is better in this respect how...?

    Identitiy is for a single table only, not shared between tables. Secondly, if you use caching to reduce IO, you will get more (or bigger) gaps of values each time the instance is restarted, eg when the server is rebooted.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • okbangas

    SSChampion

    Points: 11773

    Abrar Ahmad_ (6/4/2012)


    If possible kindly share the output of the code segment and explain a bit, because we are still on the place of having no sequences in SQL SERVER 😀

    Updated my post to include output.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • sknox

    SSChampion

    Points: 12216

    okbangas (6/4/2012)


    sknox (6/4/2012)


    danl-971512 (6/4/2012)


    It also makes it easier to "blow the lid" off of big-int based sequences especially if a single sequence is used in large or huge tables.

    And IDENTITY is better in this respect how...?

    Identitiy is for a single table only, not shared between tables. Secondly, if you use caching to reduce IO, you will get more (or bigger) gaps of values each time the instance is restarted, eg when the server is rebooted.

    For one, you don't HAVE to share a sequence between tables. You can if you'd like, or you can create a sequence for each table.

    For two, you do not lose any values when the instance is restarted properly. You only lose values when the server shuts down abnormally, or when an application reserves a range but doesn't use all of it. Read the cache management section of http://msdn.microsoft.com/en-us/library/ff878091.aspx for more detail.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply