How to generate a range of numbers between two numbers?

Question

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

How can I generate numbers between these two numbers using sql query in separate lines? I want this:

  1000 1001 1002 1003 . . 1050 

Answer 1

Select inconsistent values ​​with the VALUES keyword. Then use the JOIN to generate many combinations (can be expanded to create hundreds of thousands of lines or more).

 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) WHERE ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n BETWEEN @userinput1 AND @userinput2 ORDER BY 1 

Demo

A shorter alternative that is not so easy to understand:

 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 

Demo

Answer 2

an alternative solution is a 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) 

Answer 3

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

Demo

Please note that this table has a maximum of 2048, because then the numbers have spaces.

Here it’s a little better to use the system view (starting with 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 number table. Loans to Aaron Bertrand, I suggest reading the entire article: Create a set or sequence without cycles

Answer 4

I recently wrote this built-in table evaluation function to solve this problem. It is not limited to areas other than memory and storage. It does not access tables, so there is no need to read or write discs at all. It adds union values ​​exponentially at each iteration, so it is 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 is also suitable for date and time ranges:

 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 can use a cross to join it to split records based on values ​​in a table. For example, to create a record for every minute in a time range in a table, you can 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 

Answer 5

The best option I 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 created millions of records using this and it works great.

Answer 6

It works for me!

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

Answer 7

If you have no problem installing the CLR assembly on your server, a good option is to write the table evaluation function in .NET. Thus, you can use simple syntax, simplifying integration with other queries, and as a bonus you won’t waste memory, because the result is transmitted.

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() { } } } 

Place the node 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) 

Answer 8

2 years later, but I found that I have the same problem. This is how I solved it. (edited to include options)

 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) 

Answer 9

The best way is to use recursive ctes.

 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.

Answer 10

Nothing new, but I rewrote the decision of Brian Pressler to make it easier for the eyes, it could be useful to someone (even if it is only my future):

 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 sn 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 >= sn GO 

Answer 11

Here are a couple of perfectly 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 ; 

Answer 12

slartidan’s answer can be improved, increased productivity by eliminating all references to the Cartesian product and instead using ROW_NUMBER() ( the execution plan is 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 the CTE and add a where clause to select the numbers you need:

 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 

Answer 13

I know that I am already 4 years old, but I came across another alternative answer to this problem. The speed problem is not only pre-filtering, but also preventing sorting. This allows you to force the join order so that the Cartesian product is actually counted as a result of the union. Using a slartidan response as a transition 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 REMOTE join hint with TOP, we can only compute a subset of the values ​​we want without losing anything.

 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 REMOTE connection hint causes the optimizer to first compare on the right side of the connection. By specifying each connection as a REMOTE from most to the least significant value, the connection itself will correctly count up one. No need to filter with WHERE or sort with ORDER BY.

If you want to increase the range, you can continue to add additional compounds with progressively higher orders if they are ordered from most to least significant in the FROM clause.

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

Answer 14

It 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 

Answer 15

Better query performance

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

Answer 16

recursive CTE in exponential size (even for 100 recursions by default, this can create 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 

Answer 17

I had to insert the path to the image file into the database using a similar method. The following query 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 

Code for you:

 DECLARE @num INT = 1000 WHILE(@num<1051) begin SELECT @num SET @num = @num + 1 end 

Answer 18

 -- 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 = an + (bn * 10) + (cn * 100) + (dn * 1000) + (en * 10000) FROM #NumRange a CROSS JOIN #NumRange b CROSS JOIN #NumRange c CROSS JOIN #NumRange d CROSS JOIN #NumRange e WHERE an + (bn * 10) + (cn * 100) + (dn * 1000) + (en * 10000) BETWEEN @MinNum AND @MaxNum ORDER BY an + (bn * 10) + (cn * 100) + (dn * 1000) + (en * 10000) DROP TABLE #NumRange 

Answer 19

This only works for sequences if there are rows in any application table. Suppose I want a sequence of 1..100 and have a dbo.foo application table with a column (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 the order by clause, dbo.foo.bar must not have separate or even non-zero values.

Of course, SQL Server 2012 has sequence objects, so this product has a natural solution.

Answer 20

This completed for me in 36 seconds on our DEV server. Like Brian's answer, focusing on range filtering is important from the query; BETWEEN is still trying to generate all the initial records to the bottom, even if they do not 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 

Please note that ROW_NUMBER is bigint , so we cannot switch to 2 ^^ 64 (== 16 ^^ 16) generated records in any way that uses This. Therefore, this query takes into account the same upper limit for the generated values.

Answer 21

Here is 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 + sn 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 sn < @end - @start 

Generates up to 2 ^ 24 values. Connection conditions maintain this quickly at low values.

Answer 22

In this case, the procedural code and the table function are used. 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 

Using:

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

This is a table, so you can use it in connections with other data. I most often use this function as the left side of a connection against GROUP BY hour, day, etc., to ensure a continuous 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 unbearable (16 seconds for a million lines), but enough for many purposes.

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

Answer 23

Oracle 12c Fast but limited:

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

Note : limited by the number of lines in the all_objects view;

Answer 24

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

with number
AS
(
SELECT @start [SEQUENCE]

Union all

SELECT [SEQUENCE] + 1 FROM numcte WHERE [SEQUENCE] <@end)

SELECT * FROM numcte

Answer 25

This is what I do, it is pretty fast and flexible, and not much 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 does nothing, but ROW_NUMBER () requires ORDER BY.

Edit : I realized that the original question was to get a range from x to y. My script can be modified 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;