Sequences I

  • sknox

    SSChampion

    Points: 12294

    Comments posted to this topic are about the item Sequences I

  • Henrico Bekker

    One Orange Chip

    Points: 27652

    got it wrong, as the correct answer wasn't listed.

    your syntax is wrong in, sequence wouldn't be created to start of with...no increment value, no 'AS'..etc.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • Koen Verbeeck

    SSC Guru

    Points: 258975

    Henrico Bekker (4/16/2012)


    got it wrong, as the correct answer wasn't listed.

    your syntax is wrong in, sequence wouldn't be created to start of with...no increment value, no 'AS'..etc.

    When I look at BOL I see the following:

    CREATE SEQUENCE [schema_name . ] sequence_name

    [ AS [ built_in_integer_type | user-defined_integer_type ] ]

    [ START WITH <constant> ]

    [ INCREMENT BY <constant> ]

    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]

    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]

    [ CYCLE | { NO CYCLE } ]

    [ { CACHE [ <constant> ] } | { NO CACHE } ]

    [ ; ]

    This means that everything is optional except the first line. MSDN also lists every default value for every line.

    edit: Example E in the MSDN article is exactly the same as the QotD.

    Anyway, awesome question. Really like the SQL Server 2012 questions!

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

  • This was removed by the editor as SPAM

  • Carlo Romagnano

    SSC-Insane

    Points: 22011

    I am curious to know how many developpers will use default value for "START WITH". In most cases will be a negative number.

  • Koen Verbeeck

    SSC Guru

    Points: 258975

    Carlo Romagnano (4/17/2012)


    I am curious to know how many developpers will use default value for "START WITH". In most cases will be a negative number.

    They will all be negative, except for tinyint.

    In most cases this shouldn't be a problem, unless you are using the sequence for generating surrogate keys and you have tied special meaning to some of them.

    For example: 0 equals unknown, -1 equals not applicable.

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

  • Carlo Romagnano

    SSC-Insane

    Points: 22011

    Koen Verbeeck (4/17/2012)


    Carlo Romagnano (4/17/2012)


    I am curious to know how many developpers will use default value for "START WITH". In most cases will be a negative number.

    They will all be negative, except for tinyint.

    In most cases this shouldn't be a problem, unless you are using the sequence for generating surrogate keys and you have tied special meaning to some of them.

    For example: 0 equals unknown, -1 equals not applicable.

    A number is a number, it doesn't matter if it is negative or positive,

    but when you do maintanance on table or you should copy and paste values to/from excel, great numbers

    are annoying.

  • Iulian -207023

    SSCertifiable

    Points: 7509

    Thanks for the question

    Iulian

  • Michael Lysons

    SSCertifiable

    Points: 6478

    Thanks for the question. I had to do a fair bit of digging to find out the answer.

  • Stuart Davies

    SSCoach

    Points: 18878

    Good question today, thanks - took an educated guess and got it wrong.

    Looks like I need more education.

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

  • DugyC

    Hall of Fame

    Points: 3804

    Great question, and a nice feature to know about... not sure what I'd use it for mind.

    At first thought it wasn't fair to ask a SQL 2012 question as its so new, but bit my lip and did a wee bit of research and the answer was there in a flash.

    Relatively easy question come the end.

    Cheers for the point. 🙂

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • Koen Verbeeck

    SSC Guru

    Points: 258975

    DugyC (4/17/2012)


    At first thought it wasn't fair to ask a SQL 2012 question as its so new...

    Hold on to yourself, because it's a SSIS 2012 question tomorrow 😀

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

  • InvisibleCat

    Default port

    Points: 1496

    A great questions, got me thinking so well worth it.

    Thank you.

  • Thomas Abraham

    SSChampion

    Points: 10761

    Great question. Simple (with a single look at BOL), clear cut, a bit surprising, and on a new version. Thanks!

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

  • jfogel

    SSCarpal Tunnel

    Points: 4136

    Got it right. It is nice to see the sequence feature is now in SQL Server. Mainly because developers had to use a sequence for Oracle and an identity column for SQL Server. At least now the code can move closer to being the same. Note I wrote closer.

    Cheers

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

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