Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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, May 20, 2016 12:23 PM
Points: 322, Visits: 775
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, May 20, 2016 12:23 PM
Points: 322, Visits: 775
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 @ 1:47 PM
Points: 14,330, Visits: 37,439
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!
Post #1051959
Posted Saturday, January 22, 2011 9:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, April 1, 2016 1:37 AM
Points: 1,779, Visits: 8,370



some methods are discussed here

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




Clear Sky SQL
My Blog
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, May 26, 2016 1:57 PM
Points: 4,373, Visits: 9,663
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 opportunities 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, May 20, 2016 12:23 PM
Points: 322, Visits: 775
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
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, May 23, 2016 9:42 AM
Points: 763, Visits: 530
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