First, SQL Server 2022 has many interesting new features for both DBAs and database developers. GENERATE_SERIES is one of the new additions, and will likely please a lot of database developers and administrators. This function allows us to create a table of rows. Essentially, this function generates a list of sequential numbers as rows.
At first glance this appears to be exactly like the IDENTITY attribute and SEQUENCE object. IDENTITY and SEQUENCE generate numbers only we INSERT rows or call NEXT VALUE FOR. The GENERATE_SERIES() function is different. Every time GENERATE_SERIES is executed it generates the series of numbers. GENERATE_SERIES has two mandatory arguments (Initial, Final) and 1 optional (Step) argument. The parameters are shown below:
GENERATE_SERIES ( Initial, Final [, Step ] )
If Initial < Final then the default step is 1, and if Initial > Final then the default step is -1. The output column is named "value".
The documentation for this function is available here.
Initially, for GENERATE_SERIES two basic cases are depicted to show how this function works:
- Generate odd or even numbers
- Errors linked to the data types used as parameters
A trivial example of using GENERATE_SERIES is basically to generate odd numbers (or even). In this example, the SELECT generates the odd numbers between 1 (which is the initial value) and 13 (the final value). The result is created and returned to the client in tabular format. The step used to create the new values is 2. You can see the code and results below.
SELECT * FROM GENERATE_SERIES(1, 13, 2) value ----- 1 3 5 7 9 11 13
Most likely because this is the first version, this new function has few limitations. Assuming, we want to generate all numbers from 6 to 7.50 with a step of 0.50, we might try the next SELECT,:
SELECT * FROM GENERATE_SERIES ( 6, 7.50, 0.50 ) gs
An error is returned in this case, which is strange:
Msg 5373, Level 16, State 1, Line 33 All the input parameters should be of the same type. Supported types are tinyint, smallint, int, bigint, decimal and numeric. Msg 206, Level 16, State 2, Line 33 Operand type clash: int is incompatible with void type Msg 206, Level 16, State 2, Line 33 Operand type clash: numeric is incompatible with void type Msg 206, Level 16, State 2, Line 33 Operand type clash: numeric is incompatible with void type
The text of this error means that all parameters should have the same data type. This restriction seems to be something like a limitation. Or maybe a bug. This limitation is generated by the missing of an implicit conversion of the parameter.
In the following example we verify another function from T-SQL which is ATN2, to see that implicit conversion and/or data type precedence are working as we expected. In order to understand, we analyze the following SELECT from the ATN2 function. The execution plan is in text format. This simple example using ATN2 ( float_expression , float_expression ) demonstrates a CONVERT_IMPLICIT, which is a CAST DECIMAL -> FLOAT:
SELECT ATN2Result = ATN2(6, s.INTEGE) FROM ( SELECT 7.50 UNION SELECT 9.50 ) s(INTEGE)
The plan shows these operators:
StmtText ------------------------------------------------------------------------------------------------------ |--Compute Scalar(DEFINE:([Expr1003]=atn2((6.0000e+000),CONVERT_IMPLICIT(float(53),[Union1002],0)))) |--Concatenation |--Constant Scan(VALUES:(((7.50)))) |--Constant Scan(VALUES:(((9.50))))
In this cases the function call is working because all values are implicitly cast to FLOAT, which is the datatype of the two parameters.
In order to verify data type precedence we have create also a user defined function, which has 4 parameters:
- @a VARCHAR(10)
- @b INT
- @c VARBINARY
- @d TINYINT
Indeed, all arguments are converted to INT (which has the highest precedence). If we check the execution plan, this shows that in this case precedence is working.
SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON CREATE SCHEMA SSC -- SQLServerCentral GO CREATE FUNCTION SSC.DataTypePrecedence (@a VARCHAR(10), @b INT, @c VARBINARY, @d TINYINT) RETURNS TABLE AS RETURN SELECT Rez = @a + @b + @c + @d GO DECLARE @a VARCHAR(10) = '1', @b INT = 12, @c VARBINARY = 0x00 , @d TINYINT = 0 SELECT * FROM SSC.DataTypePrecedence(@a, @b, @c, @d) Rez --- 13
StmtText --------------------------------------------------------------------------------------------------------------------------- |--Constant Scan(VALUES:((CONVERT_IMPLICIT(int,[@a],0)+[@b]+CONVERT_IMPLICIT(int,[@c],0)+CONVERT_IMPLICIT(int,[@d],0))))
Some solutions exist and they are extremely simple. First, we define few variables and using these variables to call this function:
DECLARE @Initial DECIMAL(8,2) = 6.00 SELECT * FROM GENERATE_SERIES(@Initial, )
Another simple solution is using some dedicated functions to execute convert. In this case CAST DECIMAL:
SELECT * FROM GENERATE_SERIES(CAST(6 AS DECIMAL(8,2)), ETC)
It means that we should rewrite the initial SELECT using DECLARE:
DECLARE @First DECIMAL(8,2) = 6, @Last DECIMAL(8,2) = 7.50 DECLARE @Step DECIMAL(8,2) = 0.5 SELECT * FROM GENERATE_SERIES ( @First, @Last, @Step ) gs value ----- 6.00 6.50 7.00 7.50
As a reference for data types, the highest precedence is INT. We can see a list of data type precedence below:
Higher 16.int 17.smallint 18.tinyint 19.bit 20.ntext 21.text 22.image 23.timestamp 24.uniqueidentifier 25.nvarchar (including nvarchar(max) ) 26.nchar 27.varchar (including varchar(max) ) Lower
This diagram depict in this case the precedence order for data types (chart source)
Case Studies
Apart of few small calls which shows the basics things related to this functions, it is important to check also these practical examples. Here are a few cases where GENERATE_SERIES can be useful:
- Finding missing values from a range of numbers
- Generate some random text (passwords)
- Calendar
- Monday/Friday
We will cover each of these below.
Searching for missing values from a range is a classic practical usage of this function. In our next example the derived table, Report, has some rows only for a few months (1, 2, and 8) and instead we would like to show which months are missing in our report. We use this code to get a list of all months from 1 to 12 and then exclude those not in our list.
SELECT Missing = value FROM GENERATE_SERIES(1, 12) Months WHERE value NOT IN ( SELECTReport.Month FROM ( SELECT 111, 1, 222, 1 UNION SELECT 222, 2, 444, 2 UNION SELECT 333, 8, 666, 3 ) Report(AccountID, Month, TraValue, TraID) ) Missing ----------- 3 4 5 6 7 9 10 11
Sometimes, a report with product sales must show all the periods, including those that have no sales. The below case must show all months from January to December, no matter if we have sales or not. In this example there is a derived table, Report, having credit card transactions for the following months: 1, 2, 8. We would like to show all months in the final report. There is our query:
SELECT AccountID, Month = Months.value, TraValue, TraID FROM GENERATE_SERIES(1, 12) Months LEFT JOIN ( SELECT 111, 1, 222, 1 UNION SELECT 222, 2, 444, 2 UNION SELECT 333, 8, 666, 3 ) Report(AccountID, Month, TraValue, TraID) ON Report.Month = Months.value
Some months are missing from initial report and in the final report they have NULLs:
AccountID Month TraValue TraID --------- ----- -------- ----- 111 1 222 1 222 2 444 2 NULL 3 NULL NULL ... 333 8 666 3 ... NULL 12 NULL NULL
Another example is generating random characters. The below T-SQL scripts build up a query that generates random characters for passwords. First, using GENERATE_SERIES we generate 7 rows.
SELECT * FROM GENERATE_SERIES(1, 7) Numbe value ----- 1 2 3 4 5 6 7
For every row we generate a random number between 65 and 90, as these are the ranges for the ASCII alphabet characters. We use this formula: 65 + ABS(CHECKSUM( NEWID() )) % (90 - 65 + 1).
SELECT 65 + ABS(CHECKSUM( NEWID() )) % (90 - 65 + 1) FROM GENERATE_SERIES(1, 7) Numbe value -- 73 87 74 70 90 70 71
When can use the CHAR() function to convert the ASCII code into a new character.
SELECT CHAR( 65 + ABS(CHECKSUM( NEWID() )) % (90 - 65 + 1) ) FROM GENERATE_SERIES(1, 7) Numbe value -- K J J S A Y Z
Finally, STRING_AGG concates all these 7 characters in a new string.
Text ------- XISLSHM
T-SQL Script:
--Generates some random characters between AZ, ASCII('A') = 65 ASCII('Z') = 90
SELECT STRING_AGG(CHAR( 65 + ABS(CHECKSUM( NEWID() )) % (90 - 65 + 1) ) , '') Text
FROM GENERATE_SERIES(1, 7) Numbe
--Generates some random characters between az, ASCII('a') = 97 ASCII('z') = 122
SELECT STRING_AGG(CHAR( 97 + ABS(CHECKSUM( NEWID() )) % (122 - 97 + 1) ), '') Text
FROM GENERATE_SERIES(1, 7) Numbers
--Generates some random special characters between !/, ASCII('!') = 33 ASCII('/') = 47
SELECT STRING_AGG(CHAR( 33 + ABS(CHECKSUM( NEWID() )) % (47 - 33 + 1) ), '') Text
FROM GENERATE_SERIES(1, 7) Numbers
--Generates some random special characters between AZ, az, !/
SELECT STRING_AGG(CASE 1 + ABS(CHECKSUM( NEWID() )) % 3
WHEN 1 THEN CHAR( 65 + ABS(CHECKSUM( NEWID() )) % (90 - 65 + 1) ) -- A Z
WHEN 2 THEN CHAR( 97 + ABS(CHECKSUM( NEWID() )) % (122 - 97 + 1) ) -- a z
WHEN 3 THEN CHAR( 33 + ABS(CHECKSUM( NEWID() )) % (47 - 33 + 1) ) -- ! / Special Characters
ELSE CHAR( 33 + ABS(CHECKSUM( NEWID() )) % (47 - 33 + 1) ) -- Special Characters
END, '')
FROM GENERATE_SERIES(1, 7) Numbers
--Extract some random characters which are in @SelectedCharacters
DECLARE @SelectedCharacters VARCHAR(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&''()*+,-./'
DECLARE @Chars INT = DATALENGTH(@SelectedCharacters)
SELECT STRING_AGG(SUBSTRING(@SelectedCharacters, 1 + ABS(CHECKSUM( NEWID() )) % @Chars, 1), '')
FROM GENERATE_SERIES(1, 7) NumbersA calendar is just another example, which is also extremely easy. Using GENERATE_SERIES we generate the numbers between 1 and 31. Then we create all days between August 1 till August 31 with DATEADD/DAY. This is a classic usage of a list of numbers:
SELECT DATEADD(DAY, seri.value -1 , '2023-08-01') CalculatedDate FROM GENERATE_SERIES(1, 31) seri CalculatedDate ----------------------- 2023-08-01 00:00:00.000 2023-08-02 00:00:00.000 2023-08-03 00:00:00.000 ... 2023-08-30 00:00:00.000 2023-08-31 00:00:00.000
Instead, we can also use this date interval to create a calendar. Using @FirstDate and @EndDate as parameters we get the same dates thus:
DECLARE @FirstDate DATE = '2023-08-01' DECLARE @EndDate DATE = '2023-08-31' DECLARE @Count INT = DATEDIFF(DAY, @FirstDate, @EndDate) + 1 SELECT DATEADD(DAY, Numbe.value -1 , @FirstDate) CalculatedDate FROM GENERATE_SERIES(1, @Count) Numbe CalculatedDate ----------------------- 2023-08-01 00:00:00.000 2023-08-02 00:00:00.000 2023-08-03 00:00:00.000 ... 2023-08-30 00:00:00.000 2023-08-31 00:00:00.000
In some particular cases we have to create a time interval with a precision of minutes, hours, etc. The following script shows all date/time values between 2023-08-04 13:00 and 2023-08-04 13:99 with an interval of a minute. Basically, this example is the same as calendar with a very small change within the DATEADD call. GENERATE_SERIES is used to generate all those 60 minute intervals:
SELECT DATEADD(MINUTE, Minutes.value -1 , '2023-08-04 13:00') Minute FROM GENERATE_SERIES(1, 60) Minutes Minute ----------------------- 2023-08-04 13:00:00.000 2023-08-04 13:01:00.000 2023-08-04 13:02:00.000 2023-08-04 13:03:00.000 - 2023-08-04 13:59:00.000
Monday/Friday it is basically simple to generate: first we must find the first Monday and the weeks number:
SET DATEFIRST 1 -- Monday. It is used because DATEPART WEEKDAY is nondeterministic -- Parameters DECLARE @FirstDate DATE = '2023-08-13' DECLARE @EndDate DATE = '2023-08-31' -- End Parameters DECLARE @Count INT = (DATEDIFF(DAY, @FirstDate, @EndDate) + 1) / 7 -- How many fully weeks does exist ? DECLARE @Monday DATE -- First monday in current date interval -- Using GENERATE_SERIES(1, 7) we generate all weekdays from @FirstDate (+ 6 days = 7 days) and verify which is Monday SELECT @Monday = ccdt.CalculatedDate--, DATEPART(WEEKDAY, ccdt.CalculatedDate), DATENAME (WEEKDAY, ccdt.CalculatedDate) FROM ( SELECT CalculatedDate = DATEADD(DAY, Numbe.value -1 , @FirstDate) FROM GENERATE_SERIES(1, 7) Numbe ) ccdt WHERE DATEPART(WEEKDAY, ccdt.CalculatedDate) = 1 -- Monday. See SET DATEFIRST --Generate Weeks: Monday/Friday SELECT* FROM ( SELECT Monday = DATEADD(DAY, 7*(Numbe.value-1) , @Monday), Friday = DATEADD(DAY, 7*(Numbe.value-1) -1 + 5 , @Monday) FROM GENERATE_SERIES(1, @Count) Numbe ) s WHERE /*@FirstDate <= s.Monday AND*/ s.Friday <= @EndDate Output Monday Friday ---------- ---------- 2023-08-14 2023-08-18 2023-08-21 2023-08-25
Output
Monday Friday ---------- ---------- 2023-08-14 2023-08-18 2023-08-21 2023-08-25
In this last case we can see an interesting side effect: a function is executed 7 times CalculatedDate = DATEADD(DAY, etc). Because of that the split of a string is simple
DECLARE @Text VARCHAR(12) = '34567' DECLARE @CharCount INT = LEN(@Text) SELECTCharIndex = value, IntegerChar = SUBSTRING(@Text, value, 1) FROMGENERATE_SERIES(1,@CharCount)
Output
CharIndex IntegerChar --------- ----------- 1 3 2 4 3 5 4 6 5 7
Finish
GENERATE_SERIES is a fantastic tool to use in code. We have given a few cases here that show how you can use this. Because all the arguments should have exactly the same type, we have to be very careful in how we structure our code.
Note: A similar example using a simple SELECT to check data precedence is:
SELECT '1' - 14 meaning SELECT 1 - 14 which give 13
In this case the string/VARCHAR '1' is implicitly converted to an integer and the result, 13, is another integer. In order to find out the data type for every operand from this mathematical operation we are using SQL_VARIANT_PROPERTY having ‘BaseType’ as an argument:
SELECT SQL_VARIANT_PROPERTY('1', 'BaseType') AS BaseType
SELECT SQL_VARIANT_PROPERTY(14, 'BaseType') AS BaseType
SELECT SQL_VARIANT_PROPERTY(13, 'BaseType') AS BaseType
BaseType
--------
varchar
BaseType
--------
int
BaseType
--------
intData type precedence is working fine in this case because the string/VARCHAR '1' is implicitly casted to INTEGER. VARCHAR has a lower priority and INT has a higher priority in this case. Because of this VARCHAR '1' is converted into INTEGER.