SQL Server 2008 finding missing rows

Question

I have a huge DB and one table in particular is missing rows. I know this to be true but I need to find the gaps and show them. The primary key is the sequence number column. I need to show the actual missing sequence numbers. This table has 76,054,525 rows of data.

Update, the sequence number is provided by our software and each one represents a unique "play" record. Our software does not allow for gaps in play sequence numbers. Any gaps would be missing data.

Here's a similar question/answer: http://stackoverflow.com/questions/1057389/sql-query-to-find-missing-sequence-numbers — mellodev, Feb 19 '13 at 20:05
Why do you need to show the missing sequence numbers? Why do you care? — Aaron Bertrand, Feb 19 '13 at 20:27

Sebastian Meine · Answer 1 · 2013-02-19T20:12:18.633

3

That is a gaps and islands problem and a lot of people have written about how to solve that. For example, Itzik Ben-Gan covered it in chapter 5 of the SQL Server MVP Deep Dives book

However, why are you worried about gaps in your identity column? That is something that naturally occurs and cant be prevented. You can read more about that here: http://sqlity.net/en/792/the-gap-in-the-identity-value-sequence/

edited Feb 19 '13 at 20:12

answered Feb 19 '13 at 20:06

Sebastian Meine

11,260
29
41

@billinkc, just was making the edit to add Itziks book (chapter) myself. Thank you though for improving my answer. – Sebastian Meine Feb 19 '13 at 20:11
1

You stole my answer so it was the least I could do ;) – billinkc Feb 19 '13 at 20:13

SQL Server 2008 finding missing rows

1 Answers1