The user-defined function is used as an example to identify columns. Instead of automatically rearranging numbers, the User-Defined Function automatically fills up numbers.

-- In the example of self-made ID columns, the numbers are not automatically rearranged, but are automatically supplemented:

-- Create a function with the maximum ID
If not object_id ('[f_getid]') is null
Drop function f_getid
Go
Create Function f_getid ()
Returns int
As
Begin
Declare @ ID int
If not exists (select 1 from TB where id = '001 ')
Set @ ID = 1
Else
Begin
Select @ ID = max (ID) from TB
If @ ID is null
Set @ ID = 1
Else
Begin
Declare @ id1 int
Select @ id1 = min (ID) from tb a where ID <> @ ID and not exists (select 1 from TB where id = A. ID + 1)
If @ id1 is not null set @ ID = @ id1
Set @ ID = @ ID + 1
End
End
Lb_re:
Return @ ID
End
Go

-- Create a table
If not object_id ('[TB]') is null
Drop table TB
Go
Create Table Tb (ID int primary key default DBO. f_getid (), name varchar (10 ))
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 ('item 6 ')
Insert into Tb (name) values ('zhang qi ')
Insert into Tb (name) values ('item 8 ')
Insert into Tb (name) values ('zhang JIU ')
Insert into Tb (name) values ('dozens of sheets ')

-- Display the insert result
Select * from TB
/*
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

(Eight rows are affected)

*/
-- Delete some records
Delete from TB where name in ('zhang san', 'zhang 7', 'zhang 8', and 'zhang 10 ')

-- Display the deleted result
Select * from TB
/*
ID name
---------------------
2 sheets 4
3 zhangwu
4 sheets 6
7 sheets 9

(Four rows affected)

*/
-- Insert record again
Insert into Tb (name) values ('Li yi ')
Insert into Tb (name) values ('Lee 2 ')

-- Display the insert result
Select * from TB order by ID
/*
ID name
---------------------
1 Li Yi
2 sheets 4
3 zhangwu
4 sheets 6
5. Li 'er
7 sheets 9

(6 rows affected)
*/

-- Delete the environment
Drop table TB
Drop function f_getid

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/htl258/archive/2009/07/22/4369943.aspx