SQL Server Tally Table

The following Tally Table will help in future posts.

The Tally table is simply a table with an ID column, that will help in reducing most loops; a personal preference of mine.

CREATE TABLE dbo.Tally (
    ID INT NOT NULL
)
GO
ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally
    PRIMARY KEY CLUSTERED ( ID )
GO

--2048 * 8 = 16384 Results
INSERT INTO dbo.Tally
    SELECT  a.number * 2048 + b.number + 1 ID
      FROM  [master].dbo.spt_values a,
            [master].dbo.spt_values b
    WHERE   a.[type] = 'p'
      AND   b.[type] = 'p'
      AND   b.number < 2048
      AND   a.number < 8
    ORDER BY a.number, b.number
GO

If you want more results, you will need to mess with the “a.number < 8" line in the where clause. Each increase will result in an extra 2048 entries. I find that 16k+ suits my needs just fine.

Profile photo of superadmin

RJ writes custom Windows and Web applications using the Microsoft .NET framework, and enjoys solving problems with code, often while listening to a techno soundcloud stream. He hopes to one day write an application that many businesses will use.

Twitter 

Comments are closed.