Auto increment primary key in SQL Server Managemen

Question

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 1

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

Example:

CREATE SCHEMA blah.
GO

CREATE SEQUENCE blah.blahsequence
START WITH 1
INCREMENT BY 1
NO CYCLE;

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

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

NEXT VALUE FOR blah.blahsequence

More information and options for SEQUENCE

Answer 2

I had this issue where I had already created the table and could not change it without dropping the table so what I did was: (Not sure when they implemented this but had it in SQL 2016)

Right click on the table in the Object Explorer:

Script Table as > DROP And CREATE To > New Query Editor Window

Then do the edit to the script said by Josien; scroll to the bottom where the CREATE TABLE is, find your Primary Key and append IDENTITY(1,1) to the end before the comma. Run script.

The DROP and CREATE script was also helpful for me because of this issue. (Which the generated script handles.)

Answer 3

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

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.

Answer 4

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

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

Answer 5

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 ;)

Answer 6

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.