SQL Server ID column reset 1 and arrange

Some records may be deleted when we use the database. Therefore, the identifiers in the data table may not start from 1 or intermittently. How can we reset and arrange it?

SQL Server IDs are often used to increase by itself, that is, the identification column to act as the primary key of a table.

There are two concepts to be clarified:

1. ID column:
Use the identity attribute to create an applicationProgramThe developer can specify the ID number (identity seed attribute) in the first row inserted to the table, and determine the increment (identity increment attribute) to be added to the seed to determine the subsequent ID number. When you insert a value to a table with an identifier column, the next Identifier value is automatically generated by incrementing the seed value.

2. primary key:
It can be expressed by primary key. A column or a group of columns that uniquely identify all rows in a table. NULL values are not allowed for the primary key. There cannot be two rows with the same primary key value, so the primary key value always uniquely identifies a single row.
Then, when there are a lot of data in our table and some delete operations, the ID column is inconsistent. How can we re-arrange the ID columns of existing records in the table from 1, and set the next value of the column to record count plus 1? The following uses a studentid identity (1, 1) primary key, name, age) table as an example to provide you with several methods for your reference:

(1) manual method:

First, cancel the ID column, complete the following statement, and then add the ID column, so that the ID column can be incremented in order. You can use the Enterprise Manager directly.
Declare @ iint select @ I = 0 updatestudent set @ I = @ I 1, id = @ I

(2) automatic method:

to use a temporary table, copy the data except the ID in the table to the temporary table, clear the original table, insert the data in the temporary table, and delete the temporary table.
-- import data to a temporary table
selectname, ageinto # fromstudentorderbyid
-- clears the source table and resets the ID column.
truncatetablestudent // clears the table, specify the initial value of the Seed. Note that the following statement clears all the original data in the table.
-- import data back to the source table
insertintostudentselect * from #
-- delete a temporary table
droptable #
here, by the way, some comrades insert a record when inserting test data, which is a waste of time. We can write a loop statement to speed up the test:
declare @ iint set @ I = 1 while @ I = 100 begin insertintostudentvalues ('tbag' cast (@ I as varchar)
, 25 @ I)
set @ I = @ I 1 end