Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Foiled By SQL Azure Again Expand / Collapse
Author
Message
Posted Friday, January 21, 2011 11:46 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 27, 2013 12:16 PM
Points: 309, Visits: 725
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'



.
Post #1051921
Posted Friday, January 21, 2011 11:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 27, 2013 12:16 PM
Points: 309, Visits: 725
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



.
Post #1051922
Posted Saturday, January 22, 2011 9:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:16 PM
Points: 12,744, Visits: 31,079
i haven't tried Azure yet, but why can't you create a permanent Tally table and use that?

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1051959
Posted Saturday, January 22, 2011 9:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:26 AM
Points: 1,949, Visits: 8,291



some methods are discussed here

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




Clear Sky SQL
My Blog
Kent user group
Post #1051969
Posted Saturday, January 22, 2011 10:27 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:17 AM
Points: 4,379, Visits: 9,470
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 Union All
Select 1 Union All Select 1 Union All
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1051972
Posted Saturday, January 22, 2011 4:53 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 27, 2013 12:16 PM
Points: 309, Visits: 725
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!

.
Post #1052003
Posted Monday, April 23, 2012 2:26 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:38 AM
Points: 698, Visits: 507
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.


"Be brave. Take risks. Nothing can substitute experience."
Post #1287985
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse