InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 22:10:25 GMT20http://www.sqlservercentral.com/Forums/Topic1051921-2571-1.aspxI'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.Mon, 23 Apr 2012 02:26:26 GMTRobin Sassonhttp://www.sqlservercentral.com/Forums/Topic1051921-2571-1.aspxI 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!Sat, 22 Jan 2011 16:53:07 GMTBSavoiehttp://www.sqlservercentral.com/Forums/Topic1051921-2571-1.aspxHere is another way to create your tally table:[code] --==== 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;[/code]Sat, 22 Jan 2011 10:27:18 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic1051921-2571-1.aspxsome methods are discussed here [url]http://www.projectdmx.com/tsql/tblnumbers.aspx[/url]Sat, 22 Jan 2011 09:57:01 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic1051921-2571-1.aspxi haven't tried Azure yet, but why can't you create a permanent Tally table and use that?Sat, 22 Jan 2011 09:03:58 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1051921-2571-1.aspxHere's my obvious, brute force answer. Sure seems slow though[code="sql"]DECLARE @Tally TABLE ( Number int)DECLARE @ThisNum intSET @ThisNum = 0WHILE @ThisNum < 10000 BEGIN INSERT INTO @Tally(Number) VALUES(@ThisNum) SET @ThisNum = @ThisNum + 1END[/code]Fri, 21 Jan 2011 23:54:44 GMTBSavoiehttp://www.sqlservercentral.com/Forums/Topic1051921-2571-1.aspxAnybody know of a good alternative for a "tally table" on SQL Azure? Like most things useful, this is not supported on SQL Azure:[code="sql"]SELECT Number N FROM master..spt_values WHERE TYPE='P'[/code]Fri, 21 Jan 2011 23:46:39 GMTBSavoie