I have been experimenting with the following (simplified) CTE. When using a table variable () the query runs for minutes before I cancel it. Any of the other commented out methods return in less than a second.

If I replace the whole WHERE clause with an INNER JOIN it is fast as well.

Any ideas why using a table variable would run so slowly?

FWIW: The database contains 2.5 million records and the inner query returns 2 records.

CREATE TABLE #rootTempTable (RootID int PRIMARY KEY)
INSERT INTO #rootTempTable VALUES (1360);

DECLARE @rootTableVar TABLE (RootID int PRIMARY KEY);
INSERT INTO @rootTableVar VALUES (1360);

WITH My_CTE AS 
(
      SELECT ROW_NUMBER() OVER(ORDER BY d.DocumentID) rownum, d.DocumentID, d.Title
      FROM [Document] d
      WHERE d.LocationID IN 
        (
              SELECT LocationID 
              FROM Location 
                    JOIN @rootTableVar rtv ON Location.RootID = rtv.RootID -- VERY SLOW!
                    --JOIN #rootTempTable tt ON Location.RootID = tt.RootID -- Fast
                    --JOIN (SELECT 1360 as RootID) AS rt ON Location.RootID = rt.RootID -- Fast
                    --WHERE RootID = 1360 -- Fast
        )           
) 
SELECT * FROM My_CTE WHERE (rownum > 0) AND (rownum <= 100) ORDER BY rownum