Typically they're not. You could try splitting out a temporary table from the CTE.

IF OBJECT_ID('tempdb..#Base') IS NOT NULL DROP TABLE #Base;

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

INTO #Base

FROM dbo.OverlappingShapes AS A

INNER JOIN dbo.OverlappingShapes AS B

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

AND A.ShapeID < B.ShapeID;

-- May want to add indexes to #Base here .. CREATE INDEX IX ON #Base(ShapeID_A);

WITH

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