Post reply

auto-increment across multiple tables

  • January 31, 2014 at 3:16 pm

    #285131

    Hi guys,

    I have multiple tables in a database, all of which have an ID column. I would like to auto-increment the ID value so that each ID is unique across the database. Can I do it?

    Thanks!

  • January 31, 2014 at 3:31 pm

    #1685236

    Sounds like an interview question. Lookup "Sequences" in Books Online.

    --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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 31, 2014 at 4:22 pm

    #1685252

    Haha thanks mate. That helped a lot.

    P.S. I am a finance recruitment officer by day... I seem to structure all of my questions as though I am interviewing someone.

  • January 31, 2014 at 8:06 pm

    #1685266

    Ok so, I thought it was working however I keep getting this error for the below code...

    ERROR:

    The name "ID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

    CODE:

    CREATE TABLE testMe (ID int DEFAULT(NEXT VALUE FOR dbo.Sequence-ID))

    EDIT: Dont worry... figured it out... just needed to get the "-" out of the sequence name.

  • January 31, 2014 at 9:07 pm

    #1685273

    squeekz01 (1/31/2014)

    Haha thanks mate. That helped a lot.

    P.S. I am a finance recruitment officer by day... I seem to structure all of my questions as though I am interviewing someone.

    Heh. Understood. The cool part is that you actually knew what "Books Online" is. Most of the people that I interview don't know what it is (which is totally amazing to me especially since all of them supposedly have several years of experience under their belt).

    Glad you sussed it.

    --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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 3, 2014 at 12:31 am

    #1685400

    squeekz01 (1/31/2014)

    EDIT: Dont worry... figured it out... just needed to get the "-" out of the sequence name.

    Dashs are not valid characters in object names, so if you do use one, you then have to wrap that object name in [] every single time you use it. Best to avoid using characters such as - in SQL object names.

    Bear in mind, sequence can have gaps, there's nothing preventing those gaps and nothing wrong with a sequence having them.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

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