How to set up Auto-Increment in SQL Server 2005

  • I have a database table. I need to set a particular data field to allow for Auto Incrementing.

    Basically I need to set up the initial integer value to start at. Then to specify the amount to increment by.

    What I have done so far is to right click on the field name in SQL Server 2005. On the pop-up menu I select the menu option for properties.

    When the properties are displayed I see some fields listed in dark grey but I am not able to edit these fields. Specifically the fields I am talking about are:

    Identity Increment

    Identity Seed

    Is Identity

     

    Also I am confused as to what they expect in the field marked Is Identity. What value should I set this at?

    Thanks in advance,

    Bill

  • Is Identity --> Yes

    Identity Seed --> Starting value

    Identity Increment --> Number to be incremented by i.e if the previous number is 2 and identity increement is next number would 2+1

  • Thank  you for the effort to answer the second part of my question. I am still trying to figure out the following:

    I need to set a particular data field to allow for Auto Incrementing. Basically I need to set up the initial integer value to start at. Then to specify the amount to increment by. What I have done so far is to right click on the field name in SQL Server 2005. On the pop-up menu I select the menu option for properties.When the properties are displayed I see some fields listed in dark grey but I am not able to edit these fields.

  • You need to modify the table.

    Right click on the table and select modify, you will have your table displayed in the summary area. Select the column that you want to make an identity field and then look at the column properties below.

    There will be an Identity Specification property that you will want to expand. Once expanded, set the IsIdentity property equal to Yes and then you can update the seed and the increment values to your liking...

  • Hello,

    Can we do this through Transact SQL?

    _____________________________________________
    One ounce of practice is more important than tonnes of dreams

  • Yes you can. The column definition accepts identity(seed, increment) specification, but you cannot alter a column to become an identity column, or drop this property while keeping the column. You would need to add a new column, or rebuild the table (create a temporary table with the identity column, copy data (if you want to set the identity value you will need to set and unset identity insert), then delete the original table, rename the temporary table (sp_rename).

    To see an example you can use Management Studio designer, change a table as in the previous posts, and then use the "Script Change Script" icon to generate a script

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Hi Andras,

    Thanks man !!!

    _____________________________________________
    One ounce of practice is more important than tonnes of dreams

  • What if this option is grayed out?

  • Michael Wilkins (12/15/2008)


    What if this option is grayed out?

    Could you provide more details about which option that is greyed out?

    - Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • all of the properties under the Identity Specification node are greyed out. I am having this problem as well and I can't remember how to fix it. Any help?

  • Data type should be int. Then you have to select Allow null --> No

    Then you can enter identity details 🙂

Viewing 11 posts - 1 through 10 (of 10 total)

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