Aaron Bertrand is a fan of this new function:
I have come across a lot of use cases for manufacturing rows on the fly, aside from the common goal of populating a large data set such as a numbers or calendar table. A few favorites include building sample data, pivoting an unknown number of columns, data extrapolation, and filling gaps in date or time ranges.
If you are on SQL Server 2022 or Azure SQL Database, or have been reading up on new features, you’ve likely heard about one of the better T-SQL enhancements: a new built-in function called
GENERATE_SERIES
. The syntax is straightforward – it accepts arguments forstart
andstop
, and an optional argument to indicatestep
(in case you want to iterate by more than1
, or backwards):
Click through to see how performance for this compares to two methods we’ve used in the past to generate similar results.