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

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

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.

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")

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

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

ok then ...
thanks for all of you..

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.

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

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

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

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" ...

Ah, so by "suffix" what he actually meant was "composite" key.