Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Generate N sequential numbers (fast)

By Brian Bates,

Tally Table

I have seen others post a similar method of creating a Tally Table that was attributed to Itzik Ben-Gan. The posts that I have seen didn't explain how it worked or how to modify the query to adjust the range of numbers returned.

  1. The first table T1 with the UNIONs creates a table with 10 rows.  
  2. This table is CROSS JOINed with itself and creates a second table T2 with 10*10 or 100 rows.
  3. This process is repeated by CROSS JOINing T2 with itself to get table T3 containing 10,000 rows.
  4. One more CROSS JOIN or T3 will create table T4 containing 100,000,000 rows.

Note: If 10,000 rows or less is needed then table T4 does not have to be created, and the final select statement can select from T3 instead.

This results in a table with one field and 100 million rows with each field containing the number 1.  The actual numbers are provide by the ROW_NUMBER() function.  

To get the ROW_NUMBER() function to evaluate an OVER clause is required.  ORDER BY (SELECT NULL) satifies the conditions of the OVER clause and provides the incrementing numbers in an unnamed field.

The TOP clause is used to limit the returned number of rows.  If TOP (1000) is used then a table with 1,000 rows contaning one field with numbers ranging 1-1,000 is created.

Note: If the starting number in the sequence need to be some number other than 1, then an offset can be used in the final SELECT statement to shift the number range in a positive or negative direction.  For Example:

  • To generate 1,000 rows with a range of 0 - 999 use

 SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 FROM T4;
  • To generate 100 rows with a range of 1,000 to 1,099 and give the field a name of 'CheckNumber' use
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) +999 AS CheckNumber FROM T4;

Total article views: 1559 | Views in the last 30 days: 6
 
Related Articles
FORUM

Create Auto Number based on ORder by Field

Create Auto Number based on ORder by Field

FORUM

Using Created Fields in Stored Procedures

Created Field is not available for other created fields?

FORUM

Select just three octets from IP number

Select just three octets from IP number

FORUM

Transact-sql for changing a telephone number

Change numbers in a field

FORUM

help me to select ROW_NUMBER in sql server 2000

help me to select ROW_NUMBER in sql server 2000

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones