CODEWITHSUNDEEP
sqlsql-servert-sql
Esty
Esty

Reputation: 1912

Selecting N rows in SQL Server

Following query will return 1-10 in 10 rows.

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

But when I set @Range as 10000 it returns 7374 rows. Why this query can't return more than 7374 rows.

UPDATE

I just found another way to achieve my requirement as following

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

Without last line of code it breaks with error Maximum recursion 100 has been exhausted before statement completion and I found this line is specifying 0 for infinite recursion. But this query seems a little slower to me. Is there any faster way???

Upvotes: 2

Views: 1577

Answers (6)

Felix Pamittan
Felix Pamittan

Reputation: 31879

As commented earlier, it's because you reached the number of rows of sys.columns. Here is another way to generate list of numbers or what others call Numbers Table or Tally Table.

This uses cascaded CTEs and is said to be the fastest way 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

You could easily add another CTE if you need more than 10,000 rows.

For more information about Tally Table, read this excellent article by Jeff Moden.

For performance comparisons among ways to generate Tally Tables, read this.

Explanation taken from Jeff's article:

The CTE called E1 (as in 10E1 for scientific notation) is nothing more than ten SELECT 1's returned as a single result set.

E2 does a CROSS JOIN of E1 with itself. That returns a single result set of 10*10 or up to 100 rows. I say "up to" because if the TOP function is 100 or less, the CTE's are "smart" enough to know that it doesn't actually need to go any further and E4 and E8 won't even come into play. If the TOP has a value of less than 100, not all 100 rows that E2 is capable of making will be made. It'll always make just enough according to the TOP function.

You can follow from there. E4 is a CROSS JOIN of E2 and will make up to 100*100 or 10,000 rows and E8 is a CROSS JOIN of E4 which will make more rows than most people will ever need. If you do need more, then just add an E16 as a CROSS JOIN of E8 and change the final FROM clause to FROM E16.

What's really amazing about this bad-boy is that is produces ZERO READS. Absolutely none, nada, nil.

Upvotes: 12

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

I use this function to get running numbers. You can directly use it in FROM (or with JOIN or APPLY...)

After reading other comments I changed this to the "stacked CTE" approach (thx to Felix)

CREATE FUNCTION [dbo].[GetRunningNumbers](@anzahl INT=10000000, @StartAt INT=0)
RETURNS TABLE
AS 
RETURN
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 AS(
    SELECT TOP(ISNULL(@anzahl,1000000)) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) -1 + ISNULL(@StartAt,0) As Nmbr
    FROM E8
)
SELECT * FROM CteTally;

Upvotes: 0

jpw
jpw

Reputation: 44931

One way to generate a large series of numbers would be to use a cross join to create a cartesian product between two tables which will generate a set that is n^2 in size.

This approach however performs a lot worse than the solution put forward in the answer by Felix Pamittan and therefore shouldn't be used.

DECLARE @Range AS INT = 10000

;WITH CTE AS(
    SELECT TOP (@Range) Duration = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
SELECT Duration from CTE

This would generate a set of 54375876 rows in your case. Instead of generating the rows on the fly you should consider creating a tally table suitable for your needs.

Upvotes: 3

M.Ali
M.Ali

Reputation: 69594

It is because the maximum number of row are 7374, you can use the master..spt_Values table for that cross join to itself and you would get 6325225 values for your desired Duration column.

DECLARE @Range AS INT = 10000 

;WITH CTE AS(
    SELECT TOP (@Range) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Duration
    FROM master..spt_values a cross join master..spt_values b
)
SELECT Duration from CTE

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

It means that the total number of rows return for your query is 7374. If you can create some tables and run the code, you will see the increase in the number

Upvotes: 0

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

when value in variable exceeds value 7374, that doesn't matter. That table has only 7374 rows.

Upvotes: 0

Related Questions