database:mssql:find_gaps

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 'deleted' and use it to mark them as deleted. And when selecting data, simply add 'where not deleted'. This way, you retain all the data and may choose to 'empty the trash' when you decide.
  
 +Finding duplicates is quite easy (which is not actually the issue here):
 +
 +<code>SELECT id, COUNT(*) FROM tablename GROUP BY id HAVING COUNT(*) > 1;</code>
 +
 +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:
 +
 +<code>SELECT START,STOP FROM (SELECT m.id+1 AS START,(SELECT MIN(id)-1 FROM tablename AS X WHERE x.id > m.id) AS STOP
 +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</code>
 +
 +Avoiding subqueries:
 +
 +<code>SELECT l.id + 1 AS START, MIN(fr.id) - 1 AS STOP FROM tablename AS l LEFT OUTER JOIN tablename AS r ON l.id = r.id - 1 LEFT OUTER JOIN tablename AS fr ON l.id < fr.id WHERE r.id IS NULL AND fr.id IS NOT NULL GROUP BY l.id, r.id;</code>
 +
 +Of course, this is hardly, if at all, better. The < in the join condition makes the join essentially a CROSS JOIN
  • database/mssql/find_gaps.txt
  • Last modified: 2019/10/31 09:04
  • by 127.0.0.1