ronmoses (12/21/2010)
For educational purposes, I would genuinely appreciate it if one of the folks who take issue with the "most of the time" factor could illustrate a scenario in which that script returns different results.Thanks!
Ron
Does this suffice?
IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test;
CREATE TABLE #Test (RowID INT PRIMARY KEY CLUSTERED);
;WITH
TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),
MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),
TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)
INSERT INTO #Test
SELECT N FROM TALLY;
SELECT TOP (5) * FROM #Test WHERE RowID > 5000;
SELECT TOP (5) * FROM #Test WHERE RowID > 7000;
SELECT TOP (5) * FROM #Test WHERE RowID > 9000;
SELECT * FROM #Test;
IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test;
On my system, the last select starts with 328417.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes