How to find missing numbers of the IDENTITY Column

[tapks]

I have a huge table which contains one Identity column? I find that some numbers are missing in the identity column. How can I find out those nos? Is ther any command for the same?

Help me out.

 

[RickCole]

One way is to create a new table with one identity column and a second int column. Ensure to set the seed value of the new identity column to the lowest number in your source table. Insert all rows from the source table into the second int column of your new table.

You can then run a query like

Select col_1 from New_Table where col_1 not in (Select ident_col from Source_Table)

The results will be the numbers missing from your source table.

Hope this makes sense.

Rick.