As a database designer, I often create on database tables the primary key as identity autoincrement and sometimes I needed to find missing or deleted IDs thet interrupt the sequence of autoincrement number on the primary key.
To do this you can create a sequence from 1 (or 0 based on setted RESEED) to MAX ID available on the table and than make a LEFT JOIN of this sequence with start table.
The following snippet get the list of IDs of identity autoincrement column that interrupt the sequence of autoincrement values:
DECLARE @START_ID INT, @MAXID INT SET @START_ID = 1 SET @MAXID = (SELECT MAX(ID) FROM [TABLENAME_TO_CHECK]) DECLARE @SEQUENCE_TABLE TABLE (ID BIGINT) --CREATE TABLE WITH ID FROM [@START_ID] TO [MAX TABLE ID] WHILE @START_ID < @MAXID BEGIN INSERT INTO @SEQUENCE_TABLE VALUES(@START_ID) SET @START_ID = @START_ID + 1 END SELECT S.ID AS [MISSING ID] FROM @SEQUENCE_TABLE S LEFT JOIN [TABLENAME_TO_CHECK] AS T ON S.ID = T.ID WHERE T.ID IS NULL
No comments yet.
Leave a comment