SQL Add Auto-increment Identity Column to Existing Table

by | Sep 16, 2016 | SQL | 0 comments

SQL Design Table

SQL server uses the Identity Specification to add a numbered column that automatically increments. You can add this to a new table you are designing or to an existing one. SQL uses integer as the data type, so you are restricted to integers for your automatic identity. Available integers can be positive or negative and range from -2,147,483,648 to 2,147,483,648.

This is what you will need:

  • Column name, such as “RowID”
  • Data type: integer (int)
  • Identity Seed: This is the starting number. It defaults to 1, but you can set it to any integer in the range listed above.
  • Identity Increment: This is the amount the number will auto-increment. It defaults to 1.

Follow these steps to add a new identity column.

  1. Open SQL Server Management Server
  2. right-click your table and select “Design”
    3. SQL Design Table

    SQL Design Table

  3. Add a new column and give it an appropriate name like ID or RowID
  4. Set the datatype to “int”
  5. Turn off “Allow Nulls”
SQL Add Identity Column

SQL Add Identity Column

Follow these steps to configure your new column.
If you are not familiar with the interface, there are a few notes added to the end that can help you navigate the Column Properties.

  1. Open “Identity Specification”
  2. Change “(Is Identity)” to “Yes”
  3. If needed, change the default Identity Seed and Identity Increment from 1 to an appropriate number
    4. SQL Identity Settings

    SQL Identity Settings

  4. Example Identity setting: Seed is 110 and increment is 10. This means the first five IDs are: 110, 120, 130, 140, and 150.
    5. SQL Identity Settings Example

    SQL Identity Settings Example

  5. Example Identity setting: Seed is 10001 and increment is 1. This means the first five IDs are: 10001, 10002, 10003, 10004, and 10005.

    6. SQL Identity Settings Example 2

    SQL Identity Settings Example 2

Notes to help you navigate the Column Properties

  • Make sure your cursor is on your new ID column
  • In the Column Properties section, sections are open or closed based on the arrow in front of them. If the arrow points to the right, the section is closed. If the arrow points down, the section is open.
  • You can open a section within a section
  • You will need to open the “Identity Specification” section, which is inside the “Table Designer” section
  • By default, “Table Designer” is open and “Identity Specification” is closed.

Submit a Comment

Your email address will not be published. Required fields are marked *