Silverterra 3d blog

You can give an initial value for the identity column when you are creating the new table.

CREATE TABLE ta_tester (
	Col1 int IDENTITY([startValue],[increment]) NOT NULL,
	Col2 varchar(100)
)

If you want an identity column which one is starts from zero

CREATE TABLE ta_tester (
	Col1 int IDENTITY(0,1) NOT NULL,
	Col2 varchar(100)
)

If the table already created you can override the identity column startValue with the following command:

DBCC CHECKIDENT ( [tableName], RESEED, [startValue])

where the

tableName is the target table name
startValue is not the start value! This is the startValue – 1

it’s meaning if you want to start the identity from zero the correct command will be like this:

DBCC CHECKIDENT ( [tableName], RESEED, -1)

nota bene
If the target table contains data and you changed the identity column actual value to a smaller value you will receive a Violation of PRIMARY KEY constraint error when you try to insert a new record.

In this case you need to set the start value to the last record identity or higher.

If you don’t have primary key constraint on the identity column the server will give the next number for the new row – which one ‘may be’ was previously given already! So in this case you have two records in the table with the same identity…

Thursday, December 26th, 2019 at 20:55.
Filed under mssql.