SQL Server Performance

Help with Identity

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Mar 19, 2010.

  1. shankbond New Member

    Hi,
    I need help regarding the identity column.
    I have a column whose Identity seed was 1 when the column was initially created.
    Now I have to change the seed to -2,147,483,648
    I am having a plan;
    I am creating a temporary table with two columns both of them INT without any IDENTITY property specified.
    I am inserting the Identity column values from the table in question into this table; the other column being null
    next I will update the column2 of that table by simply adding -2,147,483,648 to the column1 of that row this will give me the expected IDENTITY value if I would have set the SEED to -2,147,483,648.
    Now finally to change the seed in the original table I will create a new column and add all values in that column and then make it to IDENTITY
    In short how do You make a column IDENTITY through ALTER Column statement and that the column is already having values in it?
    Any help shall be appreciated!
  2. FrankKalis Moderator

    Hm, why don't you just do a SET IDENTITY INSERT with a < IDENTITY col> -2,147,483,648 expression?
  3. shankbond New Member

    Hi Frank,
    I don't know if IDENTITY_INSERT ON is possible if we actually create a new column.
    can You please improve this script:
    (where should I PUT IDENTITY_INSERT ON)
    ALTER TABLE dbo.xyz
    ADD abc INT IDENTITY(-2147483648, 1)
    Please Reply
  4. Adriaan New Member

    IDENTITY_INSERT is an option you can activate before running an INSERT query on the table. (Always remember to turn it off, too.)
    Other than that, you should not attach any importance to the actual value of the identity column: it´s completely meaningless. That´s why it´s such an excellent choice as a substitute key.
    People seem to want to use it as if it were a timestamp, but it´s just not going to be reliable that way.
  5. shankbond New Member

    hmm....
    I am using IDENTITY COLUMN AS A primary key; then according to You I should not be using primary key AS an IDENTITY?
    Then what else alternatives do I have for Primary Key besides IDENTITY column?
    I am creating a database for human resource
    it means that I can have a maximum of
    -9,223,372,036,854,775,808 * 2
    rows in a table
    including the gaps I will be having due to failing of data insertions
    Though it is huge but I am still exploring another candidate :)
  6. Adriaan New Member

    DECIMAL(38,0) will go a lot higher than INT or BIGINT, and you can set IDENTITY on a column of that type.
    Again, don't bother with the actual values. Your table will have a natural key too, it's fine to make this a non-clustered primary key, after adding a clustered unique index on the identity column.
  7. shankbond New Member

    Hi Adriaan,
    [quote user="Adriaan"]Again, don't bother with the actual values. Your table will have a natural key too, it's fine to make this a non-clustered primary key, after adding a clustered unique index on the identity column.[/quote]
    If I am not wrong You are suggesting that I should be making a primary index (non clustered ) on natural key(key that has a bussiness meaning ?)
    and a unique clustered index on identity column.
    sorry English is not my mother tongue, but this is what I understood, please correct me if I am concluding wrong
  8. Adriaan New Member

    Correct, these are the two steps I was suggesting.
    Make sure you create the clustered index on the identity column first, before creating the primary key on the natural key.

Share This Page