Re-arrange the ID columns of SQL Server

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: it can be created using the identity attribute so that application developers can specify the identity number (identity seed attribute) in the first row inserted to the table ), determine the increment (identity increment attribute) to be added to the seed to determine the 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: Expressed as a 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 student (ID identity () primary key, name, age) table as an example to provide you with several methods for reference:

 

(1) Clear the table and identify the initial values of the seeds. Note that the following statement clears all the original data in the table.
Truncate table student

 

(2) 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 @ I int

Select @ I = 0

Update student set @ I = @ I + 1, id = @ I

 

(3) to use a temporary table, follow these steps to copy data except the ID in the table to the temporary table, clear the original table, insert data in the temporary table, and delete the temporary table.

-- Import data to a temporary table

Select name, age into # from student order by ID

-- Clears the source table and resets the ID column.

Truncate table student

-- Import data back to the source table

Insert into student select * from #

-- Delete a temporary table

Drop table #

 

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 @ I int

Set @ I = 1

While I <= 100

Begin

Insert into student values ('tbag '+ Cast (@ I as varchar)

, 25 + @ I)

Set @ I = @ I + 1

End