SQL Server provides various techniques and tools to optimize query performance. One such technique is the use of tally tables, also known as numbers tables. Tally tables are a simple and efficient way to generate a sequence of numbers that can be used in a variety of scenarios.

In a recent article, I came across a discussion about the use of tally tables in SQL Server. The author presented a solution using a recursive common table expression (CTE) and a while loop to generate the tally table. While this solution worked for small result sets, it had limitations in terms of scalability and performance.

After further research, I discovered an alternative solution that offers better performance and scalability. This solution uses a modified method developed by Itzik Ben-Gan to generate the base result sets for the tally table. Instead of using a recursive CTE and a while loop, this solution utilizes multiple CTEs to generate the necessary result sets.

Let’s take a closer look at this new solution and how it works. The function, dbo.ufn_Tally2, is implemented as an in-line table-valued function. It takes three parameters: the starting value, the ending value, and the increment. The function generates the tally table dynamically based on these parameters.

The function uses five CTEs to generate the base result sets. Each CTE builds on the previous one to complete its work. The first CTE, named BaseNum, generates a result set consisting of ten rows. The second CTE, named L1, generates a result set with one thousand rows by cross joining BaseNum with itself two times. The third CTE, named L2, generates a result set with one million rows by cross joining L1 with itself. The fourth CTE, named L3, generates the final base result set with a maximum of one trillion rows. The actual number of rows returned by this CTE is limited based on the starting value, ending value, and increment parameters.

The fifth CTE, named Tally, uses the final base result set along with the ROW_NUMBER() window function to generate the values used in the final SELECT statement that generates the dynamic tally table.

By using this alternative solution, we can achieve better performance and scalability compared to the recursive CTE and while loop approach. The new solution eliminates the need for recursion and looping, resulting in faster execution times and improved scalability for larger result sets.

To demonstrate the performance difference between the two solutions, I conducted a series of tests using different numbers of rows. The results clearly showed that the new solution outperformed the recursive CTE and while loop approach, especially for larger result sets.

Based on these findings, it is evident that the new solution using tally tables implemented as an in-line table-valued function is the preferred approach for generating dynamic tally tables in a production environment.

In conclusion, tally tables are a powerful tool in SQL Server for generating sequences of numbers. By using an optimized approach like the one described in this article, you can significantly improve performance and scalability in your SQL Server queries.