I recently read an article on ‘Tally’ tables. They are amazingly useful, if you have need to calculate anything in a looping fashion in SQL you really should look into them. The way a Tally table works is you create a table with unique numbers from 1 to x (where x is a very large number, 11,000 is suggested as it is enough numbers to store 365.25 days times 30 years), once you have this table you join it to your query for a super fast tally of numbers.
It seems bizarre at first, but the reason this concept is so fast is it doesn’t have to spend time calculating and waiting. A good example of a practical implementation of a Tally table is for use in situations where you want to pass an array to a sql statement, ever tried? SQL Server does not have the concept of arrays so passing one in requires a bit of a mashup. There are a few methods people will try to do this, one is a comma/semi colon delimited list, eg:
then in the stored proc they will have a loop that finds each comma and execute sql on it.
– or –
They will write dynamic sql, like so:
string sMyList = "'item1','item2','item3'" //in practice this would be constructed in a loop string sSQL = "select column1, column2 from tableX where column1 in (" + sMyList + ")"
Both of these methods have issues, the first query one is slow (it’s looping through items), the second query will never result in the SQL engine pre compiling it (the query is different on every run) which also makes it slow. Not to mention that both methods can have SQL injection issues.
So both methods have issues, so what then do we do to make a tally table work?
For example consider this SQL statement:
DECLARE @Parameter VARCHAR(8000) SET @Parameter = ',Element01,Element02,Element03,' --Note the requirement to have commas both on the end and beginning of the parameter Set Nocount ON SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1) FROM dbo.Tally WHERE N < LEN(@Parameter) AND SUBSTRING(@Parameter,N,1) = ','
This query is run for each row in our Tally table simultaneously, this means no looping, or waiting for computation and results, instead just instant results. What in essence it’s doing is saying “Get me all the rows in the Tally table where the number is less than the length of the parameter passed in.” At this point we have a list of numbers, 1,2,3,4,etc, the list is long enough that if every entry in it was a comma it would still parse the results. Once it has the list of numbers it uses it on each row simultaneously to figure out “is this character a comma? If so show everything after it up to the next comma”. It does this compare for every character in the string.
It’s blazingly fast, my post does not do the concept justice, but I felt why rewrite a post when it’s already been written so well by others? Here’s an article on the basics of Tally tables The “Numbers” or “Tally” Table: What it is and how it replaces a loop, and another article that builds on the concept, showing how to parse a string into multi dimensional array: Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays.
Thanks to SQL Server Central, parts of the code in this post are copied from the articles I’ve linked.