Results 1 to 13 of 13

Thread: Confused ...

  1. #1
    Join Date
    Jan 2006
    Posts
    3

    Question Confused ...

    Hi, I want to ask some questions:
    1. Is view faster than select?
    2. May I delete record(s) from database? If not, why?
    3. What if my tables grow very large and there is no more free space left on disk? Should I back the data up or add new storage device? Which one is better?
    4. What is the difference among Interactive SQL, Static SQL, dan Dynamic SQL?

    Thanks a lot .....

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. no
    2. sure
    3. do both
    4. interactive sql prompts you for some value(s), dynamic sql allows some value(s) to be given at execution time, static sql does neither
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Posts
    330
    Quote Originally Posted by r937
    1. no
    4. interactive sql prompts you for some value(s), dynamic sql allows some value(s) to be given at execution time, static sql does neither
    I was under the impression that you could have static sql packages that would accept values that are used in the query execution. The big difference in static SQL is that the access path for the query can be stored in the DBMS and doesn't have to be re-calcuated at execute time.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    urquel: you're undoubtedly right about the packages (but i don't know what a package is)

    however, i would call that behaviour dynamic ("allows some value(s) to be given at execution time")
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2006
    Posts
    3
    Thanks for you response. I just a little bit curious about deleting a record from database. If I delete record from database and some day I want to get data from that record again, how could I get it? May be it's somekind like history ( e.g.: product history, customer history ).

    Thanks in advance ...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you want to keep a history of deleted rows, you must design a history table, and copy the data to it before you delete the row from your main table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2006
    Posts
    3
    ok then ...
    thanks for all of you..

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by r937
    if you want to keep a history of deleted rows, you must design a history table, and copy the data to it before you delete the row from your main table
    or add a field to the table in question to indicate record "deletion" status. When deleting a record, instead of deleting it, change the status flag.

    When viewing the records, either add a where clause to filter out the 'deleted' records, or better yet, add a view with the 'deleted' records filtered out.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    If you have a real database (not a toy) for commercial purposes, you are definitely better off:
    - with one table (containing both current and 'deleted' rows) rather than a separate history table
    - use the column indicating 'deleted' as a suffix to the primary key

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Interesting observation, but most of the Reel (and the more popular Reel-to-Reel) databases went out before SQL became popular. While deleted flags were very common at one time, and are still supported in many legacy systems (because that is how the system was designed, and the cost of rewriting the existing code is higher than the cost of maintaining it), I haven't seen a system designed that way at the application level in over twenty years.

    While some database engines (such as dBase) still use a delete flag based architecture behind the scenes, I didn't know that there were new production applications being designed that way.

    Are delete flag based designs still common in Australia, or are they specific to you or your company?

    -PatP

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    "deleted" columns are sometimes appropriate, but not always. They definitely have their drawbacks, as every reference to the table has to include a filter against the "deleted" column. This bloats the code, slows down the execution time, and invites erroroneous results in ad-hoc queries.

    I have used both "deleted" columns and various forms of history tables on different occasions.

    I haven't a clue what you mean by this:
    Quote Originally Posted by DerekA
    - use the column indicating 'deleted' as a suffix to the primary key
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what that means is that if you have a table with a primary key, and you keep deleted rows in the same table, then you will have multiple rows per primary key, and therefore the primary key needs to be redefined to include the status column

    in practice, this means that you can only ever delete a row once, which means that you cannot restore it, either, unless of course you restore it to a status code like "restored"

    and then i guess if you need to delete it again, you would use the status code "re-deleted" ...

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

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Ah, so by "suffix" what he actually meant was "composite" key.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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