Steve Jones tries different methods:
We published an article recently at SQL Server Central on Tally Tables in Fabric from John Miner. In it he showed how this can be efficient. A day after he published it, he sent me an addendum to note that GENERATE_SERIES was available in Fabric and that could be used.
I ran a few tests last week, but as I read the comments on John’s article, I realized that there were 3 ways of setting up these tally tables that I’ve used and thought I’d summarize them a bit in this post. There’s a fourth way, but I haven’t used it.
My take on this is, it doesn’t really matter which technique you choose, especially if you’re using it to build a permanent tally table that you can later query from any app. In that case, it’s a one-time cost. If you’re building these on the fly often enough that the performance matters, then my first question, instead of “Which of these is fastest?” is, “Why can’t I just have a permanent tally table?”