Tally Tables In SQL Server

This article explains, what are tally tables and how to create them.
  • 1832

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:

tallyTables-sql.jpg


Categories

More Articles

© 2013 dotNetheaven. All rights reserved.