How to generate a range of numbers between two numbers?

Question

I have two numbers as input from the user, like for example 1000 and 1050.

How do I generate the numbers between these two numbers, using a sql query, in seperate rows? I want this:

slartidan · Answer 1 · 2021-03-22T04:16:33.440

239

Select non-persisted values with the VALUES keyword. Then use JOINs to generate lots and lots of combinations (can be extended to create hundreds of thousands of rows and beyond).

Short and fast version (not that easy to read):

WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM x ones, x tens, x hundreds, x thousands
ORDER BY 1

Demo

More verbose version:

SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
ORDER BY 1

Demo

Both versions can easily be extended with a WHERE clause, limiting the output of numbers to a user-specified range. If you want to reuse it, you can define a table-valued function for it.

edited Mar 22 '21 at 04:16

answered Oct 15 '15 at 11:06

slartidan

20,403
15
83
131

9

Can you explain the syntax? What is the v(n)? – Rafi Jun 16 '19 at 08:16
2

@Rafi the v(n) and hundreds(n) etc are table and column names/aliases – Twon-ha Oct 08 '19 at 14:13
3

Rather than doing `ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n` you can also just use `row_number() over (order by (select null))` – iamdave Mar 11 '21 at 09:49
@Rafi simply put, you can change v(n) to vals(n) or whatever. See more here: https://stackoverflow.com/questions/14155268/want-to-display-12-months-name-from-sql-server – Mark E. May 23 '21 at 21:05
Please incorporate a hint that the order of values is not guaranteed in a few cases. This can be fixed as shown here https://stackoverflow.com/a/49281857/1789202 – CSharpie Feb 04 '22 at 09:44
@CSharpie The `ORDER BY` guarantees order, in my opinion. – slartidan Feb 04 '22 at 14:08
@slartidan you are right, i completely overlooked this part. – CSharpie Feb 04 '22 at 14:15
You could also avoid manually introducing four lists of values by changing the following: SELECT ones.n + 10*ones.n + 100*ones.n + 1000*ones.n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n). I know it has been a lot since original answer was posted but just found it today looking for something similar. Totally useful, thanks :) – mbd Jun 13 '22 at 16:02
@mbd That would actually yield `0000, 1111, 2222, ..., 9999` (10 records). I think if you want to avoid the duplicated code, you will have to use the first option from the answer. Live-Demo: http://sqlfiddle.com/#!18/9eecb/163403 – slartidan Jun 14 '22 at 18:41
You're right, @slartidan. I was using it with a more complex query and didn't notice the values missing. – mbd Jun 15 '22 at 06:07

score 148 · Answer 2 · answered Jan 29 '14 at 09:28

148

an alternative solution is recursive CTE:

DECLARE @startnum INT=1000
DECLARE @endnum INT=1050
;
WITH gen AS (
    SELECT @startnum AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=@endnum
)
SELECT * FROM gen
option (maxrecursion 10000)

answered Jan 29 '14 at 09:28

Jayvee

10,670
3
29
40

4

Do not try to use the maxrecusion option in a view definition. Instead you must SELECT * FROM CTE_VIEW OPTION (MAXRECURSION 10000) - problematic, if you client application wants to consume the view as it is. – TvdH Nov 11 '15 at 08:38
7

There's a maximum **maxrecursion** set to 32767 (in SQL Server 2012). – BProv Dec 02 '16 at 16:25
8

Just to clarify, if you need a recursion of more than 32767, then it can be set to 0 which means nomax, – Jayvee Jul 10 '17 at 10:35
2

Here is [Demo](http://sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1e1f4fbf1/17578) for this answer. – stomy Jan 03 '18 at 19:00
8

I compared this answer with the others and the Execution plan shows that this answer (_has the least query cost and_) is the fastest. – stomy Jan 03 '18 at 19:06
1

@stomy I wouldn't necessarily trust the query execution plan for profiling; Server Profiler shows this solution to be the slowest on my machine (1000 rows, admittedly anecdotal testing: 1-3 runs). It takes 49-50ms compared to 22-27ms (sys tables) and 11-15ms (table value constructors and cross joins). I've also found that recursive CTEs have a nasty habit of "borking" (scientific term) the query planner - usually resulting in massive row mis-estimations. In this particular query actual vs estimated is 26250%. – Rab Aug 10 '20 at 10:53
Late relpy, I now, but the execution plan for a recursive CTE only shows the batch cost for the first iteration or two. You must NOT use % of Batch or anything else having to do with the execution plan to ultimately decide which code will be the fastest or use the least number of resources. Period. Please see the following reply for proof. https://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers/2663232#2663232 – Jeff Moden Aug 27 '21 at 03:29
1

It's unfortunate this solution cannot be used in a view because of the maxrecursion but on a bright side it can be used in a proc – unpantofar Oct 11 '21 at 05:57

Tim Schmelter · Answer 3 · 2014-01-29T08:35:16.607

54

SELECT DISTINCT n = number 
FROM master..[spt_values] 
WHERE number BETWEEN @start AND @end

Demo

Note that this table has a maximum of 2048 because then the numbers have gaps.

Here's a slightly better approach using a system view(since from SQL-Server 2005):

;WITH Nums AS
(
  SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id]) 
  FROM sys.all_objects 

)
SELECT n FROM Nums 
WHERE n BETWEEN @start AND @end
ORDER BY n;

Demo

or use a custom a number-table. Credits to Aaron Bertrand, i suggest to read the whole article: Generate a set or sequence without loops

edited Jan 29 '14 at 08:35

answered Jan 29 '14 at 08:26

Tim Schmelter

450,073
74
686
939

2

@user3211705: notice my edit, this table has a maximum of 2048. I suggest to read the whole article. – Tim Schmelter Jan 29 '14 at 08:31
4

I _think_ you could add `WHERE type = 'P'` and avoid `SELECT DISTINCT` – Salman A Oct 06 '15 at 13:02
1

Your first "Demo" link keeps telling me `String index out of range: 33` – slartidan Nov 19 '15 at 07:33
1

You're right. But it seems to be an issue with SqlFiddle. Does it work in your DB? – Tim Schmelter Nov 19 '15 at 07:46
6

Quick note, cross-database queries like this don't work with SQL Azure – Kieren Johnstone Oct 08 '18 at 08:09

Habeeb · Answer 4 · 2016-04-24T11:12:45.927

41

The best option I have used is as follows:

DECLARE @min bigint, @max bigint
SELECT @Min=919859000000 ,@Max=919859999999

SELECT TOP (@Max-@Min+1) @Min-1+row_number() over(order by t1.number) as N
FROM master..spt_values t1 
    CROSS JOIN master..spt_values t2

I have generated millions of records using this and it works perfect.

edited Apr 24 '16 at 11:12

answered Jan 25 '16 at 13:44

Habeeb

7,601
1
30
33

3

This is the most elegant solution here, but I think it is hard for many people to understand it (I had been doing this with master.sys.all_columns). @STLDeveloper, yes it works with 2008 and later. – Cetin Basoz Jan 28 '19 at 15:34
it has a limit of 6345361 number of values – Alew Sep 10 '21 at 20:56

Brian Pressler · Answer 5 · 2015-12-23T17:52:38.230

I recently wrote this inline table valued function to solve this very problem. It's not limited in range other than memory and storage. It accesses no tables so there's no need for disk reads or writes generally. It adds joins values exponentially on each iteration so it's very fast even for very large ranges. It creates ten million records in five seconds on my server. It also works with negative values.

CREATE FUNCTION [dbo].[fn_ConsecutiveNumbers]
(   
    @start int,
    @end  int
) RETURNS TABLE 
RETURN 

select
    x268435456.X
    | x16777216.X
    | x1048576.X
    | x65536.X
    | x4096.X
    | x256.X
    | x16.X
    | x1.X
    + @start
     X
from
(VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)) as x1(X)
join
(VALUES (0),(16),(32),(48),(64),(80),(96),(112),(128),(144),(160),(176),(192),(208),(224),(240)) as x16(X)
on x1.X <= @end-@start and x16.X <= @end-@start
join
(VALUES (0),(256),(512),(768),(1024),(1280),(1536),(1792),(2048),(2304),(2560),(2816),(3072),(3328),(3584),(3840)) as x256(X)
on x256.X <= @end-@start
join
(VALUES (0),(4096),(8192),(12288),(16384),(20480),(24576),(28672),(32768),(36864),(40960),(45056),(49152),(53248),(57344),(61440)) as x4096(X)
on x4096.X <= @end-@start
join
(VALUES (0),(65536),(131072),(196608),(262144),(327680),(393216),(458752),(524288),(589824),(655360),(720896),(786432),(851968),(917504),(983040)) as x65536(X)
on x65536.X <= @end-@start
join
(VALUES (0),(1048576),(2097152),(3145728),(4194304),(5242880),(6291456),(7340032),(8388608),(9437184),(10485760),(11534336),(12582912),(13631488),(14680064),(15728640)) as x1048576(X)
on x1048576.X <= @end-@start
join
(VALUES (0),(16777216),(33554432),(50331648),(67108864),(83886080),(100663296),(117440512),(134217728),(150994944),(167772160),(184549376),(201326592),(218103808),(234881024),(251658240)) as x16777216(X)
on x16777216.X <= @end-@start
join
(VALUES (0),(268435456),(536870912),(805306368),(1073741824),(1342177280),(1610612736),(1879048192)) as x268435456(X)
on x268435456.X <= @end-@start
WHERE @end >=
    x268435456.X
    | isnull(x16777216.X, 0)
    | isnull(x1048576.X, 0)
    | isnull(x65536.X, 0)
    | isnull(x4096.X, 0)
    | isnull(x256.X, 0)
    | isnull(x16.X, 0)
    | isnull(x1.X, 0)
    + @start

GO

SELECT X FROM fn_ConsecutiveNumbers(5, 500);

It's handy for date and time ranges as well:

SELECT DATEADD(day,X, 0) DayX 
FROM fn_ConsecutiveNumbers(datediff(day,0,'5/8/2015'), datediff(day,0,'5/31/2015'))

SELECT DATEADD(hour,X, 0) HourX 
FROM fn_ConsecutiveNumbers(datediff(hour,0,'5/8/2015'), datediff(hour,0,'5/8/2015 12:00 PM'));

You could use a cross apply join on it to split records based on values in the table. So for example to create a record for every minute on a time range in a table you could do something like:

select TimeRanges.StartTime,
    TimeRanges.EndTime,
    DATEADD(minute,X, 0) MinuteX
FROM TimeRanges
cross apply fn_ConsecutiveNumbers(datediff(hour,0,TimeRanges.StartTime), 
        datediff(hour,0,TimeRanges.EndTime)) ConsecutiveNumbers

it works for 1-100 but then fails. Even your example of generating 5-500 does not work for me, it shows 5, 21, ... 484, 500 — Rez.Net, Jul 13 '18 at 06:09
If you want it sorted you'll have to add an order by clause: `SELECT X FROM fn_ConsecutiveNumbers(5, 500) ORDER BY X;` — Brian Pressler, Jul 13 '18 at 13:47

score 23 · Answer 6 · edited Jul 10 '17 at 08:03

23

It work for me !

select top 50 ROW_NUMBER() over(order by a.name) + 1000 as Rcount
from sys.all_objects a

edited Jul 10 '17 at 08:03

freedomn-m

27,664
8
35
57

answered Mar 20 '17 at 07:19

Nguyen Son Tung

331
3
5

2

Nice one-liner - but be warned that the max number of rows will depend on `sys.all_objects` - for small ranges <2000 items, this is not an issue. Not sure if it will have permissions problems? perfect for quickly generating a batch of test data. – freedomn-m Jul 10 '17 at 08:11
@freedomn-m One way to increase the maximum rows would be to to perform a self-cross join. `select top 50 ROW_NUMBER() over(order by a.name) + 1000 as Rcount from sys.all_objects a, sys.all_objects b`. Where I could only generate 2384 rows before, I can now generate 5683456 rows. – devklick Apr 20 '19 at 12:56

cesargroening · Answer 7 · 2021-02-03T15:43:11.917

16

I do it with recursive ctes, but i'm not sure if it is the best way

declare @initial as int = 1000;
declare @final as int =1050;

with cte_n as (
    select @initial as contador
    union all
    select contador+1 from cte_n 
    where contador <@final
) select * from cte_n option (maxrecursion 0)

saludos.

edited Feb 03 '21 at 15:43

answered Jul 26 '18 at 16:19

cesargroening

323
2
6

1

This was very useful. I modified the code so I can insert 100.000 rows. With my solution it took like 13 minutes; using yours, it took five seconds. Muchísimas gracias. – Cthulhu Aug 25 '18 at 18:12
3

Actually, recursive CTE's are one of the worst ways to count. They can even be beaten by a While Loop in a transaction and the While Loop will produce far fewer reads. The cCTE method (Cascading CTEs, originally by Itizik Ben-Gan) is much faster and produces zero reads. – Jeff Moden Mar 21 '20 at 01:39

score 12 · Answer 8 · edited Apr 09 '19 at 07:24

12

declare @start int = 1000
declare @end    int =1050

;with numcte  
AS  
(  
  SELECT @start [SEQUENCE]  
  UNION all  
  SELECT [SEQUENCE] + 1 FROM numcte WHERE [SEQUENCE] < @end 
)      
SELECT * FROM numcte

edited Apr 09 '19 at 07:24

Dale K

25,246
15
42
71

answered Oct 03 '18 at 12:58

Sowbarani Karthikeyan

161
1
2
8

1

Is this different than the answer by @Jayvee? – Noel Oct 03 '18 at 13:25
1

Yeah in where condition its mentioned as num + 1 < 1050 which will print upto 1049 only. – Sowbarani Karthikeyan Oct 03 '18 at 13:42
2

An edit (or comment) to the existing answer that is essential the same would provide more value than an entirely new answer. – Noel Oct 03 '18 at 14:49

AlexDev · Answer 9 · 2016-09-28T14:06:08.810

If you don't have a problem installing a CLR assembly in your server a good option is writing a table valued function in .NET. That way you can use a simple syntax, making it easy to join with other queries and as a bonus won't waste memory because the result is streamed.

Create a project containing the following class:

using System;
using System.Collections;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace YourNamespace
{
   public sealed class SequenceGenerator
    {
        [SqlFunction(FillRowMethodName = "FillRow")]
        public static IEnumerable Generate(SqlInt32 start, SqlInt32 end)
        {
            int _start = start.Value;
            int _end = end.Value;
            for (int i = _start; i <= _end; i++)
                yield return i;
        }

        public static void FillRow(Object obj, out int i)
        {
            i = (int)obj;
        }

        private SequenceGenerator() { }
    }
}

Put the assembly somewhere on the server and run:

USE db;
CREATE ASSEMBLY SqlUtil FROM 'c:\path\to\assembly.dll'
WITH permission_set=Safe;

CREATE FUNCTION [Seq](@start int, @end int) 
RETURNS TABLE(i int)
AS EXTERNAL NAME [SqlUtil].[YourNamespace.SequenceGenerator].[Generate];

Now you can run:

select * from dbo.seq(1, 1000000)

I tried this solution out and it works well, just not super fast. If you're generating just 1,000 numbers, or maybe 10,000, it's fairly quick. If you're like me and have to generate billions of numbers, Brian Pressler's solution below is unbelievably fast compared to SQL CLR. — Derreck Dean, Sep 26 '16 at 18:25
@DerreckDean You are right. I think his is the best solution because it's easy to create and use (and fast as you say). In my case I already had an assembly for concatenating strings so I just added it there. — AlexDev, Sep 28 '16 at 14:03
I had an existing assembly as well and tried both methods. I'm generating an indeterminate number of numbers to add to dates (basically, I recreated the SQL server agent scheduler for generating dates for our in-house application, and 100 levels of recursion wasn't going to cut it for generating multiple years of datetimes, possibly down to the second.), so I was able to thoroughly test multiple solutions from this thread. I appreciate your contribution! — Derreck Dean, Sep 28 '16 at 14:06

score 8 · Answer 10 · edited May 23 '17 at 12:34

slartidan's answer can be improved, performance wise, by eliminating all references to the cartesian product and using ROW_NUMBER() instead (execution plan compared):

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM 
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x1(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x2(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x3(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x4(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x5(x)
ORDER BY n

Wrap it inside a CTE and add a where clause to select desired numbers:

DECLARE @n1 AS INT = 100;
DECLARE @n2 AS INT = 40099;
WITH numbers AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM 
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x1(x),
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x2(x),
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x3(x),
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x4(x),
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x5(x)
)
SELECT numbers.n
FROM numbers
WHERE n BETWEEN @n1 and @n2
ORDER BY n

ROW_NUMBER only starts at 1. How can we start from zero with your method? — stomy, Jan 03 '18 at 18:47
@stomy `SELECT ROW_NUMBER() OVER (...) - 1 AS n`. In some cases this might kill performance. — Salman A, Jan 04 '18 at 07:54

score 8 · Answer 11 · answered Nov 06 '18 at 13:58

Nothing new but I rewrote Brian Pressler solution to be easier on the eye, it might be useful to someone (even if it's just future me):

alter function [dbo].[fn_GenerateNumbers]
(   
    @start int,
    @end  int
) returns table
return

with 
b0 as (select n from (values (0),(0x00000001),(0x00000002),(0x00000003),(0x00000004),(0x00000005),(0x00000006),(0x00000007),(0x00000008),(0x00000009),(0x0000000A),(0x0000000B),(0x0000000C),(0x0000000D),(0x0000000E),(0x0000000F)) as b0(n)),
b1 as (select n from (values (0),(0x00000010),(0x00000020),(0x00000030),(0x00000040),(0x00000050),(0x00000060),(0x00000070),(0x00000080),(0x00000090),(0x000000A0),(0x000000B0),(0x000000C0),(0x000000D0),(0x000000E0),(0x000000F0)) as b1(n)),
b2 as (select n from (values (0),(0x00000100),(0x00000200),(0x00000300),(0x00000400),(0x00000500),(0x00000600),(0x00000700),(0x00000800),(0x00000900),(0x00000A00),(0x00000B00),(0x00000C00),(0x00000D00),(0x00000E00),(0x00000F00)) as b2(n)),
b3 as (select n from (values (0),(0x00001000),(0x00002000),(0x00003000),(0x00004000),(0x00005000),(0x00006000),(0x00007000),(0x00008000),(0x00009000),(0x0000A000),(0x0000B000),(0x0000C000),(0x0000D000),(0x0000E000),(0x0000F000)) as b3(n)),
b4 as (select n from (values (0),(0x00010000),(0x00020000),(0x00030000),(0x00040000),(0x00050000),(0x00060000),(0x00070000),(0x00080000),(0x00090000),(0x000A0000),(0x000B0000),(0x000C0000),(0x000D0000),(0x000E0000),(0x000F0000)) as b4(n)),
b5 as (select n from (values (0),(0x00100000),(0x00200000),(0x00300000),(0x00400000),(0x00500000),(0x00600000),(0x00700000),(0x00800000),(0x00900000),(0x00A00000),(0x00B00000),(0x00C00000),(0x00D00000),(0x00E00000),(0x00F00000)) as b5(n)),
b6 as (select n from (values (0),(0x01000000),(0x02000000),(0x03000000),(0x04000000),(0x05000000),(0x06000000),(0x07000000),(0x08000000),(0x09000000),(0x0A000000),(0x0B000000),(0x0C000000),(0x0D000000),(0x0E000000),(0x0F000000)) as b6(n)),
b7 as (select n from (values (0),(0x10000000),(0x20000000),(0x30000000),(0x40000000),(0x50000000),(0x60000000),(0x70000000)) as b7(n))

select s.n
from (
    select
          b7.n
        | b6.n
        | b5.n
        | b4.n
        | b3.n
        | b2.n
        | b1.n
        | b0.n
        + @start
         n
    from b0
    join b1 on b0.n <= @end-@start and b1.n <= @end-@start
    join b2 on b2.n <= @end-@start
    join b3 on b3.n <= @end-@start
    join b4 on b4.n <= @end-@start
    join b5 on b5.n <= @end-@start
    join b6 on b6.n <= @end-@start
    join b7 on b7.n <= @end-@start
) s
where @end >= s.n

GO

I believe you've distilled the essence of a beautiful algorithm into some downright pretty code. — Clay, Jan 18 '19 at 21:30
The results are ordered in a weird but not chaotic order. Test it on range from 5 to 500. It returns 5,21,37,..., 245,6,22,... Do you know how ordering would influence performance? Solutions based on `ROW_NUMBER()` do not have that problem. — Przemyslaw Remin, Feb 08 '19 at 09:52
I'm not an expert but intuitively I would guess the SQL server will need to put all results in memory and order them before returning them so more memory usage and delayed response as opposed to just streaming the results as they come. — Guillaume86, Feb 11 '19 at 11:07

score 8 · Answer 12 · edited Feb 22 '23 at 06:37

If your SQL-server version is higher than 2022 or supports GENERATE_SERIES function, we can try to use GENERATE_SERIES function and declare START and STOP parameters.

GENERATE_SERIES returns a single-column table containing a sequence of values in which each differs from the preceding by STEP

declare @start int = 1000
declare @stop int = 1050
declare @step int = 2
SELECT [Value]
FROM GENERATE_SERIES(@start, @stop, @step)

score 7 · Answer 13 · answered Mar 14 '18 at 15:34

I know I'm 4 years too late, but I stumbled upon yet another alternative answer to this problem. The issue for speed isn't just pre-filtering, but also preventing sorting. It's possible to force the join-order to execute in a manner that the Cartesian product actually counts up as a result of the join. Using slartidan's answer as a jump-off point:

    WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM x ones,     x tens,      x hundreds,       x thousands
ORDER BY 1

If we know the range we want, we can specify it via @Upper and @Lower. By combining the join hint REMOTE along with TOP, we can calculate only the subset of values we want with nothing wasted.

WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
SELECT TOP (1+@Upper-@Lower) @Lower + ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM x thousands
INNER REMOTE JOIN x hundreds on 1=1
INNER REMOTE JOIN x tens on 1=1
INNER REMOTE JOIN x ones on 1=1

The join hint REMOTE forces the optimizer to compare on the right side of the join first. By specifying each join as REMOTE from most to least significant value, the join itself will count upwards by one correctly. No need to filter with a WHERE, or sort with an ORDER BY.

If you want to increase the range, you can continue to add additional joins with progressively higher orders of magnitude, so long as they're ordered from most to least significant in the FROM clause.

Note that this is a query specific to SQL Server 2008 or higher.

Very nice indeed. The same technique can be applied to Brian Pressler's excellent answer and Guillaume86's lovely rewrite, too. — Clay, Jan 18 '19 at 21:45
You are the cure to my headache. We had the issue that the function was generateing arbitrary ordered values on one of our customers machines. This fixed it — CSharpie, Feb 04 '22 at 09:42

beetle · Answer 14 · 2017-04-06T12:30:52.483

6

2 years later, but I found I had the same issue. Here is how I solved it. (edited to include parameters)

DECLARE @Start INT, @End INT
SET @Start = 1000
SET @End = 1050

SELECT  TOP (@End - @Start+1) ROW_NUMBER() OVER (ORDER BY S.[object_id])+(@Start - 1) [Numbers]
FROM    sys.all_objects S WITH (NOLOCK)

edited Apr 06 '17 at 12:30

answered Nov 22 '16 at 17:40

beetle

61
1
4

jumxozizi · Answer 15 · 2016-09-22T16:21:09.990

Here are couple quite optimal and compatible solutions:

USE master;

declare @min as int;    set @min = 1000;
declare @max as int;    set @max = 1050;    --null returns all

--  Up to 256 - 2 048 rows depending on SQL Server version
select  isnull(@min,0)+number.number  as  number
FROM    dbo.spt_values  AS  number
WHERE   number."type"                   =   'P'     --integers
    and (   @max                            is null     --return all
        or  isnull(@min,0)+number.number    <=  @max    --return up to max
    )
order by    number
;

--  Up to 65 536 - 4 194 303 rows depending on SQL Server version
select  isnull(@min,0)+value1.number+(value2.number*numberCount.numbers)  as  number
FROM  dbo.spt_values            AS  value1
  cross join  dbo.spt_values    AS  value2
  cross join (  --get the number of numbers (depends on version)
    select  sum(1)  as  numbers
    from    dbo.spt_values
    where   spt_values."type"   =   'P' --integers
  )                             as  numberCount
WHERE   value1."type" = 'P'   --integers
    and value2."type" = 'P'   --integers
    and (   @max    is null     --return all
        or  isnull(@min,0)+value1.number+(value2.number*numberCount.numbers)    
            <=  @max            --return up to max
    )
order by    number
;

Is this method somehow better than simply `select`ing `where spt_values.number between @min and @max`? — underscore_d, Sep 11 '15 at 08:48
Type='P' filter is required to prevent duplicate numbers. With this filter the table will return numbers 0 - 2047. So "number between @min and @max" filter will work as long as the variables are within that range. My solution will allow you to get up to 2048 rows within integer range (-2,147,483,648) - (2,147,483,647). — jumxozizi, Sep 11 '15 at 13:42
the above logic is useful only when the difference between the maximum and minimum number less than 2048 and once can a maximum of 2048 records at a point of time — Smart003, Mar 09 '16 at 07:15

score 4 · Answer 16 · answered Feb 13 '17 at 03:07

4

recursive CTE in exponential size (even for default of 100 recursion, this can build up to 2^100 numbers):

DECLARE @startnum INT=1000
DECLARE @endnum INT=1050
DECLARE @size INT=@endnum-@startnum+1
;
WITH numrange (num) AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num*2 FROM numrange WHERE num*2<=@size
    UNION ALL
    SELECT num*2+1 FROM numrange WHERE num*2+1<=@size
)
SELECT num+@startnum-1 FROM numrange order by num

answered Feb 13 '17 at 03:07

user7555577

41
1

According to the OP, I think `@startnum` and `endnum` should be input by user? – J.C Feb 13 '17 at 03:20

score 4 · Answer 17 · answered Oct 01 '20 at 08:06

Update for SQL 2017 and later: If the sequence you desire is < 8k then this will work:

Declare @start_num int = 1000
,   @end_num int = 1050

Select [number] = @start_num + ROW_NUMBER() over (order by (Select null))
from string_split(replicate(' ',@end_num-@start_num-1),' ')

score 3 · Answer 18 · answered Jul 14 '16 at 17:08

This will also do

DECLARE @startNum INT = 1000;
DECLARE @endNum INT = 1050;
INSERT  INTO dbo.Numbers
        ( Num
        )
        SELECT  CASE WHEN MAX(Num) IS NULL  THEN @startNum
                     ELSE MAX(Num) + 1
                END AS Num
        FROM    dbo.Numbers
GO 51

Farhad Manafi · Answer 19 · 2016-09-04T09:13:48.013

3

The best speed when run query

DECLARE @num INT = 1000
WHILE(@num<1050)
begin
 INSERT  INTO [dbo].[Codes]
    (   Code
    ) 
    VALUES (@num)
    SET @num = @num + 1
end

edited Sep 04 '16 at 09:13

answered Sep 04 '16 at 09:05

Farhad Manafi

334
1
8
17

score 2 · Answer 20 · answered Sep 19 '16 at 14:46

I had to insert picture filepath into database using similar method. The query below worked fine:

DECLARE @num INT = 8270058
WHILE(@num<8270284)
begin
    INSERT  INTO [dbo].[Galleries]
    (ImagePath) 
    VALUES 
    ('~/Content/Galeria/P'+CONVERT(varchar(10), @num)+'.JPG')

    SET @num = @num + 1
end

The code for you would be:

DECLARE @num INT = 1000
WHILE(@num<1051)
begin
    SELECT @num

    SET @num = @num + 1
end

score 2 · Answer 21 · answered Feb 13 '18 at 00:10

Here's what I came up with:

create or alter function dbo.fn_range(@start int, @end int)  returns table
return
with u2(n) as (
    select n 
    from (VALUES (0),(1),(2),(3)) v(n)
), 
u8(n) as (
    select
        x0.n | x1.n * 4 | x2.n * 16 | x3.n * 64 as n
    from u2 x0, u2 x1, u2 x2, u2 x3
)
select 
    @start + s.n as n
from (
    select
        x0.n | isnull(x1.n, 0) * 256 | isnull(x2.n, 0) * 65536 as n
    from u8 x0 
    left join u8 x1 on @end-@start > 256
    left join u8 x2 on @end-@start > 65536
) s
where s.n < @end - @start

Generates up to 2^24 values. Join conditions keep it fast for small values.

Robert Calhoun · Answer 22 · 2018-07-05T19:32:43.597

This uses procedural code and a table-valued function. Slow, but easy and predictable.

CREATE FUNCTION [dbo].[Sequence] (@start int, @end int)
RETURNS
@Result TABLE(ID int)
AS
begin
declare @i int;
set @i = @start;
while @i <= @end 
    begin
        insert into @result values (@i);
        set @i = @i+1;
    end
return;
end

Usage:

SELECT * FROM dbo.Sequence (3,7);
ID
3
4
5
6
7

It's a table, so you can use it in joins with other data. I most frequently use this function as the left side of a join against a GROUP BY hour, day etc to ensure a contiguous sequence of time values.

SELECT DateAdd(hh,ID,'2018-06-20 00:00:00') as HoursInTheDay FROM dbo.Sequence (0,23) ;

HoursInTheDay
2018-06-20 00:00:00.000
2018-06-20 01:00:00.000
2018-06-20 02:00:00.000
2018-06-20 03:00:00.000
2018-06-20 04:00:00.000
(...)

Performance is uninspiring (16 seconds for a million rows) but good enough for many purposes.

SELECT count(1) FROM [dbo].[Sequence] (
   1000001
  ,2000000)
GO

This should be the accepted answer. Intuitive and straight to the point. Also, for 1 million rows, 16 seconds is not too much! On my laptop, it took 12 seconds for 1 million rows. — Yahya, Jul 22 '23 at 14:43

JohnnyIV · Answer 23 · 2019-03-27T20:28:12.600

This is what I do, it's pretty fast and flexible and not a lot of code.

DECLARE @count  int =   65536;
DECLARE @start  int =   11;
DECLARE @xml    xml =   REPLICATE(CAST('<x/>' AS nvarchar(max)), @count);

; WITH GenerateNumbers(Num) AS
(
    SELECT  ROW_NUMBER() OVER (ORDER BY @count) + @start - 1
    FROM    @xml.nodes('/x') X(T)
)
SELECT  Num
FROM    GenerateNumbers;

Note that (ORDER BY @count) is a dummy. It doesn't do anything but ROW_NUMBER() requires an ORDER BY.

Edit: I realized that the original question was to get a range from x to y. My script can be modified like this to get a range:

DECLARE @start  int =   5;
DECLARE @end    int =   21;
DECLARE @xml    xml =   REPLICATE(CAST('<x/>' AS nvarchar(max)), @end - @start + 1);

; WITH GenerateNumbers(Num) AS
(
    SELECT  ROW_NUMBER() OVER (ORDER BY @end) + @start - 1
    FROM    @xml.nodes('/x') X(T)
)
SELECT  Num
FROM    GenerateNumbers;

Zhorov · Answer 24 · 2023-02-08T08:48:41.150

For all versions, starting from SQL Server 2016, you may try a JSON-based approach. The idea is to build a JSON array and parse this array with OPENJSON(). The result is a table with columns key, value and type and the key column returns the 0-based index of each item in the array. The series are generated with a simple calculation:

DECLARE @start int = 1000
DECLARE @stop int  = 1500
DECLARE @step int  = 1  

SELECT CONVERT(int, [key]) * @step + @start AS [value]
FROM OPENJSON('[1' + REPLICATE(',1', (@stop - @start) / @step) + ']')

Starting from SQL Server 2022 , GENERATE_SERIES() provides another option:

DECLARE @start int = 1000
DECLARE @stop int  = 1500
DECLARE @step int  = 1  

SELECT [value]
FROM GENERATE_SERIES(@start, @stop, @step)

score 1 · Answer 25 · answered Jan 22 '15 at 10:28

-- Generate Numeric Range
-- Source: http://www.sqlservercentral.com/scripts/Miscellaneous/30397/

CREATE TABLE #NumRange(
    n int
)

DECLARE @MinNum int
DECLARE @MaxNum int
DECLARE @I int

SET NOCOUNT ON

SET @I = 0
WHILE @I <= 9 BEGIN
    INSERT INTO #NumRange VALUES(@I)
    SET @I = @I + 1
END


SET @MinNum = 1
SET @MaxNum = 1000000

SELECT  num = a.n +
    (b.n * 10) +
    (c.n * 100) +
    (d.n * 1000) +
    (e.n * 10000)
FROM    #NumRange a
CROSS JOIN #NumRange b
CROSS JOIN #NumRange c
CROSS JOIN #NumRange d
CROSS JOIN #NumRange e
WHERE   a.n +
    (b.n * 10) +
    (c.n * 100) +
    (d.n * 1000) +
    (e.n * 10000) BETWEEN @MinNum AND @MaxNum
ORDER BY a.n +
    (b.n * 10) +
    (c.n * 100) +
    (d.n * 1000) +
    (e.n * 10000) 

DROP TABLE #NumRange

score 1 · Answer 26 · answered Apr 28 '15 at 16:55

This only works for sequences as long as some application table has rows. Assume I want sequence from 1..100, and have application table dbo.foo with column (of numeric or string type) foo.bar:

select 
top 100
row_number() over (order by dbo.foo.bar) as seq
from dbo.foo

Despite its presence in an order by clause, dbo.foo.bar does not have to have distinct or even non-null values.

Of course, SQL Server 2012 has sequence objects, so there's a natural solution in that product.

J Bryan Price · Answer 27 · 2018-03-05T20:22:28.590

This completed for me in 36 seconds on our DEV server. Like Brian's answer, focusing on filtering to the range is important from within the query; a BETWEEN still tries to generate all the initial records prior to the lower bound even though it doesn't need them.

declare @s bigint = 10000000
    ,   @e bigint = 20000000

;WITH 
Z AS (SELECT 0 z FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)) T(n)),
Y AS (SELECT 0 z FROM Z a, Z b, Z c, Z d, Z e, Z f, Z g, Z h, Z i, Z j, Z k, Z l, Z m, Z n, Z o, Z p),
N AS (SELECT ROW_NUMBER() OVER (PARTITION BY 0 ORDER BY z) n FROM Y)

SELECT TOP (1+@e-@s) @s + n - 1 FROM N

Note that ROW_NUMBER is a bigint, so we can't go over 2^^64 (==16^^16) generated records with any method that uses it. This query therefore respects the same upper limit on generated values.

score 1 · Answer 28 · answered Sep 15 '18 at 20:41

1

Oracle 12c; Quick but limited:

select rownum+1000 from all_objects fetch first 50 rows only;

Note: limited to row count of all_objects view;

answered Sep 15 '18 at 20:41

Witold Kaczurba

9,845
3
58
67

score 1 · Answer 29 · answered May 31 '19 at 22:00

The solution I've developed and used for quite some time now (riding some on the shared works of others) is slightly similar to at least one posted. It doesn't reference any tables and returns an unsorted range of up to 1048576 values (2^20) and can include negatives if desired. You can of course sort the result if necessary. It runs pretty quickly, especially on smaller ranges.

Select value from dbo.intRange(-500, 1500) order by value  -- returns 2001 values

create function dbo.intRange 
(   
    @Starting as int,
    @Ending as int
)
returns table
as
return (
    select value
    from (
        select @Starting +
            ( bit00.v | bit01.v | bit02.v | bit03.v
            | bit04.v | bit05.v | bit06.v | bit07.v
            | bit08.v | bit09.v | bit10.v | bit11.v
            | bit12.v | bit13.v | bit14.v | bit15.v
            | bit16.v | bit17.v | bit18.v | bit19.v
            ) as value
        from       (select 0 as v union ALL select 0x00001 as v) as bit00
        cross join (select 0 as v union ALL select 0x00002 as v) as bit01
        cross join (select 0 as v union ALL select 0x00004 as v) as bit02
        cross join (select 0 as v union ALL select 0x00008 as v) as bit03
        cross join (select 0 as v union ALL select 0x00010 as v) as bit04
        cross join (select 0 as v union ALL select 0x00020 as v) as bit05
        cross join (select 0 as v union ALL select 0x00040 as v) as bit06
        cross join (select 0 as v union ALL select 0x00080 as v) as bit07
        cross join (select 0 as v union ALL select 0x00100 as v) as bit08
        cross join (select 0 as v union ALL select 0x00200 as v) as bit09
        cross join (select 0 as v union ALL select 0x00400 as v) as bit10
        cross join (select 0 as v union ALL select 0x00800 as v) as bit11
        cross join (select 0 as v union ALL select 0x01000 as v) as bit12
        cross join (select 0 as v union ALL select 0x02000 as v) as bit13
        cross join (select 0 as v union ALL select 0x04000 as v) as bit14
        cross join (select 0 as v union ALL select 0x08000 as v) as bit15
        cross join (select 0 as v union ALL select 0x10000 as v) as bit16
        cross join (select 0 as v union ALL select 0x20000 as v) as bit17
        cross join (select 0 as v union ALL select 0x40000 as v) as bit18
        cross join (select 0 as v union ALL select 0x80000 as v) as bit19
    ) intList
    where @Ending - @Starting < 0x100000
        and intList.value between @Starting and @Ending
)

score 1 · Answer 30 · answered Apr 15 '20 at 08:58

;WITH u AS (
    SELECT Unit FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(Unit)
),
d AS (
    SELECT 
        (Thousands+Hundreds+Tens+Units) V
    FROM 
           (SELECT Thousands = Unit * 1000 FROM u) Thousands 
           ,(SELECT Hundreds = Unit * 100 FROM u) Hundreds 
           ,(SELECT Tens = Unit * 10 FROM u) Tens 
           ,(SELECT Units = Unit FROM u) Units
    WHERE
           (Thousands+Hundreds+Tens+Units) <= 10000
)

SELECT * FROM d ORDER BY v

score 1 · Answer 31 · answered May 12 '20 at 15:24

I made the below function after reading this thread. Simple and fast:

go
create function numbers(@begin int, @len int)
returns table as return
with d as (
    select 1 v from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(v)
)
select top (@len) @begin -1 + row_number() over(order by (select null)) v
from d d0
cross join d d1
cross join d d2
cross join d d3
cross join d d4
cross join d d5
cross join d d6
cross join d d7
go

select * from numbers(987654321,500000)

score 1 · Answer 32 · answered Aug 27 '21 at 11:07

1

much more simpler solution available with https://www.postgresql.org/docs/9.1/queries-with.html

WITH RECURSIVE t(n) AS (
    SELECT 1
  UNION ALL
    SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

answered Aug 27 '21 at 11:07

Rahul Pawar

29
1

2

This type of answer is covered by at least 3 other answers all written over 6 years ago. When answering older posts you need to include explanation as to why your solution is superior to the others already voted for. This in fact is not a valid answers, it is for postgres and OP specifically tagged this with MS SQL Server, In fact there are inherent problems with this simple recursive CTE that make it a well established anti-pattern that should be avoided for all but very small number ranges. So while this works for 0-100, it is not advisable for production systems. – Chris Schaller Aug 27 '21 at 11:23
1

Please add further details to expand on your answer, such as working code or documentation citations. – Community Aug 27 '21 at 11:23

score 1 · Answer 33 · answered Oct 13 '21 at 08:19

If you want to use a recursive CTE to generate a range without running into the recursion limit, you can use something like this (MySQL dialect, but shouldn't be hard to translate to SQL Server):

WITH RECURSIVE
    a (x) AS
        (SELECT 0 UNION ALL SELECT 1),
    b (x) AS
        (SELECT x FROM a
         UNION ALL
         SELECT (b.x + 1) * 2 + a.x FROM a, b WHERE (b.x + 1) * 2 + a.x < 10000)
SELECT x FROM b
ORDER BY x;

Each step of recursion produces twice as many results as the previous step, so the total number of recursions is approximately log2(n) instead of n.

The ORDER BY clause is needed if you want to ensure the generated numbers are in order, but can be safely omitted otherwise.

If you want a range starting at a number other than zero, just add an offset to the final result e.g. SELECT x + offset FROM b

score 1 · Answer 34 · answered Oct 21 '21 at 08:51

Although there are a lot of good answers on this topic, IMO I think newer TSQL (2019 +) syntax allow for a bit simpler and easy to understand solutions to this problem:

Declare @n1 int = 1000
Declare @n2 int = 1050
Declare @n  int = @n2 -@n1
SELECT @n1 -1 + Row_Number() over ( partition by 1 order by value) as Val FROM 
STRING_SPLIT ( (SELECT REPLICATE(';', @n)) , ';' )

score 0 · Answer 35 · answered Nov 06 '20 at 21:40

Here is a generic and relatively fast solution that outputs integers from 1 to @n. It works with any positive integer of @n (very large numbers will cause arithmetic overflow) without needing to add or remove table joins. It doesn't require the use of system tables nor do you to change max recursions.

declare @n int = 10000 

;with d as (select * from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x (d)),
n as ( 
    select d x from d where d > 0 and d <= @n
    union all
    select x * 10 + d from n, d where x * 10 + d <= @n
)
select x from n

You can add an order by clause to sort the numbers.

score 0 · Answer 36 · answered May 05 '22 at 11:33

0

In new version of mssql server there is sequences: https://learn.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers?view=sql-server-ver15

Example:

CREATE SEQUENCE SequenceFrom1000  
   AS tinyint  
    START WITH 1000  
    INCREMENT BY 1  
    MINVALUE 1000  
    MAXVALUE 1050
GO

use it like

SELECT YourTable.Column1, YourTable.Column2, NEXT VALUE FOR SequenceFrom1000 AS GeneratedNumber,FROM YourTable;  
GO

answered May 05 '22 at 11:33

Kate

751
1
12
26

Nice. do you need both `START WITH` _and_ `MINVALUE`, or is one of either sufficient? – JHBonarius May 05 '22 at 11:57

score 0 · Answer 37 · answered Oct 06 '22 at 12:39

CREATE OR ALTER FUNCTION [dbo].[RangeGenerator](
    @Start  BIGINT
,   @Stop   BIGINT
,   @Step   BIGINT
)
RETURNS TABLE AS RETURN (

    WITH [Initial] AS (
        SELECT 
            [n] 
        FROM (VALUES
            (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        ,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        ,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        ,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        ,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        ,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        ,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        ,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        ,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        ,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        ) AS [t]([n])
    )
    SELECT TOP ((@Stop - @Start) / @Step + 1)
        [n] = @Start + ROW_NUMBER() OVER (ORDER BY [i0].[n]) * @Step - @Step 
    FROM        [Initial]   AS  [i0] /*       100 */
    CROSS JOIN  [Initial]   AS  [i1] /*     10000 */
    CROSS JOIN  [Initial]   AS  [i2] /*   1000000 */
    ORDER BY 1

);
GO

SELECT [n] FROM [dbo].[RangeGenerator](0, 100, 2);

score 0 · Answer 38 · answered Aug 04 '23 at 06:57

I solved it in this way, using recursion in SQL Server:

WITH NumberList AS (
    SELECT 1000 AS Number
    UNION ALL
    SELECT Number + 1
    FROM NumberList
    WHERE Number < 1050
)
SELECT Number FROM NumberList
OPTION (MAXRECURSION 0); -- Set the recursion depth (0 means no limit)

It will print:

Ivan Khloptsev · Answer 39 · 2023-08-21T19:13:04.017

0

from 2 to 12:

declare 
    @start int = 2,
    @end int = 12;
    
;with t as
(
    select @start n
    union all
    select n + 1 from t where n < @end
)
select n from t option(maxrecursion 0);

from 12 to 2:

 declare 
    @start int = 12,
    @end int = 2;
    
;with t as
(
    select @start n
    union all
    select n - 1 from t where n > @end
)
select n from t option(maxrecursion 0);

edited Aug 21 '23 at 19:13

answered Aug 21 '23 at 17:59

Ivan Khloptsev

1
1

score -1 · Answer 40 · edited Jan 12 '21 at 12:01

-1

DECLARE @a int=1000, @b int=1050
SELECT @a-1+ROW_NUMBER() OVER(ORDER BY y.z.value('(/n)[1]', 'int') ) rw
FROM (
SELECT CAST('<m>'+REPLICATE('<n>1</n>', @b-@a+1)+'</m>' AS XML ) x ) t
CROSS APPLY t.x.nodes('//m/n') y(z)

edited Jan 12 '21 at 12:01

Wai Ha Lee

8,598
83
57
92

answered Jan 12 '21 at 11:49

keyvan Aryaee

1

score -1 · Answer 41 · edited Jan 12 '21 at 12:00

CREATE OR ALTER  FUNCTION [dbo].[_ICAN_TF_Nums2](@a INT, @b INT)
-------------------------------------------------------------------------------------------------------------------
--INVENTIVE:Keyvan ARYAEE-MOEEN
-------------------------------------------------------------------------------------------------------------------
RETURNS @_ICAN_TF_Nums2 TABLE
(
    num int
)
AS
BEGIN
------------------------------------------------------------------------------------------------------------------
WITH nums AS
   (SELECT @a AS value
    UNION ALL
    SELECT value + 1 AS value
    FROM nums
    WHERE nums.value < @b)
INSERT @_ICAN_TF_Nums2
SELECT *
FROM nums
ORDER BY 1
option ( MaxRecursion 0 );
RETURN
END
-------------------------------------------------------------------------------------------------------------------
-- SELECT * FROM dbo._ICAN_TF_Nums2(1000, 1050)
-------------------------------------------------------------------------------------------------------------------

score -2 · Answer 42 · answered Jun 19 '22 at 05:49

-2

One that works in SQLite (this gives 0 to 999)

with Ns as (select n FROM ( select 0 n union select 1 n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)),
srl as ( SELECT  a.n hundreds, b.n tens, c.n ones ,a.n*100+b.n*10+c.n serial from  Ns a, Ns b , Ns c)
select * from srl

answered Jun 19 '22 at 05:49

Rafeek

41
8

This question is specifically tagged as a [tag:sql-server] question. Please avoid adding answers that use a different database engine or DBMS, as this can cause confusion for other users that come across the same issue later on. – Hoppeduppeanut Jun 24 '22 at 06:19
Agreed. I reached to this question while looking for a solution that can be applicable to SQLite. Felt it would help someone who would reach likewise here. A slight modification, and it works for SQL-Server too. ```with Ns as (select n FROM ( select 0 n union select 1 n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) a1 ), srl as ( SELECT a.n hundreds, b.n tens, c.n ones ,a.n*100+b.n*10+c.n serial from Ns a, Ns b , Ns c) select * from srl order by 4``` – Rafeek Jun 26 '22 at 14:59
For Oracle `with Ns as (select n FROM ( select 0 n from dual union select 1 n from dual union select 2 from dual union select 3 from dual union select 4 from dual union select 5 from dual union select 6 from dual union select 7 from dual union select 8 from dual union select 9 from dual)), srl as ( SELECT a.n hundreds, b.n tens, c.n ones ,a.n*100+b.n*10+c.n serial from Ns a, Ns b , Ns c) select * from srl` – Rafeek Jun 26 '22 at 15:00
OP clearly tag `sql-server` in the question. – Eric Aug 21 '23 at 18:54

How to generate a range of numbers between two numbers?

42 Answers42

Linked

Related