CODEWITHSUNDEEP
sqlsql-serverdatabasessms
pnizzle
pnizzle

Reputation: 6431

Does identity column start at the specified seed

Referring to the image attached below which is from SQL Server Management Studio, I have a table with records in it already, and the last record has an id of 7. I wish to have this column auto increment as the primary key (as it did before some unknown stuff-up), obviously with the next record having an id of 8.

enter image description here

Will specifying the seed as 7 mean the next record will be given an id of 7? Or will it be (7 + incrementValue) giving 8?

Upvotes: 2

Views: 350

Answers (3)

KumarHarsh
KumarHarsh

Reputation: 5094

Suppose your table is large,then you have to query to find max id

Simply do this,

DBCC CHECKIDENT ('TableName', RESEED, 1)

Upvotes: 1

NiteRain
NiteRain

Reputation: 703

I think SQL Server will force you to recreate the table with the new identity, and you will have to shift your data into that table where it will start at 1 and increment by 1.

Upvotes: -1

PSK
PSK

Reputation: 17943

SQL Server is intelligent enough, any value you give from 1 to 8 it will start from 8 only.

For example if you give the Identity Seed as 1, and insert a record to the table, SQL will calculate the new Identity as 8 only.

If you specify > 8 in that case only it will consider the seed value.

Note: When you are using SSMS to achieve this, internally table will be dropped and a new table will be created with proper identity.

If you want to do this using an sql query, you have to follow the instruction mentioned in this answer

Upvotes: 3

Related Questions