• 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)