Find SQL Server self-increment ID values for non-sequential records

In many cases, we will set a field in the database table: ID, which is an identity, that is, a self-increment ID. When the concurrency is large and the field is not a primary key, it is possible to repeat the value, or in some cases (such as when inserting data, or when a user deletes a record using delete), the ID value is not sequential, for example, 1, 2,3,5,6,7,10, then in the middle of a few data, then we want to find these related records in the data, I want to find the record is 3,5,7,10, through these records can view the rules of these records to analyze or statistics, or I need to know that the ID value is not: 4, 8,9.

The core idea of the solution is to get the ID value of the next record in the current record, and then determine if the two ID values are the difference of 1, if not 1 then it means the data is discontinuous.

Execute the following statement to generate the test table and test record

1 --Generate test data2 if exists(Select *  fromsysobjectswhereId= object_id('[t_idnotcontinuous]') and ObjectProperty(ID,'isusertable')= 1) 3 DROP TABLE [t_idnotcontinuous]4 5 CREATE TABLE [t_idnotcontinuous] (6 [ID] [int]  IDENTITY(1,1) not NULL,7 [valuesstring] [nchar](Ten)NULL)8 9 SET Identity_insert [t_idnotcontinuous]  onTen  One INSERT [t_idnotcontinuous]([ID],[valuesstring])VALUES(1,'Test') A INSERT [t_idnotcontinuous]([ID],[valuesstring])VALUES(2,'Test') - INSERT [t_idnotcontinuous]([ID],[valuesstring])VALUES(3,'Test') - INSERT [t_idnotcontinuous]([ID],[valuesstring])VALUES(5,'Test') the INSERT [t_idnotcontinuous]([ID],[valuesstring])VALUES(6,'Test') - INSERT [t_idnotcontinuous]([ID],[valuesstring])VALUES(7,'Test') - INSERT [t_idnotcontinuous]([ID],[valuesstring])VALUES(Ten,'Test') -  + SET Identity_insert [t_idnotcontinuous] OFF -  + Select *  from [t_idnotcontinuous]

(Figure 1: Test table)

1 --Connect to the next record of the current record2 Selectid,new_id3  into [t_idnotcontinuous_temp]4  from (5 Selectid,new_id= (6 Select Top 1Id from [t_idnotcontinuous]7 whereId=(Select min(ID) from [t_idnotcontinuous] whereId>a.id)8 )9  from [t_idnotcontinuous]  asaTen) asb One  A Select *  from [t_idnotcontinuous_temp]

(Figure 2: Misplaced Records)

1 --discontinuous and sequential records2 Select * 3  from [t_idnotcontinuous_temp]4 whereId<>new_id- 15 6 7 --querying original Records8 SelectA.*  from [t_idnotcontinuous]  asa9 Inner Join(Select * Ten  from [t_idnotcontinuous_temp] One whereId<>new_id- 1) asb A  ona.ID>=b.ID anda.ID<=b.new_id - Order  bya.ID

(Figure 3: effects)

Supplement 1: If this ID field is not a primary key, then there will be duplicate ID values (there may be some misoperation, have encountered before) then you need to handle the top. In this case, however, you can use the following simplified statement

1 Select  as = 2 (selectminfromwhere>3from t_idnotcontinuous A

Supplement 2: missing ID value list,

1--Method One: Find the previous record +1, then compare the size2 Select(Select Max(ID)+1 3  from [t_idnotcontinuous] 4 whereId<a.ID) asBeginid,5(ID-1) asEndid6  from [t_idnotcontinuous]a7 where8a.ID>(Select Max(ID)+1  from [t_idnotcontinuous] whereId<a.ID)

(Figure 4: effects)

1 --method Two: All +1, then judge in the original record can not find2 SelectBeginid,3(Select min(ID)-1  from [t_idnotcontinuous] whereId>Beginid) asEndid4  from (  5 SelectId+1  asBeginid from [t_idnotcontinuous]6 whereId+1  not inch 7(SelectId from [t_idnotcontinuous]) 8  andId<(Select Max(ID) from [t_idnotcontinuous])  9) asT

Find non-sequential records of SQL Server self-increment ID values