{"id":96175,"date":"2023-03-30T23:21:49","date_gmt":"2023-03-30T23:21:49","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=96175"},"modified":"2023-03-29T04:54:59","modified_gmt":"2023-03-29T04:54:59","slug":"generate-series-to-build-a-set","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/generate-series-to-build-a-set\/","title":{"rendered":"GENERATE_SERIES: My new go-to to build sets"},"content":{"rendered":"
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.<\/p>\n

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<\/code><\/a>. The syntax is straightforward – it accepts arguments for start<\/code> and stop<\/code>, and an optional argument to indicate step<\/code> (in case you want to iterate by more than 1<\/code>, or backwards):<\/p>\n

 SELECT value FROM GENERATE_SERIES(<start>, <stop> [, <step>]);<\/pre>\n
A few quick examples:<\/p>\n
 \/* count to 6 *\/ \r\n SELECT [1-6] = value \r\n   FROM GENERATE_SERIES(1, 6);\r\n\r\n \/* count by 5s to 30 *\/\r\n SELECT [step 5] = value \r\n   FROM GENERATE_SERIES(5, 30, 5);\r\n\r\n \/* count from 10 to 0, backwards, by 2 *\/\r\n SELECT [backward] = value\r\n   FROM GENERATE_SERIES(10, 0, -2);\r\n\r\n \/* get all the days in a range, inclusive *\/\r\n DECLARE @start date = '20230401',\r\n         @end   date = '20230406';\r\n\r\n SELECT [days in range]  = DATEADD(DAY, value, @start)\r\n   FROM GENERATE_SERIES(0, DATEDIFF(DAY, @start, @end));<\/pre>\n
Results:<\/p>\n\n\n\n\n\n\n\n\n\n
1-6<\/strong>
\n(first resultset)<\/td>\n		 <\/td>\nstep 5<\/strong>
\n(second resultset)<\/td>\n		 <\/td>\nbackward<\/strong>
\n(third resultset)<\/td>\n		 <\/td>\ndays in range<\/strong>
\n(fourth resultset)<\/td>\n<\/tr>\n
1<\/td>\n5<\/td>\n10<\/td>\n2023-04-01<\/td>\n<\/tr>\n
2<\/td>\n10<\/td>\n8<\/td>\n2023-04-02<\/td>\n<\/tr>\n
3<\/td>\n15<\/td>\n6<\/td>\n2023-04-03<\/td>\n<\/tr>\n
4<\/td>\n20<\/td>\n4<\/td>\n2023-04-04<\/td>\n<\/tr>\n
5<\/td>\n25<\/td>\n2<\/td>\n2023-04-05<\/td>\n<\/tr>\n
6<\/td>\n30<\/td>\n0<\/td>\n2023-04-06<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u00a0<\/div>\n
That is some handy syntax that is quite easy to use. I dug in more about it during the beta<\/a>, but…<\/p>\n
How would we do this on older versions of SQL Server?<\/h3>\n
We’ve been generating sets since before SQL Server was SQL Server, so we’ve always found a way. Some approaches are cryptic, and some perform poorly; others are cryptic and<\/em> perform poorly. I have two that I like: one that works in SQL Server 2016 and above, and one that works all the way back to SQL Server 2008. There are others (even some that will work on SQL Server 2000), but these are the two I want to focus on today.<\/p>\n
I’m going to present both techniques as inline table-valued functions, since the logic is complicated enough to justify encapsulation, and that also happens to keep demos nice and tidy. These will be written to accommodate a series of up to 4,000 values – we can certainly go beyond that, but exceeding 8,001 values leads to the first solution requiring LOB support, which can do unpredictable things to performance. The second is capped at 4,096 values because it is the highest power of 4 that is also less than 8,001; you’ll see why that’s important in a moment.<\/p>\n
2016+<\/span> STRING_SPLIT + REPLICATE<\/b><\/p>\n
This one is a rather recent addition to my toolbox; I don’t recall where I first came across it, but I like it because it’s concise without being overly opaque. We determine the number of values we want in our sequence, less one – which is the stop<\/code> minus the start<\/code>. We use REPLICATE<\/code> to generate a string that is a sequence of that many<\/em> commas. Then we split that string using STRING_SPLIT<\/code>, which results in { stop - start + 1 }<\/code> empty strings. We then apply a ROW_NUMBER()<\/code> to the output, which serves as our series. Since our starting value might not be 1<\/code>, we add it to the row number, and subtract 1<\/code>.<\/p>\n
To get started, I will create a new database named GenSeries<\/code> to put the sample code.<\/p>\n
 CREATE FUNCTION dbo.GenerateSeries_Split\r\n (\r\n   @start int,\r\n   @stop  int\r\n )\r\n RETURNS TABLE WITH SCHEMABINDING\r\n AS\r\n   RETURN\r\n   (\r\n     SELECT TOP (@stop - @start + 1) \r\n       value = ROW_NUMBER() OVER (ORDER BY @@SPID) + @start - 1\r\n     FROM STRING_SPLIT(REPLICATE(',', @stop - @start), ',')\r\n     ORDER BY value\r\n   );<\/pre>\n
To support a range greater than 8,001 values, you can change this line:<\/p>\n
 FROM STRING_SPLIT(REPLICATE(CONVERT(varchar(max),','), @stop - @start), ',')<\/pre>\n
…but that’s not the version I’m going to test today.<\/p>\n
<\/p>\n
2008+<\/span> Cross-Joined CTEs<\/b><\/p>\n
This solution reaches further back into most of the unsupported versions of SQL Server you might still be clinging to but, unfortunately, it is a little more cryptic. I remember first using it in this solution<\/a> after discovering this really efficient implementation<\/a> by Jonathan Roberts.<\/p>\n
 CREATE FUNCTION dbo.GenerateSeries_CTEs\r\n (\r\n   @start int,\r\n   @stop  int\r\n )\r\n RETURNS TABLE WITH SCHEMABINDING \r\n AS \r\n   RETURN\r\n   (\r\n     \/* could work in 2005 by changing VALUES to a UNION ALL *\/\r\n     WITH n(n) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0)) n(n)),\r\n      i4096(n) AS (SELECT 0 FROM n a, n b, n c, n d, n e, n f)      \r\n     SELECT TOP (@stop - @start + 1) \r\n       value = ROW_NUMBER() OVER (ORDER BY @@TRANCOUNT) + @start - 1 \r\n     FROM i4096\r\n     ORDER BY value\r\n   );<\/pre>\n
This approach uses two CTEs – one that just generates 4 rows using a VALUES<\/code> constructor; the second one cross joins it to itself, however many times is necessary to cover the range of values you need to support. (In our case, we want to support 4,000 values.)<\/p>\n
Each time you cross join the original set of 4, you produce a Cartesian product of 4^n<\/code>, where n<\/code> is bumped by 1 for each new reference. So if you just named it once, you’d have 4^1<\/code>, which is 4. The second reference is 4^2<\/code>, which is 16. Then 4^3<\/code> = 64, 4^4<\/code> = 256, 4^5<\/code> = 1,024, and 4^6<\/code> = 4,096. I’ll try to illustrate in an image:<\/p>\n
\"Explaining<\/a><\/p>\n
If you only need to support 256 values, for example, then you could change that second line to stop at the 4th cross join:<\/p>\n
   i256(n) AS (SELECT 0 FROM n a, n b, n c, n d)<\/pre>\n
And if you needed more than 4,096 values – say, up to 16,384 – you would instead just add one additional cross join:<\/p>\n
   i16K(n) AS (SELECT 0 FROM n a, n b, n c, n d, n e, n f, n g)<\/pre>\n
And of course you can be more verbose and self-documenting. Technically, I would want to write the following, it’s just a lot more to digest on first glance:<\/p>\n
   i4096(n) AS \r\n        (\r\n           SELECT 0 FROM n AS n4    CROSS JOIN n AS n16 \r\n              CROSS JOIN n AS n64   CROSS JOIN n AS n256\r\n              CROSS JOIN n AS n1024 CROSS JOIN n AS n4096\r\n           \/* ... *\/\r\n        )<\/pre>\n
You could also code defensively and alter the parameters to smallint<\/code> or tinyint<\/code>\u00a0to prevent surprises when someone uses an int<\/code> value that is too large and they don’t get the full set they expect. This won’t raise an error, unless you also add additional handling, say, to divide by 0 somewhere if the range is too large. Keep in mind that someone could try to generate 100 rows by passing in a start parameter of 2,000,000,000 and a stop parameter of 2,000,000,100 – so restricting either input value instead of the difference might be unnecessarily limiting.<\/p>\n
I often see recursive CTEs suggested for set generation, since they are a little less cryptic than this, and are somewhat self-documenting (if you already understand recursive CTEs, I suppose). I do like recursive CTEs generally, and have offered them up in many posts and answers, but they’re not ideal for broad consumption in this context unless you will never retrieve more than 100 rows (say, generating the days for a monthly report). This is because you will need a MAXRECURSION<\/code> query hint to produce more than 100 values; since you can’t put that hint inside a function, it means you have to put it on every outer query that references the function. Ick! So much for encapsulation.<\/p>\n
So how do they perform?<\/h3>\n
I thought about the simplest test I can do to pit different number generation techniques against each other, and the first that came to mind involves pagination<\/a>. (Note: This is a contrived use case and not intended to be a discussion about the best ways to paginate data.)<\/em><\/p>\n
In the GenSeries<\/code> database, I will create a simple table with 4,000 rows:<\/p>\n
 SELECT TOP (4000) rn = IDENTITY(int,1,1),*\r\n INTO dbo.things FROM sys.all_columns;\r\n\r\n CREATE UNIQUE CLUSTERED INDEX cix_things ON dbo.things(rn);<\/pre>\n
Then I created three stored procedures. One that uses the split approach:<\/p>\n
 CREATE OR ALTER PROCEDURE dbo.PaginateCols_Split\r\n   @PageSize int = 100,\r\n   @PageNum  int = 1\r\n AS\r\n BEGIN\r\n   SET NOCOUNT ON;\r\n\r\n   DECLARE @s int = (@PageNum-1) * @PageSize + 1;\r\n   DECLARE @e int = @s + @PageSize - 1;\r\n\r\n   WITH r(rn) AS\r\n   (\r\n     SELECT TOP (@PageSize) rn = value\r\n     FROM dbo.GenerateSeries_Split(@s, @e)\r\n   )\r\n   SELECT t.* FROM dbo.things AS t \r\n   INNER JOIN r ON t.rn = r.rn;\r\n END<\/pre>\n
One that uses stacked CTEs:<\/p>\n
 CREATE OR ALTER PROCEDURE dbo.PaginateCols_CTEs\r\n   @PageSize int = 100,\r\n   @PageNum  int = 1\r\n AS\r\n BEGIN\r\n   SET NOCOUNT ON;\r\n\r\n   DECLARE @s int = (@PageNum-1) * @PageSize + 1;\r\n   DECLARE @e int = @s + @PageSize - 1;\r\n\r\n   WITH r(rn) AS\r\n   (\r\n     SELECT TOP (@PageSize) rn = value\r\n     FROM dbo.GenerateSeries_CTEs(@s, @e)\r\n   )\r\n   SELECT t.* FROM dbo.things AS t \r\n   INNER JOIN r ON t.rn = r.rn;\r\n END<\/pre>\n
And one that uses GENERATE_SERIES<\/code> directly:<\/p>\n
 CREATE OR ALTER PROCEDURE dbo.PaginateCols_GenSeries\r\n   @PageSize int = 100,\r\n   @PageNum  int = 1\r\n AS\r\n BEGIN\r\n   SET NOCOUNT ON;\r\n\r\n   DECLARE @s int = (@PageNum-1) * @PageSize + 1;\r\n   DECLARE @e int = @s + @PageSize - 1;\r\n\r\n   WITH r(rn) AS\r\n   (\r\n     SELECT TOP (@PageSize) rn = value\r\n     FROM GENERATE_SERIES(@s, @e)\r\n   )\r\n   SELECT t.* FROM dbo.things AS t \r\n   INNER JOIN r ON t.rn = r.rn;\r\n END<\/pre>\n
Then I created a wrapper that will call each of them with a defined page number – this way I could test the beginning, middle, and end of the set (pagination often sees tanking performance as the page number gets higher). This table is hardly a performance nightmare but if I ran the procedures enough times I would hopefully see some variance.<\/p>\n
 CREATE OR ALTER PROCEDURE dbo.PaginateCols_Wrapper\r\n   @PageNum int = 1\r\n AS\r\n BEGIN\r\n   SET NOCOUNT ON;\r\n\r\n   EXEC dbo.PaginateCols_Split     @PageNum = @PageNum;\r\n   EXEC dbo.PaginateCols_CTEs      @PageNum = @PageNum;\r\n   EXEC dbo.PaginateCols_GenSeries @PageNum = @PageNum;\r\n END<\/pre>\n
If you execute this procedure, you will see 3 output sets that contain rows from sys.columns<\/code>. If you vary the @pagenum<\/code> parameter value, you will see different pages of data from that set, but each three will be the same results. The only difference is the series generating code.<\/p>\n
I turned on Query Store<\/a>, and always want to remind you that QUERY_CAPTURE_MODE = ALL<\/code> is not a production-friendly option – but quite handy if you want to make sure you capture every instance of every query:<\/p>\n
 ALTER DATABASE GenSeries SET QUERY_STORE \r\n (\r\n   OPERATION_MODE              = READ_WRITE,\r\n   QUERY_CAPTURE_MODE          = ALL \/* Do not do this in production! *\/\r\n );<\/pre>\n
I didn’t want to run the procedures a bunch of times manually; I like using sqlstresscmd<\/a> because I can run tests hundreds of thousands of times without guilt about overwhelming a poor UI, or waiting for results to render, or battling resource conflicts and poisoning the test as a result. It runs the queries, discards the results, and that’s it.<\/p>\n
I configured a JSON file called GenSeries.json<\/code> like this, to run each procedure 10,000 times across 16 threads. It took about 5 minutes to run on average:<\/p>\n
 {\r\n   \"CollectIoStats\": true,\r\n   \"CollectTimeStats\": true,\r\n   \"MainDbConnectionInfo\": \r\n   {\r\n     \"Database\": \"GenSeries\",\r\n     \"Login\": \"sa\",\r\n     \"Password\": \"$tr0ng_P@$$w0rd\",\r\n     \"Server\": \"127.0.0.1,2022\"\r\n   },\r\n   \"MainQuery\": \"EXEC dbo.PaginateCols_Wrapper @PageNum = 1;\",\r\n   \"NumIterations\": 10000,\r\n   \"NumThreads\": 16,\r\n   \"ShareDbSettings\": true\r\n }<\/pre>\n
Then ran it using the following:<\/p>\n
 sqlstresscmd -s ~\/Documents\/GenSeries.json<\/pre>\n
Then I collected the average runtimes from Query Store:<\/p>\n
 SELECT qt.query_sql_text,\r\n        avg_duration       = AVG(rs.avg_duration\/1000.0)\r\n   FROM sys.query_store_query_text AS qt\r\n   INNER JOIN sys.query_store_query AS q \r\n     ON qt.query_text_id = q.query_text_id\r\n   INNER JOIN sys.query_store_plan  AS p \r\n     ON q.query_id = p.query_id\r\n   INNER JOIN sys.query_store_runtime_stats AS rs \r\n     ON p.plan_id = rs.plan_id\r\n   WHERE qt.query_sql_text LIKE N'%dbo.things%'\r\n     AND qt.query_sql_text NOT LIKE N'%sys.query_store%'\r\n   GROUP BY qt.query_sql_text;<\/pre>\n
When I wanted to switch to the middle or the end of the set, I ran this query to clear Query Store data. (Note: you will need to capture the results from Query Store each time before executing this statement as this clears everything from Query Store):<\/p>\n
 ALTER DATABASE GenSeries SET QUERY_STORE CLEAR;<\/pre>\n
Then I changed the MainQuery<\/code> line appropriately to run tests for the middle and the end. For rows 1,901 – 2,000:<\/p>\n
 \"MainQuery\": \"EXEC dbo.PaginateCols_Wrapper @PageNum = 20;\",<\/pre>\n
And for rows 3,901 – 4,000:<\/p>\n
 \"MainQuery\": \"EXEC dbo.PaginateCols_Wrapper @PageNum = 40;\",<\/pre>\n
Here are the timing results in milliseconds (click to enlarge<\/a>):<\/p>\n
\"Line<\/a><\/p>\n
In these tests, the split approach was the winner, but the new built-in function is right on its heels. The stacked CTEs, while much more backward-compatible, have become a bit of an outlier.<\/p>\n
I would love to see some flat lines in there, of course, since there shouldn’t be any penalty for jumping ahead to any page; but, not the point today. I do plan to revisit some of my old pagination techniques in a future article.<\/em><\/p>\n
Conclusion<\/h3>\n
As the title suggests, I’m pretty happy with the syntax of GENERATE_SERIES<\/code> so far, and I hope you get to try it out sooner than later! The performance of the split approach is slightly better, but both are still relatively linear and, for the simplicity of the implementation, I’d be inclined to use the newer syntax in most cases. At this scale, we’re talking about single-digit milliseconds anyway, so maybe not all that telling other than “this is worth testing.”<\/p>\n
And to reiterate, this wasn’t meant to show that any of these methods might be better for pagination specifically – it was a completely manufactured scenario where the table just happened to have contiguous row numbers to join to the output. This was more a demonstration of how easy it is to swap GENERATE_SERIES<\/code> into places where you’re using more convoluted methods today.<\/p>\n
Further reading<\/h4>\n
As far as series generation goes, there are other options out there, too, including some from Paul White, Itzik Ben-Gan, and others in this 6-part Number series generator challenge<\/a> from 2021. In particular, there is an interesting solution from Paul White (dbo.GetNums_SQLkiwi<\/code>) in solutions part 4<\/a>, but it does require a little concentration, and is version-limiting (it requires a table with a clustered columnstore index). You should do more thorough testing with his and other approaches from that series, with your data and workload, especially if your primary objective is squeezing performance. Some solutions will only be options if you are on modern versions and\/or have some leeway in implementation (some CLR solutions might be interesting as well).<\/p>\n<\/section>\n","protected":false},"excerpt":{"rendered":"
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……<\/p>\n","protected":false},"author":341115,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143531],"tags":[147170,4745],"coauthors":[158980],"class_list":["post-96175","post","type-post","status-publish","format-standard","hentry","category-featured","category-t-sql-programming-sql-server","tag-sql-server-2022","tag-transact-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96175","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/341115"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=96175"}],"version-history":[{"count":123,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96175\/revisions"}],"predecessor-version":[{"id":96513,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96175\/revisions\/96513"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=96175"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=96175"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=96175"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=96175"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}