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    1000  1001  1002  1003        1050

User · Answer

The best speed when run query  DECLARE  num INT   1000 WHILE  num lt 1050  begin  INSERT  INTO  dbo   Codes          Code            VALUES   num      SET  num    num   1 end

User · Answer

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

User · Answer

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  lt  final   select   from cte n option  maxrecursion 0   saludos

User · Answer

declare  start int   1000 declare  end    int  1050   with numcte   AS         SELECT  start  SEQUENCE      UNION all     SELECT  SEQUENCE    1 FROM numcte WHERE  SEQUENCE   lt   end          SELECT   FROM numcte

User · Answer

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  gt  256     left join u8 x2 on  end- start  gt  65536   s where s n  lt   end -  start   Generates up to 2 24 values  Join conditions keep it fast for small values

User · Answer

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  lt    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

User · Answer

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

User · Answer

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

User · Answer

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  lt   b  INSERT   ICAN TF Nums2 SELECT   FROM nums ORDER BY 1 option   MaxRecursion 0    RETURN END ------------------------------------------------------------------------------------------------------------------- -- SELECT   FROM dbo  ICAN TF Nums2 1000  1050  -------------------------------------------------------------------------------------------------------------------

User · Answer

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

User · Answer

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  gt  0 and d  lt    n     union all     select x   10   d from n  d where x   10   d  lt    n   select x from n   You can add an order by clause to sort the numbers

User · Answer

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

User · Answer

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

User · Answer

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 lt   endnum   SELECT   FROM gen option  maxrecursion 10000

User · Answer

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   lt m gt   REPLICATE   lt n gt 1 lt  n gt     b- a 1    lt  m gt   AS XML   x   t CROSS APPLY t x nodes    m n   y z

User · Answer

-- 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  lt   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

User · Answer

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  lt  0x100000         and intList value between  Starting and  Ending

User · Answer

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    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 as 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

User · Answer

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

User · Answer

I had to insert picture filepath into database using similar method  The query below worked fine   DECLARE  num INT   8270058 WHILE  num lt 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 lt 1051  begin     SELECT  num      SET  num    num   1 end

User · Answer

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   lt   10000    SELECT   FROM d ORDER BY v

User · Answer

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 lt   size     UNION ALL     SELECT num 2 1 FROM numrange WHERE num 2 1 lt   size   SELECT num  startnum-1 FROM numrange order by num

User · Answer

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  lt    end- start and b1 n  lt    end- start     join b2 on b2 n  lt    end- start     join b3 on b3 n  lt    end- start     join b4 on b4 n  lt    end- start     join b5 on b5 n  lt    end- start     join b6 on b6 n  lt    end- start     join b7 on b7 n  lt    end- start   s where  end  gt   s n  GO

User · Answer

Update for SQL 2017 and later  If the sequence you desire is  lt  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

User · Answer

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     lt     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                   lt     max            --return up to max       order by    number

User · Answer

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

User · Answer

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  lt    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

User · Answer

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   lt x  gt   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   lt x  gt   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

User · Answer

It work for me    select top 50 ROW NUMBER   over order by a name    1000 as Rcount from sys all objects a

User · Answer

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

User · Answer

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

User · Answer

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  lt    end- start and x16 X  lt    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  lt    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  lt    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  lt    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  lt    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  lt    end- start join  VALUES  0   268435456   536870912   805306368   1073741824   1342177280   1610612736   1879048192   as x268435456 X  on x268435456 X  lt    end- start WHERE  end  gt       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

[sql] How to generate a range of numbers between two numbers?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008