ChatGPT解决这个技术问题 Extra ChatGPT

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 to do this.

I've looked at the properties but can't see an option. I saw 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.

Is the key column's datatype is int?
@CoDe aDDict its currenty set as char(10) but i can change it as I'm just practicing , thanks

R
Raab

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

https://i.stack.imgur.com/ueRYJ.jpg

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


Just a bit of correction: The IDENTITY property could be applied to any numeric data types (so it could be tinyint, smallint, int, bigint, numeric, decimal), the only constraint is that it could not represent fractional number (so it can't be float or real, neither numeric or decimal with non-zero scale) and ofc, the identity spec should be compatible with the selected data type.
Work's, providing there are no Foreign keys etc
I just wanted to add, if you've already set a default value on your identity column, the (Is Identity) remains greyed out. Remove the default value, and it becomes available again.
If you can't modify the values, go to Tools-Options-Designers and uncheck the option "prevent saving changes that require a re-creation".
Why is it even named "identity"? It's kinda a really abstract way of describing an autoincremented value.
S
Shaiju T

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

CREATE TABLE (
  ID_column INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  ...
);

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.

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


Ah, this is for SQL Server 2008 r2 specifically - never mind.
this create table syntax is good practice as advised by author of this post in comments, hope helps someone.
g
gbn

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

https://i.stack.imgur.com/8QAfh.png

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


The yes is greyed out as it is char(10) (from comment just posted)
@Martin Smith yes that seems to be the general issue , I'm going to change this and test, many thanks
while creating a new table i used BookID int primary key identity i didn't specify both seed and increment , is that good practice ? cause i see documentation in MSDN If neither is specified, the default is (1,1) .
A
Andrew Barber

https://i.stack.imgur.com/SBQvM.jpg

https://i.stack.imgur.com/ueRYJ.jpg


this seems like it would be the correct answer to me, just making the data type an int wouldn't do the trick, right?
A
Abd Abughazaleh
CREATE TABLE Persons (
    Personid int IDENTITY(1,1) PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.

In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.

Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5).

To insert a new record into the "Persons" table, we will NOT have to specify a value for the "Personid" column (a unique value will be added automatically):


T
Techie Joe

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


This solution is the correct one if you care about the time order of inserted rows, because IDENTITY sometimes jumps leaving gaps then it is back to fill those gaps again. So IDENTITY does not guarantee ALWAYS incrmenet condition
@Martin Smith Oops, didn't follow the link, my mistake! I've gotten so used to MS-SQL not having certain features that I sometimes forget that there are newer versions that support more stuff. I'll delete my comment since it might be confusing... If expressed more general it remains valid though: could be that he's using an RDBMS make/version that doesn't support SEQUENCE objects -- especially since the question was asked only a few months after SQL Server 2012 was released ;)
@FindOut_Quran Are you sure that gaps in IDENTITY columns are filled up? I've only worked with SQL Server 2005 where I've never seen that happen - the gaps simply remain. And I'd be rather surprised to see it happen on any RDBMS because such a feature would be a strange combination of "not free" (performance-wise) and "not desirable" (at least in most applications).
@PaulGroke Yes I am sure. You can try be using a transaction for inserting 1000 rows, then rollback. The idenetity value will jumb up. Try to insert more thousands of rows and commit / or rollback. You will notice it sometimes backs to fill gaps
@FindOut_Quran I can not see how this could happen. MS SQL IDENTITY columns use a simple counter which is seeded at a user-definable value and then incremented by a user-definable value. You can even query and re-set the counter. The only way I can see this happening is when concurrent transactions commit "out of order". I.e. the transaction that was first bumping the IDENTITY counter commits after another transaction. In that case you'd have a short-lived gap that then gets filled up. Which is necessary to avoid collisions. The IDENTITY counter will never have jumped back though.
W
WhatEvil

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


where is the column properties located at?
c
cheriejw

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


C
Chanuka Naveen Koswatta

You can use the keyword IDENTITY as the data type to the column along with PRIMARY KEY constraint when creating the table.
ex:

StudentNumber IDENTITY(1,1) PRIMARY KEY

In here the first '1' means the starting value and the second '1' is the incrementing value.


S
Softec

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.


You can use ALTER TABLE ... SWITCH to just switch the metadata without having to touch the data at all. e.g. See how to set auto increment after creating a table without any data loss?
Process mentioned in the link provided by you involves transferring the data to temp table and back which does agree with my statement above. If I am wrong please provide me a working example.
There's a working example in the link. There's no " transferring the data to temp table and back". ` ALTER TABLE ... SWITCH` is a metadata only change. It doesn't move any data.
J
Java Main

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.