MSSQL - Building a Tally Table

10. March 2011 19:28

I am sure you have come across tally tables on firums or other sources of information on the internet. But what are they for?

The answer to that is really simple. It give you a basic range of numbers that you can reference.

 

So here is a really quick way to build a tally table.

 

 

SELECT TOP 1000000
	IDENTITY(INT,1,1) AS N
	INTO Tally
	FROM sys.all_columns a1, sys.all_columns a2

ALTER TABLE Tally
	ADD CONSTRAINT PK_Tally
	PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
E-mail Kick it! DZone it! del.icio.us Permalink