"to change the original seed value and reseed any existing rows, you must drop the identity column and recreate it specifying the new seed value. when the table contains data, the identity numbers are added to the existing rows with the specified seed and increment values.. Creates an identity column in a table. this property is used with the create table and alter table transact-sql statements. identity columns can be used for generating key values. the identity property on a column guarantees the following: each new value is generated based on the current seed. You cannot alter the definition of an existing column in the database, to add the identity property (nor to remove it). you have to create a new column with the identity property:. alter table mytable add newid int identity(1,1) not null unfortunately, you're not then able to assign the old id values to this new column..
Microsoft sql server's identity column generates sequential values for new records using a seed value. seeding and reseeding an identity column is easy and relatively safe, if you do it correctly.. I'm sure there are excellent reasons that sql server doesn't support removing an identity property from a column via a simple alter table statement, but it's nevertheless making me sad currently that that's the case.. Let's say the table definition is the following: create table beer ( beer_id numeric(10) not null, mnemonic nvarchar(8) ); go let's say that i want the new identities to temporarily start at 12345, and at the end delete the new rows and set the next identity to what it would have been..
