Post reply

How to set up Auto-Increment in SQL Server 2005

  • August 10, 2006 at 10:48 am

    #172062

    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

  • August 10, 2006 at 11:29 am

    #654386

    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

  • August 10, 2006 at 1:19 pm

    #654412

    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.

  • August 11, 2006 at 6:41 am

    #654524

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

  • September 11, 2007 at 7:47 am

    #732782

    Hello,

    Can we do this through Transact SQL?

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

  • September 11, 2007 at 7:56 am

    #732789

    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

  • September 14, 2007 at 8:17 am

    #733788

    Hi Andras,

    Thanks man !!!

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

  • December 15, 2008 at 9:59 am

    #912611

    What if this option is grayed out?

  • December 18, 2008 at 9:01 am

    #914642

    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

  • April 13, 2009 at 2:34 pm

    #976874

    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?

  • October 1, 2009 at 9:06 am

    #1060505

    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