Foiled By SQL Azure Again

  • 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'


  • 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



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


    --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!

  • some methods are discussed here

    Clear Sky SQL
    My Blog[/url]

  • 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
    “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

  • 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!


  • 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:

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

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