Page 1 of 3 123 LastLast
Results 1 to 15 of 42

Thread: Insert Question

  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Unanswered: Insert Question

    I have found a proc that reads as follows:

    CREATE PROCEDURE InsertArea
    @AreaDescr as varchar(50)
    as
    insert into tblAreas
    select isnull(max(AreaID)+1,1), @AreaDescr
    from tblAreas
    GO

    My question: tblAreas is a simple lookup table but why would I use this method of managing the AreaID instead of just inserting the record using an Identity column and being done with it? So I am confused on whether there is a hidden secret here or if the person wasn't aware of the Identity field = not knowing what they were doing? All of the tables in this database are like this as well, not just an isolated few.

    Thanks for any input.
    Greg

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Just use an identity column, if you can. This kind of logic is leftover from either an old database engine, or an old database designer.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Generally speaking, I agree with blindman. The only time I would use this type of process is when the "business logic" dictates that there can not be missing numbers, which as we all know can happen with an identity column.
    Paul

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Any logic that relies upon the relative values of a surrogate key is not business logic. It's just bad logic.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I respect your opinion. How would you populate a field that needed to increment by one, and could not skip numbers? Say, a check number field or something like that.
    Paul

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pbaldy
    How would you populate a field that needed to increment by one, and could not skip numbers? Say, a check number field or something like that.
    if that is indeed a requirement, then i would pre-populate the numbers manually

    in other words, if you issue a cheque book with cheques numbered 001 through 150, then insert those numbers into (some) table and indicate that they have been issued but not disbursed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by r937
    if that is indeed a requirement, then i would pre-populate the numbers manually

    in other words, if you issue a cheque book with cheques numbered 001 through 150, then insert those numbers into (some) table and indicate that they have been issued but not disbursed
    Huh? I am not a big fan of identity either but can you explain the rationale for this for us lesser mortals. Pre-populate? How do you make this scalable? A really big table with empty records waiting to be filled in? Do you add so many empty records a day? What if you go over your set amount. It's early, am I misreading this?
    “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
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus
    Huh? I am not a big fan of identity either but can you explain the rationale for this for us lesser mortals.
    please, sean, that's inflammatory language, let's not get into another flamefest

    buddy asked a question, i told him one way how, and you're questioning why

    it's not a "why" question, just accept the requirement to generate a sequence of numbers with absolutely no gaps, and let's see your suggestion for this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I did not think it was inflamatory at all. I was asking a question.

    ~"The IT world can be harsh" - Rudy from the other day.

    Any time you poke fun at me it can be justified. When I ask a legit question, it's a flame war.

    And you did not answer me.

    First I would ask this guy why it is a requirement that there are no missing numbers. I suspect deep down there is some suspect logic here. Then I would ask him how this works out if one of these rows are deleted. Does he repopulate this column and re-establish his relationships each time? The whole thing is a bad idea.
    Last edited by Thrasymachus; 09-22-05 at 09:34.
    “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.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think it was more of an inphlegmatory statement.

    I like Rudy's idea of prepopulating check numbers when they are issued, and then complete them when they are disbursed. Remember, even check numbers these days are not always sequential. Sometimes I write checks from two different sets alternately, and then there are counter-checks and electronic checks too.
    But I also suspect, like Sean, that if there is a need for a gapless incrementing value then there is a flaw in the application concept.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Get the hose...

    Damn.

    us mere mortals....Sean, you still smarting from the pad left with zeroes thread?

    The answer to this thread is "Wake up and rework your model"


    Plus the sproc he "found" is a poor construction to replace identity. It show lack of forsight in a multiuser environment, and can get hosed easily, and rather quickly.

    And the check analogy, what if I cancel a check..bad analogy too, but is a "missing" number in a sequence, at least from the banks perspective.

    And if you want a non-identity, Identity column

    Try this

    http://weblogs.sqlteam.com/brettk/ar...6/29/1687.aspx
    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
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    And the check analogy, what if I cancel a check..bad analogy too, but is a "missing" number in a sequence, at least from the banks perspective.
    no, it isn't, it's an excellent example

    and no, it isn't missing at all, it's just cancelled

    it's the same situation joe celko refers to in several of his examples -- what did we do in the old days of pre-printed order forms, when you spilled coffee on an order form, you couldn't use that one, but you couldn't just destroy it, you have to account for that order number in the order number sequence somehow

    just saying "rework the model" does not really address the business requirement adequately

    rework it how, exactly?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    just saying "rework the model" does not really address the business requirement adequately
    So when the business requirement is flawed, then we fix it with code? I usually go back to the Business Analyst and send them back to the project sponsor to fix the underlying problem... I rarely fix a bad business process by adding code to it.

    -PatP

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    I rarely fix a bad business process by adding code to it.
    geez you guys are stubborn

    okay, let's suppose neil diamond has personally autographed a series of 100 gold-plated cds of "margaritaville" and these are now up for sale

    your job is to record who purchased each one

    you need to assign a "record number" (pun partially intentional) numbered 001 through 100 to each of these discs

    you're going to go back to the sponsor and tell them this is a bad business decision? tell them you can't assign a sequential number because it might have gaps and you don't know how to do handle them?

    sheesh!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Ok what happens when one of the records (pun partially intended again) turns out to be non readable and the purchaser rejects the record.
    Get yourself a copy of the The Holy Book

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

Posting Permissions

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