Selecting N rows in SQL Server -

following query return 1-10 in 10 rows.

declare @range int = 10  ;with cte as(     select top (@range) duration = row_number() over(order object_id)     sys.all_columns     order [object_id] ) select duration cte 

but when set @range 10000 returns 7374 rows. why query can't return more 7374 rows.

update

i found way achieve requirement following

declare @start int = 1; declare @end int = 10;  numbers (     select @start number     union     select number + 1       numbers     number < @end ) select * numbers option (maxrecursion 0); 

without last line of code breaks error maximum recursion 100 has been exhausted before statement completion , found line specifying 0 infinite recursion. query seems little slower me. there faster way???

as commented earlier, it's because reached number of rows of sys.columns. here way generate list of numbers or others call numbers table or tally table.

this uses cascaded ctes , said fastest way create tally table:

declare @range 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 e1 cross join e1 b), -- 10 ^ 2 = 100 rows e4(n) as(select 1 e2 cross join e2 b), -- 10 ^ 4 = 10,000 rows e8(n) as(select 1 e4 cross join e4 b), -- 10 ^ 8 = 10,000,000 rows ctetally(n) as(     select top(@range) row_number() over(order by(select null))     e8 ) select * ctetally 

you add cte if need more 10,000 rows.

for more information tally table, read excellent article jeff moden.

for performance comparisons among ways generate tally tables, read this.

---

explanation taken jeff's article:

the cte called e1 (as in 10e1 scientific notation) nothing more ten select 1's returned single result set.

e2 cross join of e1 itself. returns single result set of 10*10 or 100 rows. "up to" because if top function 100 or less, cte's "smart" enough know doesn't need go further , e4 , e8 won't come play. if top has value of less 100, not 100 rows e2 capable of making made. it'll make enough according top function.

you can follow there. e4 cross join of e2 , make 100*100 or 10,000 rows , e8 cross join of e4 make more rows people ever need. if need more, add e16 cross join of e8 , change final from clause from e16.

what's amazing bad-boy is produces zero reads. absolutely none, nada, nil.