SQL Server 2022’s GENERATE_SERIES Doesn’t Suck Anymore

Life Comes At You Fast


The summer intern at Microsoft was hard at work between CTPs. Last time around, there were some serious performance issues with our dear new friend GENERATE_SERIES.

With the release of CTP 2.1, the problems that we saw the first time around are all gone. But there are still a couple small caveats that you should be aware of.

There’s also been a change in the way you call the function, too. You not longer need the START and STOP identifiers in the function.

You just put in whatever numbers you’re into.

That’s probably a good thing.

Caveat #1: Parallelism Restrictions


Loading data into tables that have row store indexes on them can’t go parallel.

DROP TABLE IF EXISTS
    dbo.art_aux;

CREATE TABLE 
    dbo.art_aux
(
    id int NOT NULL PRIMARY KEY CLUSTERED
);

INSERT INTO
    dbo.art_aux WITH(TABLOCK)
(
    id
)
SELECT
    gs.value
FROM GENERATE_SERIES
     (
         1, 
         10000000
     ) AS gs
OPTION
(
    MAXDOP 8, 
    QUERYTRACEON 8649
);

But performance is still pretty good, here. Much better than prior.

SQL Server Query Plan
cooperation

I think most folks out there would be okay waiting a few seconds for 10 million rows to show up in a table for them.

So sequential. Much unique.

But, what if you want to load those rows in faster?

Caveat #2: Use A Heap Or SELECT INTO Instead


If you create a heap, or just SELECT INTO on instead, you’ll get a parallel load into the table.

For 10 million rows, there’s an improvement of about 1.7 seconds on my sort of crappy travel VM with 4 available CPUs.

DROP TABLE IF EXISTS
    dbo.art_aux;

CREATE TABLE 
    dbo.art_aux
(
    id int NOT NULL
);

INSERT INTO
    dbo.art_aux WITH(TABLOCK)
(
    id
)
SELECT
    gs.value
FROM GENERATE_SERIES
     (
         1, 
         10000000
     ) AS gs;

Here’s the plan:

SQL Server Query Plan
hot swap

Yes, dear reader, I even blog on vacation. That’s how much I love and care about you.

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.



4 thoughts on “SQL Server 2022’s GENERATE_SERIES Doesn’t Suck Anymore

  1. First, than you for the awesome articles and what you do for this community. I really appreciate it.

    If you create a HEAP and load it, you’ll need to create the CI separately. That means left over free space that you might not want and, unless you can’t got to something other than the FULL recovery model, there’s no change of going “Minimally Logged” either.

    And, I have to ask… what with the “Comments are closed” thing you do? Why do you close comments at all, never mind so quickly? Not a jab… I’m genuinely interested in why people do such a thing. I can understand it for some because their article might make sucking sounds especially with code but you don’t write bad code.

      1. Erik, I noticed the “Comments are closed.” as well recently…if you check either of the blog links for Generate Series mentioned in this article, they indicate comments are closed. Also, I really enjoy your writing style and I appreciate all of the information you share through your blog with the community. Thanks,

Comments are closed.