• Here's another way using recursive CTEs

    WITH Base AS (

    SELECT A.ShapeID AS ShapeID_A, B.ShapeID AS ShapeID_B

    FROM dbo.OverlappingShapes AS A

    INNER JOIN dbo.OverlappingShapes AS B

    ON A.Shape.STIntersects(B.Shape) = 1

    AND A.ShapeID < B.ShapeID),

    Recur AS (

    SELECT b.ShapeID_A,

    b.ShapeID_B

    FROM Base b

    WHERE NOT EXISTS(SELECT * FROM Base b2 WHERE b2.ShapeID_B=b.ShapeID_A)

    UNION ALL

    SELECT r.ShapeID_A,

    b.ShapeID_B

    FROM Base b

    INNER JOIN Recur r ON r.ShapeID_B = b.ShapeID_A),

    Results AS (

    SELECT ShapeID_A,

    ShapeID_B

    FROM Recur

    UNION

    SELECT a.ShapeID,

    a.ShapeID

    FROM dbo.OverlappingShapes a

    WHERE NOT EXISTS (SELECT * FROM Recur r WHERE r.ShapeID_B=a.ShapeID))

    SELECT ShapeID_B AS ShapeID,

    DENSE_RANK() OVER(ORDER BY ShapeID_A) AS ClusterID

    FROM Results;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537