Loops
At some point in our work we use them because they are a basic programming structure. But in SQL queries loops and cursors are not always the best option, these could slow our process. In scenarios where there is few data, everything runs smoothly. But when we have to deal with high volumes of data these can seriously affect the performance.
This post is about a less known alternative of loops in SQL- the TALLY TABLE.
The Tally table are tables that have a single column. In this column numbers are entered in an orderly manner from 0 or 1, to a number that is useful to us. For example: If you work with a routine, you need to go all the count of a column values and the max value of the column is 500 than you should fill 1 to 500, which is the maximum number of values in that column.
Sample data table
CREATE TABLE #TallyExampleData ( Name VARCHAR(50) , Location VARCHAR(50) , Hits INT )
Now lets create the tally table and cross join with #TallyExampleData table which will give us the multiple rows for the number of hits. We could have also used a function like-
SELECT * FROM #TallyExampleData CROSS APPLY dbo.fnMultiRows(Hits) X
But this function again requires a while loop which I wanted to avoid.
Solution –
DECLARE @count INT SELECT @count = MAX(Hits) FROM #TallyExampleData SELECT TOP ( @count ) N = IDENTITY( INT, 1, 1) INTO #tally FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns b; SELECT * FROM #TallyExampleData TA CROSS JOIN #tally tl WHERE tl.N <= TA.Hits
In terms of performance prospective tally tables are blazing fast, with another query which I used for some data migration. It took me just 2 m 3 sec to process and insert more than 11 million records where the hits count was near about 500 hits .
Do you have any application for a TALLY TABLE? Put it in the comments and share with others.