MSSQL auto-increment column loss ID retrieval-rough Solution

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.