How Can I Efficiently Select a Specific Number of Rows in SQL Server, Even Beyond the sys.all_columns Limit?

Selecting a Specified Number of Rows in SQL Server

In SQL Server, selecting a specific number of rows can be achieved using the ROW_NUMBER() function. However, there are limitations to the number of rows that can be returned.

The Sys.Columns Limitation

The initial query provided does not specify a specific table from which to select rows. When executing this query against sys.all_columns, it will only return up to 7374 rows because that is the total number of rows in that table.

Alternate Approaches

To select a larger number of rows, alternative methods can be employed:

Using a Numbers Table:

DECLARE @start INT = 1;
DECLARE @end INT = 10;

WITH numbers AS (
    SELECT @start AS number
    UNION ALL
    SELECT number + 1 
    FROM  numbers
    WHERE number < @end
)
SELECT *
FROM numbers
OPTION (MAXRECURSION 0);

By using a Numbers Table, an infinite number of rows can be generated. However, this approach can be relatively slow.

Cascaded CTEs for a Tally Table

For improved performance, cascaded CTEs can be used to create a Tally Table:

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 capable of generating a vast number of rows with zero reads. For more performance comparisons and additional details on Tally Tables, refer to the provided links in the response.

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