Too late now!

I experimented with those union queries but couldn't find the right one, probably having too many unions or too few. Did you arrive at 32 by...

Why thank you Mister Magoo that's very interesting feedback. I have most recently been looking at my version of the 4 billion on a virtual server running SQL Server...

OK, so how far can we push this one then with the tools at my disposal? 100 billion is a nice round number and is just a billion...

Well I ran it on my laptop (2.13 GHz, 1 GB RAM)

and it took 47 minutes for the 4 billion rows. On a more conventional server set up...

OK then, let's take a look and up the ante to 4 billion rows at the same time.

Database in SIMPLE recovery mode.

ZERO growth in database files.

ZERO growth in tempdb files.

set...

In the meantime is this a possibility? This variation also sets the threshold

to 4750104241 (1681 x 1681 x 1681), the same as 41^6.

set statistics time on

;

with

cte1...

However if I rewrite my previous post using the WHERE clause instead of TOP, it makes a massive difference and is very, very close to Tom's.

set statistics time on

;

with

cte1 as

(

select...

Sure beats this more conventional CTE approach by about a factor of 4.

CAVEAT

Don't use this one. There are better ones out there!

set statistics time on

;

with

cte1 as

(

select top 150000000...

In fact there's a simpler way of writing this since you don't need the other columns.

IF NOT OBJECT_ID('tempdb.dbo.#FOO', 'U') IS NULL

DROP TABLE #FOO

select '399.' AS longcolumn INTO #FOO union...

Or for another variation on a theme

IF NOT OBJECT_ID('tempdb.dbo.#FOO', 'U') IS NULL

DROP TABLE #FOO

select '399.' AS longcolumn INTO #FOO union all

select '400.' union all

select '400.367.' union all

select '400.35.369.' union...

Lucky I went for varchar(255) then Jeff.

That should cover it!

Cheers

Steve

Has anyone come up with this one yet?

with

cte1 as

(

select top 100 row_number() over (order by (select 0)) as row from master.sys.all_columns

)

select case when row%15 = 0 then 'FIZZBUZZ' when...

This won't help you in SQL Server 2000 but works nicely in later versions

IF NOT OBJECT_ID('tempdb.dbo.#FOO', 'U') IS NULL

DROP TABLE #FOO

SELECT 'A|B|C|10' AS BulkColumn

INTO #FOO

UNION ALL SELECT 'A|B|C|10|jhv'

UNION...