Generate serie of numbers in SQL Server 2016 using OPENJSON

How can we use OPENJSON to generate series of numbers?

In this post I will talk about one unusual use case of OPENJSON - generating series of numbers.

Problem: I want to dynamically generate a table of numbers (e.g. from 0 to N). Unfortunately we don't have this kind of function in SQL Server 2016, but we can use OPENJSON as a workaround.

OPENJSON can parse array of numbers [1,25,3,5,32334,54,24,3] and return a table with [key,value] pairs. Values will be elements of the array, and keys will be indexes (e.g. numbers from 0  to length of array - 1). In this example I don't care about values I just need indexes.

I can easily dynamically generate valid JSON array of length N using replicate function:

SELECT '[1' + replicate(',1',@count-1)+']'

Since I don't care about values in the array, I'm generating an array that has all elements equal to 1. Now, we can see how to generate series of numbers using this approach and OPENJSON table value function.

Case 1: Generate table with N numbers

In the first example, I will create table value function that generates @count numbers:

 drop function if exists dbo.fn_gen_numbers
 go
create function
 dbo.fn_gen_numbers(@count int)
 returns table
 return (select cast([key] as int) as number from OPENJSON( '[1' + replicate(',1',@count-1)+']'))
 go

The following call will return numbers from 0 to 9:

  select * from dbo.fn_gen_numbers(10)
 

This function might be useful if you want to generate all months in the year, or all days in a month:

 select DATETIMEFROMPARTS ( 2015, number+1, 1, 0, 0, 0, 0 )
 from dbo.fn_gen_numbers(12)
select DATETIMEFROMPARTS ( 2015, 1, number+1, 0, 0, 0, 0 )
 from dbo.fn_gen_numbers(31)

Case 2: Generate range from [@start to @end]

In slightly modified example, I will generate sequence from @start to @end inclusive:

 drop function if exists dbo.fn_gen_range
 go
create function dbo.fn_gen_range(@start int, @end int)
 returns table
 return (select cast([key] as int) + @start as number from OPENJSON( '[1' + replicate(',1',@end-@start)+']'))

The following call will return numbers in the range [10,30]:

 select * from dbo.fn_gen_range(10,30)

Case 3: Generate range from [@start to @end] with @step:

In the final example, I will generate sequence with step using following function:

 drop function if exists dbo.fn_gen_sequence
 go
create function dbo.fn_gen_sequence(@start int, @end int, @step int)
 returns table
 return (select cast([key] as int) * @step + @start as number from OPENJSON( '[1' + replicate(',1',(@end-@start)/@step)+']'))

The following function will return odd numbers from 10 to 18:

 select *
 from dbo.fn_gen_sequence(10,29,2)

Conclusion

Although we don't have built-in function that generates sequence of numbers, OPENJSON might be easiest workaround for this.

Note that in this examples I'm using new DROP IF EXISTS syntax that is available in SQL Server 2016, see https://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016.aspx

Licence

Table value functions in this example can be copied and modified with one requirement - table value function derived from this code that generates numbers using OPENJSON MUST be called with fully qualified schema name, e.g.:

 

SELECT * FROM dbo.fn_gen_range()

and NOT just with function name:

SELECT * FROM fn_gen_range()

T-SQL enables you to call table value functions with or without schema name. However, if you don't use schema name function might be either user defined function or built-in function.

SQL Server team might decide to implement similar built-in table value function function in the future, and new function might collide with your names of user defined functions. Using schema name will prevent this kind of collision, and this is general coding guidance for T-SQL, so I'm adding this as a licence requirement :).

Vote for this function here if you want to see it in next version of SQL Server Add a built-in table of numbers .