Tally Tables In SQL Server
This article explains, what are tally tables and how to create them.
Introduction
In this article, I am going to explain about Tally Tables in SQL Server. A Tally Table is a single column table which are used to calculate anything in a looping manner. It improves the performance of query by reducing time for its execution.
Tally Tables
A Tally Table is like any other table with a single column that holds unique numbers from 1(or 0) to any large number as per your requirement. In Tally table, large number should be taken according to your system or application. So try to avoid using very high number.
Creating Tally Table
Suppose we want to create a tally table of numbers from 1 to 10,000. Here is the complete sample code showing how to create temporary table.
set nocount on;
if object_id('dbo.Tally') is not null drop table dbo.tally
go
select top 10000 identity(int,1,1) as ID
into dbo.Tally from master.dbo.SysColumns
alter table dbo.Tally
add constraint PK_ID primary key clustered(ID)
go
select * from dbo.Tally
Output: