Create an ID column by yourself in SQL)

If you use the system identity, this situation occurs: When you delete a record and add a new record, the ID of the new record will be the maximum ID + 1 in the record. in this way, IDs in existing records are interrupted! Therefore, you need to create an ID column to make up for the shortcomings. In the above case, the ID is automatically updated and continuous.
Note: you are not allowed to modify the value of an ID column. Therefore, if you want to automatically rearrange the column after deletion, you cannot use the ID column. Otherwise, it will cause a lot of trouble.

-- Create a function with the maximum ID
Create   Function F_getid ()
Returns   Int
As
Begin
Declare @ ID Int
Select @ ID = Max (ID) From TB
Set @ ID = Isnull (@ ID, 0 ) + 1
Return (@ ID)
End
Go

-- Create a table
Create Table TB (ID Int Default DBO. f_getid () Primary Key , Name Varchar ( 10 ))
Go
-- Create a trigger. When deleting a record in a table, the ID of the record is automatically updated (** if this function is not required, delete this trigger)
Create Trigger T_delete On TB
After Delete
As
Declare @ ID Int , @ Mid Int
Select @ Mid = Min (ID ), @ ID = @ Mid - 1 From Deleted
Update TB Set ID = @ ID , @ ID = @ ID + 1 Where ID > @ Mid
Go
-- Insert Record Test
Insert Into TB (name) Values ( ' Zhang San ' )
Insert Into TB (name) Values ( ' Zhang Si ' )
Insert Into TB (name) Values ( ' Zhang Wu ' )
Insert Into TB (name) Values ( ' Zhang 6 ' )
Insert Into TB (name) Values ( ' Zhang Qi ' )
Insert Into TB (name) Values ( ' Zhang Ba ' )
Insert Into TB (name) Values ( ' Zhang JIU ' )
Insert Into TB (name) Values ( ' Sheet 10 ' )

--Display Insert Results
Select * FromTB

-- Delete some records
Delete From TB Where Name In ( ' Zhang Wu ' , ' Zhang Qi ' , ' Zhang Ba ' , ' Sheet 10 ' )

--Display deleted results
Select * FromTB

-- delete an environment
drop table TB
drop function f_getid

/*-- Test Result
ID name
---------------------
1 Zhang San
2 sheets 4
3 zhangwu
4 sheets 6
5 sheets 7
6 sheets and 8 sheets
7 sheets 9
8x10

(The number of affected rows is 8)

ID name
---------------------
1 Zhang San
2 sheets 4
3 sheets 6
4 sheets 9

(The number of affected rows is 4)
--*/