Martin Grape (7/2/2011)
Here is another thing i found and modified a little.Takes under a second for 1 millon rows and returns gaps size sorted 😉
Change tables and fields surrounded by []
----------------------------------------
SELECT
LastSysId + 1 AS GapFrom,
NextSysId - 1 AS GapTo,
NextSysId - (LastSysId + 1) AS GapSize
FROM
(
SELECT
(
SELECT TOP 1
[SysId]
FROM
[yourtable]
WHERE
[SysId] < a.[SysId]
ORDER BY
[SysId] DESC
) AS LastSysId,
a.[SysId] AS NextSysId
FROM
[yourtable] AS a
LEFT JOIN
[kicks_medlem_20110630] AS b ON a.[SysId] = b.[SysId] + 1
WHERE
b.[SysId] IS NULL
) AS a
WHERE
LastSysId IS NOT NULL
ORDER BY
3 DESC
Nicely done and I can verify the speed. The only problem is that the code doesn't recognize missing "SysID" of 1. In fact, it doesn't recognize any gap from 1 to x-1 if all the rows between 1 to x-1 are missing. That could be OK for some folks. For me, it's usually not. It's neither wrong nor right. "It Depends" on what it's being used for.
--Jeff Moden
Change is inevitable... Change for the better is not.