How to Efficiently Select Rows in SQL Server Beyond the 7374 Row Limit?

Selecting Rows in SQL Server: Handling Large Numbers Beyond 7374 Rows

You may encounter a limitation when attempting to retrieve rows using the ROW_NUMBER function from the sys.all_columns table. Starting from 7374 rows, the query abruptly terminates. This is because you have reached the maximum number of rows available in sys.all_columns.

Cascading CTEs for Generating Large Numbers

To overcome this limitation, you can leverage cascading CTEs to create a "Numbers Table" or "Tally Table." This approach is considered the most efficient method for generating a table of sequential integers. The following snippet demonstrates:

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

Explanation

TheCTE E1 serves as the starting point with ten rows. Subsequent CTEs (E2, E4, and so on) perform cross joins to multiply the number of rows by 10. This cascading approach allows you to generate a vast number of rows efficiently.

Infinite Recursion Avoidance

To prevent an error related to maximum recursion, you can specify a maximum recursion depth of 0 in the Options clause:

OPTION (MAXRECURSION 0)

This instructs the query optimizer to use whatever depth is necessary to complete the execution without limitation.

Performance Considerations

While the recursive approach is straightforward, it may be slower for certain scenarios. For a more optimized solution, consider using other methods such as the following:

• NUMERIC_TABLE() function (Azure SQL Database and SQL Server 2022 and later)
• Recursive Stored Procedure (SQL Server 2000 and later)
• Identity Column with Seed and Increment (SQL Server 2005 and later)

Conclusion

By leveraging the cascading CTE technique or exploring alternative methods, you can efficiently select large numbers of rows in SQL Server, exceeding the limitations encountered with the sys.all_columns table.

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