How to Efficiently Select a Specific Number of Rows in SQL Server?

Selecting N Rows in SQL Server

Query 1:

DECLARE @Range AS INT = 10

;WITH CTE AS(
    SELECT TOP (@Range) Duration = ROW_NUMBER() OVER(ORDER BY OBJECT_ID)
    FROM sys.all_columns
    ORDER BY [Object_id]
)
SELECT Duration from CTE

Explanation:

This query is limited by the number of rows in the sys.all_columns table. Therefore, setting @Range to 10,000 will only return 7,374 rows because that is the number of rows in sys.all_columns.

Query 2:

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);

Explanation:

This alternative query uses a recursive common table expression (CTE) to generate a table of numbers from @start to @end. The OPTION (MAXRECURSION 0) is necessary to prevent recursion errors when creating a table with an unlimited number of rows.

Cascaded CTE Solution:

Explanation:

A cascaded CTE approach is the fastest way to create a "Tally Table" for a large number of rows.

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

Advantages of Cascaded CTE:

• Generates rows without any reads from other tables.
• Can easily be extended to create tables with billions of rows.
• Performs well in performance comparisons among other methods.

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