OK, thanks to everyone for responding. I thought there would be no organic way to remove it from the output once it had been selected, and you guys would...

Thank you, but no.

While the output of your proc is correct, I am literally interested in either a process that matches my topic title or a definitive statement...

CirquedeSQLeil (2/24/2010)

---

WayneS (2/24/2010)

---

FYI, Gianluca's unpivot works the fastest on my system (813ms), though Lynn's is right behind (833ms) - initial runs. But, when run repeatedly (and randomly), they actually flip-flop...

WayneS (2/24/2010)

---

FYI, Gianluca's unpivot works the fastest on my system (813ms), though Lynn's is right behind (833ms) - initial runs. But, when run repeatedly (and randomly), they actually flip-flop back...

OK, I have run all four of the ones that are scalable/credible contenders and I have, for 150 000 000 rows using the COUNT (ALL xxxx) method described earlier. ...

This will be the new testing standard, since I own that right now and no one is fighting me for it. You do a COUNT(ALL [put your mod conversion...

OK, I converted Tom's code so that the conversion is done where the select statement is done outside of the CTE and the time for 1 000 000 is the...

OK, Tom. The thing with your TSQL seems to be that while you are indeed generating the numbers, you are not specifically doing so in a way that the...

Interesting Tom. I will study this. On my machine it comes up @ 0:07 (seven seconds!)compared to Lynn's 1:03 (1 minute) w/out a temp table and 2:47...

The N being written to the table allows you to retreive the data in the proper order by including the necessary ORDER BY clause on the query. With out that,...

Lynn Pettis (2/24/2010)

---

My problem with comparing the two solutions above is you are actually comparing apples to oranges. Jason's routine is returning the results directly...

CirquedeSQLeil (2/23/2010)

I think there are a few things to note.

With the size of the recordset, memory becomes an issue. Another is tempdb and disk space become an issue. ...

OK, I ran Jason's (CirquedeSQLeil) updated code (modified by me as before) and got 0:54, which puts it as the fastest time of the scalable three that I reviewed. ...

Review of Kev Riley's v2 solution:

for 150 000 000 the time is 1:38

The modified Riley code used for this test:

declare @maxnum int

set @maxnum = 150000000

set statistics io on

set statistics time...

Review on the performance based answers contest:

Lynn Pettis' solution is very scalable, though not as fast as CirqueDeSQLeil's solution which appears for some reason to be not as scalable. ...