Results 1 to 9 of 9

Thread: sql statement

  1. #1
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Wink Unanswered: sql statement

    Transact-SQL statement that returns as a result set the numbers 1 through 1000, one number per row??

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Every database should have a numbers table



    Failing that, use:
    Code:
    SELECT number
    FROM   master.dbo.spt_values
    WHERE  number BETWEEN 1 AND 1000
    AND    type = 'P'
    Can't remember off the top of my head if you need to chuck a DISTINCT in there or not
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    ;
    with NumList (NumVal) as
    		(select 0 as NumVal
    		UNION ALL
    		select NumVal + 1
    		from NumList
    		where NumVal < 9)
    select	Ones.NumVal + Tens.NumVal * 10 + Hundreds.NumVal * 100 + Thousands.NumVal * 1000
    from	NumList Ones,
    		NumList Tens,
    		NumList Hundreds,
    		NumList Thousands
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Think in sets and not loops

    This is a common problem that some tricks. The first is not to do this more than once; put it in a table so you can re-use it. You will see this called "Tally" (it is not), "Numbers" (too vague) or "Sequence" (now a reserved word). Being a math major, I prefer "Series" and will add columns for other integer function I do not want to re-compute over and over, like an additive congruence generator.

    WITH Digits (i)
    AS (SELECT X.i
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) AS X(i)
    )
    SELECT
    1 + Units.i + 10*Tens.i + 100*Hundreds.i
    FROM Digits AS units,
    Digits AS tens,
    Digits AS hundreds;

    This is portable, ANSI/ISO code.

    The first trick you will see is to use ROW_NUMBER() and a system table that is larger than the taget size. This is not portable.

    The second trick is a recursive CTE. This is really a loop and cursor hidden under the covers. It is slow and non-relational. But some people think it looks cool

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Celko View Post
    The second trick is a recursive CTE. This is really a loop and cursor hidden under the covers. It is slow and non-relational. But some people think it looks cool
    I have to say that I like your solution better than the recursive CTE, but I'll take issue with two points.

    Slow might be relative, but for 1000000 rows there is no material difference in the performance of your solution versus the recursive one. In three trials, they tied once and each method was marginally faster once. From my perspective, that's a draw.

    I'm curious how you see the recursive CTE as non-relational. It isn't tartan either, but neither recursion nor tartan has anything to do with relational in my mind. Coach me, what am I missing?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    procedural vs declarative code

    The way you define a set is by intention or by extension. That is, by a process that generates the set elements (procedural) or by a predicate that applies to an entire set (declarative, functional).

    Even numbers := {i : MOD(i, 2) = 0} as a predicate
    Even numbers := FOR i: = 1 to n
    DO INSERT INTO Evens VALUES (2*i);

    Procedural programming is based on primitive recursive function theory. Using it as the basis for programming really began with Algol (a favorite of mine). The basic operations got mapped into programming constructs:

    Selection: if-then, if-then-else, case, switch, etc

    Iteration: while-do, do-until, while-do-until, for-do, for-do-until, etc. The successor function (or increment for numbers) is the big part of this.

    Sequence: begin-end

    Recursion: f(f()). This was a big fight when Algol did it!

    In the case of recursive CTEs in T-SQL, we only got to the for-do-until loop version. You can see the procedural mindset in this code:

    WITH Series (i)
    AS
    (SELECT i FROM (VALUES (0)) AS X(i)
    UNION ALL -- loop!
    SELECT i + 1 -- increment!
    FROM Series
    WHERE i < 9) -- loop test!

    This is an until loop hidden in syntactic sugar for:

    -- CREATE TABLE Series (i INTEGER NOT NULL);
    -- DELETE FROM Series;
    BEGIN
    DECLARE @i INTEGER;
    SET @i = 0;
    WHILE @i <= 9
    BEGIN
    INSERT INTO Series VALUES (@i);
    SET @i = @i +1;
    END;
    END;

    Now look at:

    WITH Digits (i)
    AS (SELECT X.i
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) AS X(i)
    )
    SELECT*
    1 + Units.i + 10*Tens.i + 100*Hundreds.i*
    FROM Digits AS units,*
    Digits AS tens,*
    Digits AS hundreds;

    No loops, no increment, no process. In fact, the numbers will be out of sequence in the table, and not inserted in order. If I have parallelism, I do not have to wait for the set to be built.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Surely there is a programmatic loop occurring in
    Code:
    SELECT X.i
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) AS X(i)
    It's just shifted behind the scenes, away from the coding logic. It performs a constant scan, which steps through the values one at a time.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and (I'll admit this surprised me), the execution plan generated from running both simultaneously showed the VALUES method requiring 62% of the query cost, and the recursive CTE requiring only 38%. Not that efficiency is a huge concern here. I'll grant that the VALUES method is more readable, but claiming that it avoids loops is not exactly true.
    Last edited by blindman; 04-03-13 at 15:15.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Functional vs Procedural

    On something this small, just about anything will work. But the real advantage of functional/declarative programming is:
    1. In environments that are parallelizable; distributive and associative properties are not just for High School algebra. Talk to someone using Erlang
    2. When you have to port a tool to a totally new platform; implement the basic stuff and use it as the basis set for the operators
    3. When optimized code is important. Any small improvement in that basis set cascades when you run old code with the newer tool. We are used to that in SQL and now in F#!


    But my main argument is that it is a better mindset for problem solving in general as well as for RDBMS.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •