SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2003
    Location
    Halifax
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple Identity Fields + SQL Server?

    Just wondering if anyone can answer a question for me.

    At the moment I have a populated table, I currently have 1 field specified as an Identity field, however I want to add another one.

    Upon adding the second identity field my first identity field is unselected. I'm guessing this is some prebuilt constraint in SQL server?

    Are there any ways around this at the db, or am I going to have to run an initial query on my first field to return the MAX value for a field.

    TIA

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    can't have more than one identity per table, as far as i know

    select max() is a poor technique

    why two identities? what are you really after, the uniqueness or the serialness?

    are you familiar with sequences, i.e. the way some databases (oracle, postgresql) assign sequential numbers?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2003
    Location
    Halifax
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought that would be the case.

    I'm not particularly comfortable with using MAX as it has very obvious limitations.

    Yes I'm familar with sequences and how most db's handle them, and I've even done some more extended reading around the identity function.

    My problem is I have two columns, both of which must be unique, and incremented based upon the last value within each of the columns hence my requirement for multiple identity columns.

    Perhaps its more my lack of knowledge of SQL server that is the problem...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by furious5
    ... both of which must be unique, and incremented based upon the last value within each of the columns
    why incremented? why not random? can they just be sequential? are gaps allowed, or must they be consecutive too?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Mar 2003
    Location
    Halifax
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The values could be sequential with gaps.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, the problem with the select max() technique is throttling the very table you're trying to insert into

    first you have to lock the table you want to insert into, then run the select max() query, then do the insert using the incremented max number, then release the lock

    instead, try the "sequence number table" strategy

    the sequence number table contains one column in one row

    place a lock on the sequence number table, add 1 to the number, select it (look ma, no max), release the lock, then use the number, without a lock, to insert into your target table

    (aside: this should actually be faster than the select max() technique, right? i mean, not just because it doesn't require max, but also because the target table is so much bigger)

    in your case, you could use this technique plus the identity, for two different incrementing numbers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Mar 2003
    Location
    Halifax
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting, will have a look at the proposed technique. Thanks for you're time.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •