2

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?

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
pnizzle
  • 6,243
  • 4
  • 52
  • 81
  • 1
    @marc_s my current circumstances don't allow me to experiment with the database and data as you suggest. And I had to do it ASAP, no time to work with a copy/dummy table. Either way, I ended up going with instinct before anyone answered :) – pnizzle Jan 30 '19 at 05:26

3 Answers3

3

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

Dale K
  • 25,246
  • 15
  • 42
  • 71
PSK
  • 17,547
  • 5
  • 32
  • 43
1

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

Simply do this,

DBCC CHECKIDENT ('TableName', RESEED, 1)
KumarHarsh
  • 5,046
  • 1
  • 18
  • 22
-1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
NiteRain
  • 663
  • 8
  • 14
  • Nope, it doesn't. You can modify a table as is. But as PSK mentioned, a new table is created under the covers – pnizzle Jan 30 '19 at 05:28
  • Interesting, I get a message that says: Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes to require the table to be re-created. – NiteRain Jan 30 '19 at 05:53
  • 1
    You can find the override for that error here: https://stackoverflow.com/a/6810442/870565 – pnizzle Jan 30 '19 at 05:56