To Generate More than One Billion Numbers on the fly

Question

To Generate More than One Billion Numbers on the fly

karthik M

One Orange Chip

Points: 29139
More actions
December 31, 2007 at 2:12 am

#182262

Experts,
I have used the below code to generate the 100 numbers on the fly.
---------------------------------------------------------------
Select Ones.Number*10+Tens.Number As No
(Select 0 As Number
Union Select 1
Union Select 2
Union Select 3
Union Select 4
Union Select 5
Union Select 6
Union Select 7
Union Select 8
Union Select 9
)As Ones
CROSS JOIN
(Select 0 As Number
Union Select 1
Union Select 2
Union Select 3
Union Select 4
Union Select 5
Union Select 6
Union Select 7
Union Select 8
Union Select 9
)As Tens
--------------------------------------------------------------
Now i modified the code to generate 1000 numbers on the fly.
Select Ones.Number*10+Tens.Number+Hundreds.Number As No
(Select 0 As Number
Union Select 1
Union Select 2
Union Select 3
Union Select 4
Union Select 5
Union Select 6
Union Select 7
Union Select 8
Union Select 9
)As Ones
CROSS JOIN
(Select 0 As Number
Union Select 1
Union Select 2
Union Select 3
Union Select 4
Union Select 5
Union Select 6
Union Select 7
Union Select 8
Union Select 9
)As Tens
CROSS JOIN
(Select 0 As Number
Union Select 10
Union Select 20
Union Select 30
Union Select 40
Union Select 50
Union Select 60
Union Select 70
Union Select 80
Union Select 90
)As Hundred
---------------------------------------------------------------
Again i modified the code to generate 10000 numbers on the fly.
Select Ones.Number*100+Tens.Number+Hundreds.Number+Thousand.Number*100
As No
(Select 0 As Number
Union Select 10
Union Select 20
Union Select 30
Union Select 40
Union Select 50
Union Select 60
Union Select 70
Union Select 80
Union Select 90
)As Ones
CROSS JOIN
(Select 0 As Number
Union Select 1
Union Select 2
Union Select 3
Union Select 4
Union Select 5
Union Select 6
Union Select 7
Union Select 8
Union Select 9
)As Tens
CROSS JOIN
(Select 0 As Number
Union Select 10
Union Select 20
Union Select 30
Union Select 40
Union Select 50
Union Select 60
Union Select 70
Union Select 80
Union Select 90
)As Hundred
CROSS JOIN
(Select 0 As Number
Union Select 1
Union Select 2
Union Select 3
Union Select 4
Union Select 5
Union Select 6
Union Select 7
Union Select 8
Union Select 9
)As Thousand
----------------------------------------------------------------------------
I know we can use master.dbo.spt_values to generate numbers on the fly.But it limits to 255 only.
As Mr.Jeff told,I know we can use
select TOP 10000
IDENTITY (int,1,1)
into dbo.Tally
from master.dbo.syscolumns s1,master.dbo.syscolumns s2
to generate 10000 numbers on the fly.
Here,Suppose if i want to create more than 1 billions numbers on the fly.How ? shall i need to extent the logic of the above code?
Inputs are Higly appreciable.
karthik

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Cross join the table in a couple more times untill you have the number of rows you need.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 2

Yes,We can do it. But i don't want to add more cross join and sql queries.

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Then run it in a loop a few thousand times, inserting into a table with an identity.

Is there any reason you refuse the first suggestion offered fo a particular problem time and time again?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Yes,Because we need to extent our query.Then it may keep on going.

is there any way to generate more than one billion row with some modification in the current code ?

karthik

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

You can use the function on this link to generate up to 16,777,216 rows at a time for any valid datatype integer range.

I recommend that you do not insert more than about 10,000,000 rows at a time. Larger numbers or rows will generate huge transactions. Just run this function in a loop to insert 10,000,000 at a time until you have a billion rows.

Number Table Function

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 6

Why on Earth would you want to generate a Billion sequential numbers? That will take 8 gigabytes of space all by itself... it is not practical to do such a thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 7

Jeff Moden (12/31/2007)
Why on Earth would you want to generate a Billion sequential numbers? That will take 8 gigabytes of space all by itself... it is not practical to do such a thing.

I agree, why do you need one-billion numbers?

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 8

Jason! Hey! How's the new job???

I'll bet I know why he wants it... it's probably to find missing ID's in a table. My first reaction would be filling in missing ID's is a waste of time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 9

I am having 1400000(Normaly we say it as 14 Lakhs Records)records in a table. Now i want to create Sequnece number for these records.

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Add a column of type int (that's 1.4 million records you have. Int goes up to 2.1 billion) and set identity on.

Edit: And if you do expect more than 2.1 billion rows, make the column bigint.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 11

Gail is 100% correct... if all you want to do is add a "sequence" number to the table, then just add an IDENTITY column as she described.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EdVassie SSC Guru Points: 60476 More actions · Answer 12

Use a recursive CTE to do this, it is far more elegant than multiple cross joins.

Graeme Birchall has superb examples of generating test data using recursive CTEs in his DB2 SQL Cookbook. DB2 and SQL Server have very similar DML, and just about all the syntax Graeme uses in his recursion examples runs unchanged in SQL.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

CTEs are nice for generating smallish amounts of data, but the max number of rows a recursive CTE can return (without using cross joins, union or while loop) is 32767. It's the max value permissible for MAX_RECURSION.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EdVassie SSC Guru Points: 60476 More actions · Answer 14

Aye, but you can use OPTION (MAXRECURSION 0) to allow unlimited recursion, and get billions of rows in the result set if you need them.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara