How Can I Efficiently Select a Large Number of Rows in SQL Server?

Selecting Rows in SQL Server

The provided query fails to return more than 7374 rows because it exhausts the number of rows in the sys.columns table. To overcome this limitation, a more efficient approach is to use cascaded CTEs to generate a "Tally Table" or "Numbers Table".

Here's an optimized query using cascaded CTEs:

DECLARE @Range AS INT = 7374

;WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows
CteTally(N) AS(
    SELECT TOP(@Range) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E8
)
SELECT * FROM CteTally

This approach is incredibly efficient because it generates zero reads and scales to extremely large numbers of rows. For performance comparisons and more information on Tally Tables, refer to the linked resources provided in the response.

The above is the detailed content of How Can I Efficiently Select a Large Number of Rows in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!