Using Fractions when Generating a Series in SQL Server

When we use SQL Server’s GENERATE_SERIES() function to create a series, we provide the start and stop points as numbers. And if we provide the optional step argument, then it’s provided as a number too.

The numbers we provide can be decimal fractions if required. This means that we can create a series made up of decimal fractions. But we still need to ensure that all our arguments are of the same type.

By “decimal fractions” I mean numbers like 2.75 or 0.5, where we can have numerals on the right side of the decimal separator.

Example

Here’s an example to demonstrate:

SELECT * FROM GENERATE_SERIES( 1.5, 7.5, 1.5 );

Result:

value
-----
  1.5
  3.0
  4.5
  6.0
  7.5

In this case I started the series with 1.5, I ended it with 7.5, and I specified a step amount of 1.5.

Here’s another example:

SELECT * FROM GENERATE_SERIES( 0.0, 0.0000010, 0.0000001 );

Result:

All Argument Types Must Match

Although the GENERATE_SERIES() function accepts various numeric types (tinyint, smallint, int, bigint, decimal, and numeric), they must all be the same numeric type.

For example, if you provide decimal type for the first argument, then the other arguments must also be decimal.

Here’s what happens when the arguments have mismatching types:

SELECT * FROM GENERATE_SERIES( 1.5, 3.5, 1 );

Result:

Msg 206, Level 16, State 2, Server 7f44773c4987, Line 1
Operand type clash: numeric is incompatible with void type
Msg 206, Level 16, State 2, Server 7f44773c4987, Line 1
Operand type clash: numeric is incompatible with void type
Msg 206, Level 16, State 2, Server 7f44773c4987, Line 1
Operand type clash: int is incompatible with void type
Msg 5373, Level 16, State 2, Server 7f44773c4987, Line 1
All the input parameters should be of the same type. Supported types are tinyint, smallint, int, bigint, decimal and numeric.

In this case the third argument’s type doesn’t match that of the first two arguments. Specifically, the third argument is an integer, but the first two are numeric.

To fix the above issue, we can simply change the third argument from 1 to 1.0:

SELECT * FROM GENERATE_SERIES( 1.5, 3.5, 1.0 );

Result:

value
-----
  1.5
  2.5
  3.5

The above error can happen even when we try to combine numeric with decimal. In SQL Server, decimal and numeric are synonyms and can be used interchangeably. But if we try to combine them into the GENERATE_SERIES() function, we’ll get an error:

DECLARE @start numeric(3,2) = '1.55';
DECLARE @end numeric(3,2) = '5.55';
DECLARE @step decimal(3,2) = '1.55';
SELECT * FROM GENERATE_SERIES( @start, @end, @step );

Result:

Msg 206, Level 16, State 2, Server 7f44773c4987, Line 4
Operand type clash: numeric is incompatible with void type
Msg 206, Level 16, State 2, Server 7f44773c4987, Line 4
Operand type clash: numeric is incompatible with void type
Msg 206, Level 16, State 2, Server 7f44773c4987, Line 4
Operand type clash: decimal is incompatible with void type
Msg 5373, Level 16, State 2, Server 7f44773c4987, Line 4
All the input parameters should be of the same type. Supported types are tinyint, smallint, int, bigint, decimal and numeric.

So even though decimal and numeric are synonyms, we still get the error.

So to fix it, all we need to do is ensure that they’re all either decimal or numeric (but not a combination):

DECLARE @start numeric(3,2) = '1.55';
DECLARE @end numeric(3,2) = '5.55';
DECLARE @step numeric(3,2) = '1.55';
SELECT * FROM GENERATE_SERIES( @start, @end, @step );

Result:

value
-----
 1.55
 3.10
 4.65

Default Step Amount

The third argument (for the step amount) is optional. If we don’t provide the third argument, the step amount defaults to 1 (when the start is lower than the end, otherwise it defaults to -1).

However, just because it defaults to 1, doesn’t mean it automatically becomes an integer. If our start and end points are numeric, then the default step will still work:

DECLARE @start numeric(3,2) = '1.55';
DECLARE @end numeric(3,2) = '3.55';
SELECT * FROM GENERATE_SERIES( @start, @end );

Result:

value
-----
 1.55
 2.55
 3.55