Auto increment primary key in SQL Server Management Studio 2012

How do I auto increment the primary key in a SQL Server database table, I’ve had a look through the forum but can’t see how.

I’ve looked the the properties but can’t see an option, I have seen an answer where you go to the Identity specification property and set it to yes and set the Identity increment to 1, but that section is grayed out and I can’t change the no to yes.

  • Run OSQL on systems which do not have Sql Server installed
  • Determining if a SQL Server table is read-only
  • Default sort order for a select query in SQL Server 2005 and SQL Server 2012
  • How to Parse a comma delimited string of numbers into a temporary orderId table?
  • Crosstab query with count of values in SQL Server 2008 R2
  • Change app.config connection string depending on PC
  • There must be a simple way to do this but I can’t find it.

  • Work around SQL Server maximum columns limit 1024 and 8kb record size
  • Fuzzy SQL Search
  • How to easily edit SQL XML column in SQL Management Studio
  • SQL get values for only a few hours in given time period?
  • Table Value Function, Select Multiple Rows
  • Accents not getting inserted in SQL server
  • 8 Solutions collect form web for “Auto increment primary key in SQL Server Management Studio 2012”

    Make sure that the Key column’s datatype is int and then setting identity manually, as image shows

    enter image description here

    Or just run this code

    -- ID is the name of the  [to be] identity column

    the code will run, if ID is not the only column in the table

    image reference fifo’s

    When you’re creating the table, you can create an IDENTITY column as follows:


    The IDENTITY property will auto-increment the column up from number 1. (Note that the data type of the column has to be an integer.) If you want to add this to an existing column, use an ALTER TABLE command.

    Tested a bit, and I can’t find a way to change the Identity properties via the Column Properties window for various tables. I guess if you want to make a column an identity column, you HAVE to use an ALTER TABLE command.

    You have to expand the Identity section to expose increment and seed.

    enter image description here

    Edit: I assumed that you’d have an integer datatype, not char(10). Which is reasonable I’d say and valid when I posted this answer

    Expand your database, expand your table right click on your table and select design from dropdown.
    ITlooks like this

    Now go Column properties below of it scroll down and find Identity Specification, expand it and you will find Is Identity make it Yes. Now choose Identity Increment right below of it give the value you want to increment in it.
    enter image description here

    Perhaps I’m missing something but why doesn’t this work with the SEQUENCE object? Is this not what you’re looking for?


    CREATE SEQUENCE blah.blahsequence
    CREATE TABLE blah.de_blah_blah
    (numbers bigint PRIMARY KEY NOT NULL

    When referencing the squence in say an INSERT command just use:

    NEXT VALUE FOR blah.blahsequence

    More information and options for SEQUENCE

    When you’re using Data Type: int you can select the row which you want to get autoincremented and go to the column properties tag. There you can set the identity to ‘yes’. The starting value for autoincrement can also be edited there. Hope I could help 😉

    Be carefull like if you want the ID elements to be contigius or not. As SQLSERVER ID can jump by 1000 .

    Examle: before restart ID=11
    after restart , you insert new row in the table, then the id will be 1012.

    If the table is already populated it is not possible to change a column to IDENTITY column or convert it to non IDENTITY column. You would need to export all the data out then you can change column type to IDENTITY or vice versa and then import data back.
    I know it is painful process but I believe there is no alternative except for using sequence as mentioned in this post.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.