Auto increment primary key in SQL Server Management Studio 2012

sql-server sql-server-2012 identity
Ledgemonkey picture Ledgemonkey · Jun 12, 2012 · Viewed 1.2M times · Source

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.

There must be a simple way to do this but I can't find it.

Answer

Raab picture Raab · Jun 12, 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
ALTER TABLE [yourTable] DROP COLUMN ID 
ALTER TABLE [yourTable] ADD ID INT IDENTITY(1,1)

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

image reference fifo's

Related questions

SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry

I have a strange scenario in which the auto identity int column in my SQL Server 2012 database is not incrementing properly. Say I have a table which uses an int auto identity as a primary key it is sporadically skipping …

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More
Convert Date format into DD/MMM/YYYY format in SQL Server

I have a query in SQL, I have to get a date in a format of dd/mm/yy Example: 25/jun/2013. How can I convert it for SQL server?

Read More