Tally Tables in SQL

Reynold Greenlaw

A tally table is a table with a set of numbers in, such as all of the integers from 1 to 1,000,000  in order. It may also known as a number table and could contain every day between two dates, or every Monday between two dates, etc.

Tally tables can be used for a large and varied number of ways, to make code simpler and/or faster.

Converting a CSV string into a dataset

A common use of a tally table is in converting a CSV string into a dataset. The obvious way would be to start at the beginning of the string, go forward one character at a time and output a row whenever a comma is found. This is all very well, but not quite set-based (which is what we should be trying to do in SQL). However, you can do this in a tally table like this:

 SET @CSV = 'Testing,testing,one,two,three,,also works with empty ones'
 SET @CSV = ',' + @CSV + ','

SELECT SUBSTRING (@CSV, N + 1, CHARINDEX (',', @CSV, N + 1) - N - 1)
 FROM TSys_Tally_N
 AND (SUBSTRING (@CSV, N, 1) = ',')

This is using (SUBSTRING (@CSV, N, 1) = ',') to find every row in the tally table which corresponds to a comma in the string. For each of these rows, it then uses the value of N to extract the appropriate section from the string and return it to the output dataset.