Sequences III

  • sknox

    SSChampion

    Points: 12243

    Comments posted to this topic are about the item Sequences III

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice question - finally beginning to understand what a "Sequence", is and how it works ..........THANKS

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Revenant

    SSC-Forever

    Points: 42467

    Thanks for a really easy one!

    (I wish I came up with it...)

  • marlon.seton

    SSCrazy

    Points: 2623

    I think this question indicates that using default values with a new item like CREATE SEQUENCE isn't a great idea. I, for one, will be defining everything (AS, START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE, CACHE) if I get round to using sequences in SQLServer. I know it might look wordy and take up a few more lines but for the sake of clarity, and potential help in solving problems, I would think it worth it.

  • TomThomson

    SSC Guru

    Points: 104772

    Nice question. Easy if one recognises the decimal representation of 2**31.

    Tom

  • Thomas Abraham

    SSChampion

    Points: 10761

    Argh! Blew right past the CYCLE/NO CYCLE option explanation in BOL, looking for behavior when min value exceeded. SHould have had that first cup of green tea.

    Thanks for the question! I've really enjoyed these questions on CREATE SEQUENCE. I look forward to using it when my company moves to 2012 ....

    .... in 2021!

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Michael Lysons

    SSCertifiable

    Points: 6472

    I like these sequence questions, despite the fact I keep getting them wrong 😀

  • Toreador

    SSChampion

    Points: 11243

    Does anyone have any suggestions for possible uses for sequences? Once upon a time I'd have used them for populating primary keys instead of using identities (ie the way I used to in Oracle). But now that we have the Output clause to easily read back the generated values there doesn't seem much advantage in using a sequence instead.

  • marlon.seton

    SSCrazy

    Points: 2623

    Toreador (5/1/2012)


    Does anyone have any suggestions for possible uses for sequences? Once upon a time I'd have used them for populating primary keys instead of using identities (ie the way I used to in Oracle). But now that we have the Output clause to easily read back the generated values there doesn't seem much advantage in using a sequence instead.

    Our software has to run on Oracle and SQLServer. At the moment, the modules that use sequences are Oracle only but if we port those modules to SQLServer we would use sequence there, too, so as to keep the code as similar as possible.

  • EL Jerry

    SSCertifiable

    Points: 7053

    Thanks for another SEQUENCEs question, I think I now understand how they work.

    I agree with Marlon that defaults should not be used in sequences, as it is harder to debug when there is a problem.

    "El" Jerry

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • OzYbOi d(-_-)b

    Hall of Fame

    Points: 3944

    another good question - cheers

  • mtassin

    SSC-Insane

    Points: 23096

    marlon.seton (5/1/2012)


    I think this question indicates that using default values with a new item like CREATE SEQUENCE isn't a great idea. I, for one, will be defining everything (AS, START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE, CACHE) if I get round to using sequences in SQLServer. I know it might look wordy and take up a few more lines but for the sake of clarity, and potential help in solving problems, I would think it worth it.

    Defaults aren't bad... a default sequence is a bigint starting at -9,223,372,036,854,775,808 and counting up.

    In this case the qotd started at the bottom end of the int and counted down.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • mtassin

    SSC-Insane

    Points: 23096

    Toreador (5/1/2012)


    Does anyone have any suggestions for possible uses for sequences? Once upon a time I'd have used them for populating primary keys instead of using identities (ie the way I used to in Oracle). But now that we have the Output clause to easily read back the generated values there doesn't seem much advantage in using a sequence instead.

    a loosely coupled multi-sql server system could use these....

    define the sequence on each server in a way that they never collide. Then you could write your own "merge" code to replicate changes in the manner you describe.

    Other than something like that.... no clue... personally I prefer identities.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Ken Wymore

    SSCoach

    Points: 16432

    Thanks for the question. Not sure if we will ever use this feature but it is good to know how it works anyway.

  • sknox

    SSChampion

    Points: 12243

    mtassin (5/1/2012)


    marlon.seton (5/1/2012)


    I think this question indicates that using default values with a new item like CREATE SEQUENCE isn't a great idea. I, for one, will be defining everything (AS, START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE, CACHE) if I get round to using sequences in SQLServer. I know it might look wordy and take up a few more lines but for the sake of clarity, and potential help in solving problems, I would think it worth it.

    Defaults aren't bad... a default sequence is a bigint starting at -9,223,372,036,854,775,808 and counting up.

    In this case the qotd started at the bottom end of the int and counted down.

    This QotD (and Sequences I and II) were written to highlight specific defaults for sequences. No, defaults in and of themselves aren't bad -- indeed with sequences Microsoft has done an OK job of ensuring that if you rely on defaults, things won't break too badly.

    The problem comes in when you specify some values but leave others at default, as in this scenario, or when you haven't used a feature for a while, and think a default is A when it's actually B.

    If you explicitly define each value, you not only can't easily make that kind of mistake, but you've also documented the structure completely in one place rather than relying on documentation elsewhere. This reduces the chance that someone else (a coworker or even a replacement) could misunderstand what the sequence is doing.

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

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