-----------------------------------------------------------
-- Create a working table to play with.
-----------------------------------------------------------

DROP TABLE #Numbers
SELECT TOP 1000000  
	n = ROW_NUMBER() OVER (ORDER BY a.name),
	CalcValue = CAST(NULL AS BIGINT)
INTO #Numbers
FROM master.dbo.syscolumns a, master.dbo.syscolumns b
CREATE UNIQUE CLUSTERED INDEX CIn ON #Numbers ([n] ASC)
 
-----------------------------------------------------------
-- run a test against the table
-----------------------------------------------------------
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT a.*, B.n AS Nextrow 
INTO #junk
FROM #Numbers a
INNER JOIN #Numbers b ON b.n = a.n + 1

-- (999999 row(s) affected) / CPU time = 3516 ms,  elapsed time = 3538 ms.
-- Table 'Worktable'. Scan count 2, logical reads 6224, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SET STATISTICS IO Off
SET STATISTICS TIME Off

DROP TABLE #junk

-----------------------------------------------------------
-- run a functionally similar test against a CTE of the table with ROW_NUMBER() generating "row IDs"
-----------------------------------------------------------
SET STATISTICS IO ON
SET STATISTICS TIME ON

;WITH CTE AS (SELECT NewRowNumber = ROW_NUMBER() OVER (ORDER BY n DESC) FROM #Numbers)
SELECT a.*, B.NewRowNumber AS Nextrow 
INTO #junk
FROM CTE a
INNER JOIN CTE b ON b.NewRowNumber = a.NewRowNumber + 1

-- (999999 row(s) affected) / CPU time = 7781 ms,  elapsed time = 7808 ms.
-- Table 'Worktable'. Scan count 2, logical reads 6224, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SET STATISTICS IO Off
SET STATISTICS TIME Off