TheSQLGuru (10/11/2010)
We SOOOOOOO need full Windowing Function support in SQL Server. And yes, performance on this type of query will currently be exceptionally poor and approaching non-functional on increasingly large datasets.
Not so. Always check.
-----------------------------------------------------------
-- 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
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden