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.