Foiled By SQL Azure Again

Question

Post reply

Foiled By SQL Azure Again

BSavoie

SSCertifiable

Points: 6309
More actions
January 21, 2011 at 11:46 pm

#230680

Anybody know of a good alternative for a "tally table" on SQL Azure? Like most things useful, this is not supported on SQL Azure:
SELECT Number N FROM master..spt_values WHERE TYPE='P'
.

Viewing 7 posts - 1 through 6 (of 6 total)

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

BSavoie SSCertifiable Points: 6309 More actions · Answer 1

Here's my obvious, brute force answer. Sure seems slow though

DECLARE @Tally TABLE ( Number int)

DECLARE @ThisNum int

SET @ThisNum = 0

WHILE @ThisNum < 10000 BEGIN

INSERT INTO @Tally(Number) VALUES(@ThisNum)

SET @ThisNum = @ThisNum + 1

END

.

Lowell SSC Guru Points: 323504 More actions · Answer 2

i haven't tried Azure yet, but why can't you create a permanent Tally table and use that?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 3

some methods are discussed here

http://www.projectdmx.com/tsql/tblnumbers.aspx

Clear Sky SQL
My Blog[/url]

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 4

Here is another way to create your tally table:

--==== cteTally

With e1 (n)

As ( --=== Create Ten 1's

Select 1 Union All Select 1 Union All

Select 1 Union All Select 1)

, e2 (n) As (Select 1 From e1 a, e1 b)

, e3 (n) As (Select 1 From e2 a, e2 b)

, cteTally(n) As (Select row_number() over(Order By (Select n)) From e3)

Select *

From cteTally;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

BSavoie SSCertifiable Points: 6309 More actions · Answer 5

I guess a permanent tally makes the most sense. The cte tally table is nice for my situation, but I'll probably need it again before long. Thanks!

.

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 6

I'd recommend a tally table. Keep the computations to a minimum as this will result in more compute cycles which will impact on the cost of the instance over time.

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation: