Announcement: QQ group: 124766907, if you are in. the NET field has unique insights and profound programming skills. It has expertise in a certain field. You are welcome to join this group. This group already has several MVPs, In the SL ,. NET, BS has accomplished people welcome to the group. If you have more than 4 years of experience, do not add it. This group is pursuing the high-end and top-level products. Thank you.
A friend in the group said that there is a good way to retrieve the IDS lost in the auto-increment column. Therefore, I wrote the test table and data for SQL analysis. I recorded the results first, and it seems that I can pass them, however, if the last data is deleted, a BUG exists and the last data cannot be found. For example, if my table is deleted with the last ID = 11, the estimation of ID = 11 cannot be printed. What are some tips?
-- Create a table
Create table [dbo]. [tb] (
[Id] [int] IDENTITY (1, 1) not null,
[Username] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_tb] PRIMARY KEY CLUSTERED
(
[Id] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
-- Insert data
Insert tb values ('wonder0 ')
Insert tb values ('wonder1 ')
Insert tb values ('wonder2 ')
Insert tb values ('wonder3 ')
Insert tb values ('wonder4 ')
Insert tb values ('wonder5 ')
Insert tb values ('wonder6 ')
Insert tb values ('wonder7 ')
Insert tb values ('wonder8 ')
Insert tb values ('wonder9 ')
Insert tb values ('wonder10 ')
Insert tb values ('wonder11 ')
Delete from tb where id in (7, 9)
Select * from tb
-- Query auto-increment ID loss ID
Declare @ totalMax int
Declare @ step int
Declare @ temp int
Declare @ final int
Set @ step = 0
SELECT @ totalMax = max (id) from tb
While @ step <@ totalMax + 1
Begin
Select @ temp = id from tb where id = @ step
If (@ step <> @ temp)
Print @ step
Set @ step = @ step + 1
End