Possible to have two auto incrementing columns?

Discussion in 'microsoft.public.sqlserver.newusers' started by laredotornado, Apr 21, 2010.

  1. Hi,

    I'm using MS Sql Server 2005. I have a table with an INT IDENTITY
    column, but I would like another, non-primary key column (also an INT)
    to also auto increment if no value is inserted. Is this possible, and
    how?

    Thanks for the help, - Dave
     
    laredotornado, Apr 21, 2010
    #1
    1. Advertisements

  2. You can only have one IDENTITY column per table, so you would have
    to handle this through a trigger.

    Since it is not clear how it should increment I do not give any example.
    Also, is the column nullable or not?
     
    Erland Sommarskog, Apr 21, 2010
    #2
    1. Advertisements

  3. Hi, The column is NOT NULL and will have a default value of zero. The
    point of the column is to serve as an ORDER_ID column so that I can
    order items. So the next step is to figure out how to manipulate the
    value of the column once a row is added or deleted.

    Thanks, - Dave
     
    laredotornado, Apr 23, 2010
    #3
  4. The best would probably be an INSTEAD OF trigger, as you then could have
    a unique index on the table. Something like:

    CREATE TRIGGER insert_tri ON tbl INSTEAD OF INSERT AS
    DECLARE @maxid int

    SELECT @maxid = coalesce(MAX(ORDER_ID), 0)
    FROM tbl WITH (UPDLOCK)

    INSERT tbl (ORDER_ID, col1, col2, ....
    SELECT @maxid + row_number() OVER (ORDER BY somecol), col1, col2, ....
    FROM inserted

    One drawback here is that the UPDLOCK hint will cause a serialisation
    point which can be bad for concurrency. Then again, if you need a
    contiguous series of numbers, this is inevitable anyway.
     
    Erland Sommarskog, Apr 23, 2010
    #4
  5. laredotornado

    Uri Dimant Guest

    Hi
    Also I think you can have a computed column for inserted values ( you can
    have statistics on that column tohelp speed up the queries) ,however , for
    deleted
    orders you will need a trigger


    Hi, The column is NOT NULL and will have a default value of zero. The
    point of the column is to serve as an ORDER_ID column so that I can
    order items. So the next step is to figure out how to manipulate the
    value of the column once a row is added or deleted.

    Thanks, - Dave
     
    Uri Dimant, Apr 25, 2010
    #5
  6. laredotornado

    Eric Isaacs Guest

    I would strongly recommend that you don't store the value, but rather
    store a creation date with an index on it, then when you select the
    values from the table that's when you generate the numeric value by
    using row_number() to determine the values. If all you need it for is
    for sorting, I think you're better off with a creation date than a
    numeric value you need to keep updated. The only exception to this
    would be if the user is updating the sort order, in which case the
    order should be stored.

    SELECT row_number() OVER (ORDER BY createdate), col2, col3, ....
    FROM tablename

    -Eric Isaacs
     
    Eric Isaacs, May 18, 2010
    #6
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.