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
    )  
        

 Data in example #TallyExampleData

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.