Differences
This shows you the differences between two versions of the page.
— | database:mssql:find_gaps [2019/10/31 09:04] (current) – created - external edit 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== Find gaps in sequences in SQL tables ====== | ||
+ | So, you have auto increment column (id) and and want to know if any records have been deleted. For start, you could not delete the records. Instead, use an additional Boolean column, name it ' | ||
+ | Finding duplicates is quite easy (which is not actually the issue here): | ||
+ | |||
+ | < | ||
+ | |||
+ | But in order to detect gaps in sequence ranges and detect how many records have been removed from a table, take a look at following query. It will ignore the gap at the beginning (0 to n) and at the end (n to seed) of the table: | ||
+ | |||
+ | < | ||
+ | FROM tablename AS m LEFT OUTER JOIN tablename AS r ON m.id = r.id -1 WHERE r.id IS NULL) AS X WHERE STOP IS NOT NULL</ | ||
+ | |||
+ | Avoiding subqueries: | ||
+ | |||
+ | < | ||
+ | |||
+ | Of course, this is hardly, if at all, better. The < in the join condition makes the join essentially a CROSS JOIN |