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:
DECLARE @CSV VARCHAR (8000) 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 WHERE (N < LEN (@CSV)) 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.