Steve Jones performs a pair of tests:
I had someone reach out about generate_series() recently, saying they hadn’t realized this was a new feature in SQL Server 2022. They were wondering if it was better than using a tally table.
I didn’t want to do an exhaustive test, but I thought I’d take a minute and try a couple simple things just to see.
Steve used the CTE-based tally table builder, building based on cross joining spt_values. This is one of the classic approaches. The performance differences aren’t enough on their own to justify large-scale changes if you’re using a classical tally table, though it is good to see that GENERATE_SERIES() does perform well. And if you’re not familiar with the power of a tally table, here is one great explanation of the concept.