Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Unanswered: Number table builder question

    Why does
    Code:
    SELECT
       Identity(int, 0, 1) AS n
       INTO Numbers
       WHERE  Coalesce(Scope_Identity(), 0) < 65535
    generate one surprise, while
    Code:
    CREATE TABLE dbo.Numbers 
    ( 
       n		INT		IDENTITY(0, 1)
          PRIMARY KEY CLUSTERED 
    ) 
     
    WHILE Coalesce(Scope_Identity(), 0) <= 65535 
       INSERT dbo.Numbers DEFAULT VALUES
    generates a different surprise?

    -PatP

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    your little game does not work in reverse.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, I have to admit, I am too lazy to run the sample code, but I am going to bet that the initial return from scope_identity being NULL would give you some sort of heartache.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, the NULL value is handled as I'd expect by Coalesce(). The SELECT INTO only generates a row for zero, and the WHILE loop always generates one more row than I'd expect (65536 is present).

    I'm at a loss for the moment.

    -PatP

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    question: are we doing this in one window, one right after the other. or are we doing this in different scopes?

    I am going for my walk.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by Pat Phelan
    No, the NULL value is handled as I'd expect by Coalesce(). The SELECT INTO only generates a row for zero,
    why is this a surprise?

    it seems to me that this would only insert one record.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Code:
    WHILE Coalesce(Scope_Identity(), 0) <= 65535 
       INSERT dbo.Numbers DEFAULT VALUES
    come on Pat. Are you having a stroke?

    it evaluates the previous Identity, and it inserts 1 more. You solve that problem by removing the = from the operator.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Good catch on the equality... I'd edited that thing so many times that I missed that. I still can't figure out why the SELECT only includes the zero row.

    I've been working on a lot of different projects today (scattered hither and yon). One more SPUFI and I'm done for the day, and maybe this will look different tomorrow.

    I've attached the files that I'm using if anyone wants to play along at home! You'll probably need to edit the server and database arguments in the Numbers.bat file, but otherwise I'd think it would play nicely for others.

    -PatP
    Attached Files Attached Files

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan
    Why does
    Code:
    SELECT
       Identity(int, 0, 1) AS n
       INTO Numbers
       WHERE  Coalesce(Scope_Identity(), 0) < 65535
    generate one surprise
    You have no from clause so I expect one row. If you add e.g.
    from OneMilionRowTable
    then I'll expect 1 Mil rows
    Scope_Identity() is from a previous identity so if you had a previous identity>=65535 then I expect no rows

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Pat is either playing a game with us or he was having a low blood sugar. Sometimes i think there is a positive correlation between my blood sugar and my IQ. My BS reading was 82 just now which makes me mildly mentally challenged.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SPUFI?

    As in OS/390?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by Brett Kaiser
    SPUFI?

    As in OS/390?
    Is that a "misery loves company" question?

    -- This is all just a Figment of my Imagination --

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    SPUFI?

    As in OS/390?
    I don't think SPUFI comes in any other flavor.

    -PatP

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Thrasymachus
    Pat is either playing a game with us or he was having a low blood sugar. Sometimes i think there is a positive correlation between my blood sugar and my IQ. My BS reading was 82 just now which makes me mildly mentally challenged.
    I do Ok into the 70s, and still am marginally functional into the high 40s, but yesterday was not a good day for me. I've been wrangling with things for almost two months, looking at 300 point swings in both directions! It has NOT been much fun.

    -PatP

Posting Permissions

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