SQL Server Identity Seed

Background:

Modifying a column with an identity can make it grow automatically

Examples are:

CREATE TABLE T (ID int NOT null identity (),
Data nvarchar (32));

Inserting data

DECLARE @i as int = 1;
while (@i<10)
Begin
INSERT into T values (replicate (CAST (@i as NCHAR (1)), 10))
Set @i = @i +1;
End

Use DBCC CHECKIDENT (' table_name '); View the seed value of the table.

Delete data:

Delete from T
where ID >1;
Go

DBCC CHECKIDENT (' table_name ');--this time the seed value is still 9

Insert the data again:

Insert into T (Data) VALUES (' AAAA ');

View data:

Solutions,

Reset seed Value:

DBCC CHECKIDENT (' T ', reseed,2);--to this point the problem has been solved

Insert data:

INSERT into T values (' BBB ');

Output:

SELECT * FOM T;

Focus:

This seed value is 2 and the table is a value of 10 what happens if you keep going long? Let's insert a few more data to see

Insert data:

DECLARE @i as int = 1;
while (@i<10)
Begin
INSERT into T values (replicate (' NNN ', 10))
Set @i = @i +1;
End

Output:

Focus 2,

If the ID is the primary key what to do.

The first step:

Delete data

Delete from T
where Data like '%N% '
Go

Step Two:

Add primary Key

ALTER TABLE T
Add constraint pk_id primary key (ID);
Go

SQL Server Identity Seed