Sequences in SQL Server 2012

  • Daniel Calbimonte

    SSCarpal Tunnel

    Points: 4702

    Comments posted to this topic are about the item Sequences in SQL Server 2012

  • Koen Verbeeck

    SSC Guru

    Points: 258890

    What would have been nice is some real world examples why I should use sequences.

    For now it just seems I can use an IDENTITY column as well.

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

  • okbangas

    SSChampion

    Points: 11773

    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



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

    Concatenating Row Values in Transact-SQL[/url]

  • kramaswamy

    SSCoach

    Points: 18135

    You can use them as default values? oh that's cool - I was wondering where this feature would be usable. I can see that coming in handy for sure. Thanks!

  • Sean Lange

    SSC Guru

    Points: 286402

    I too was wondering why the sequence thing was such a big deal. Thanks okbangas for your example and explanation. That looks like it could be really useful in certain situations.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Daniel Calbimonte

    SSCarpal Tunnel

    Points: 4702

    Yes, this article was just an introduction. I will write a new one named sequences vs IDENTITY.

    Personally, I hate the IDENTITY. 😛

  • Sean Lange

    SSC Guru

    Points: 286402

    calbimonte.daniel (4/13/2012)


    Yes, this article was just an introduction. I will write a new one named sequences vs IDENTITY.

    Personally, I hate the IDENTITY.

    What is wrong with IDENTITY? It is super easy to use and the performance is good. Curious why you feel so strongly about it.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Steph Locke

    SSCrazy

    Points: 2857

    prima facie this seems a good way to produce a tally table

    I'm also going to have a play to see whether you can have a formula as the increment field. I'm not sure yet just how useful it will be but being able to increment in units of 10, not 1 would certainly give an old school feel to numerical increments.

  • eric.lyons

    SSC Eights!

    Points: 840

    Sequences are used to deterministic programming.

    A simple Example: EDI documents outbound have unique numbers that are in sequence for EACH customer.

    Customer1 receives document 5010, 5011, etc...

    Customer2 receives document 2010, 2011, etc...

    Each customer would therefore have a sequence when set up for EDI.

    No, sequences aren't the only way to do this.

    *****

    Give a man a fish, and you've given him a meal. Teach a man to fish and he'll ask to borrow your boat.:-P

  • Scott D. Jacobson

    SSCertifiable

    Points: 6012

    I'm also going to have a play to see whether you can have a formula as the increment field. I'm not sure yet just how useful it will be but being able to increment in units of 10, not 1 would certainly give an old school feel to numerical increments.

    I don't see why you couldn't just change the INCREMENT BY statement to whatever value you like.

    Can you explain more about what you mean by a "tally table"?

  • Sean Lange

    SSC Guru

    Points: 286402

    Scott D. Jacobson (4/13/2012)


    I'm also going to have a play to see whether you can have a formula as the increment field. I'm not sure yet just how useful it will be but being able to increment in units of 10, not 1 would certainly give an old school feel to numerical increments.

    I don't see why you couldn't just change the INCREMENT BY statement to whatever value you like.

    Can you explain more about what you mean by a "tally table"?

    To coin a phrase from one of the other threadizens, "the tally table is the swiss army knife for sql server".

    Jeff Moden has an article that explains it The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange

    SSC Guru

    Points: 286402

    stephanie.sullivan (4/13/2012)


    prima facie this seems a good way to produce a tally table

    I'm also going to have a play to see whether you can have a formula as the increment field. I'm not sure yet just how useful it will be but being able to increment in units of 10, not 1 would certainly give an old school feel to numerical increments.

    Are you saying you would use this in a loop to create your tally table? See the article I posted above from Jeff. The first part talks about creating the table with no loops.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Steph Locke

    SSCrazy

    Points: 2857

    I was more thinking as a way of producing the tally table itself - Jeff uses an identity and cross joins sys columns. This method may or may not be faster but it would read more clearly, which I'll happily trade some performance for.

  • Sean Lange

    SSC Guru

    Points: 286402

    stephanie.sullivan (4/13/2012)


    I was more thinking as a way of producing the tally table itself - Jeff uses an identity and cross joins sys columns. This method may or may not be faster but it would read more clearly, which I'll happily trade some performance for.

    It would definitely be slower but for just creating a tally table it probably doesn't matter all that much. I don't create them frequently because I just keep it permanently.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Jeff Moden

    SSC Guru

    Points: 993909

    stephanie.sullivan (4/13/2012)


    I was more thinking as a way of producing the tally table itself - Jeff uses an identity and cross joins sys columns. This method may or may not be faster but it would read more clearly, which I'll happily trade some performance for.

    The only time I'll make a trade on performance is for accuracy. Comments can be used to explain anything else.

    Can you post how you'd make a Tally Table using a Sequence? Seems like a bit of extra work especially considering how comparatively long the code for the WHILE loop would be.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 15 posts - 1 through 15 (of 20 total)

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