Joe Celko s SQL for Smarties - Advanced SQL Programming P26. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 222 CHAPTER 8 TABLE OPERATIONS The default values clause is actually values default default . default so it is just shorthand for a particular single row insertion. The tabular constant insertion is a simple tool mostly used in interactive sessions to put in small amounts of data. A query insertion executes the query and produces a working table which is inserted into the target table all at once. In both cases the optional list of columns in the target table has to be union-compatible with the columns in the query or with the values in the VALUES clause. Any column not in the list will be assigned null or its explicit default value. The Nature of Inserts In theory an insert using a query will place the rows from the query in the target table all at once. The set-oriented nature of an insertion means that a statement like this INSERT INTO SomeTable somekey transaction_time SELECT millions CURRENT_TIMESTAMP FROM HugeTable will have one value for transaction_time in all the rows of the result no matter how long it takes to load them into SomeTable. Keeping things straight requires a lot of checking behind the scenes. The insertion can fail if just one row violates a constraint on the target table. The usual physical implementation is to put the rows into the target table but mark the work as uncommitted until the whole transaction has been validated. Once the system knows that the insertion is to be committed it must rebuild all the indexes. Rebuilding indexes will lock out other users and might require sorting the table if the table had a clustered index. If you have had experience with a file system your first thought might be to drop the indexes insert the new data sort the table and reindex it. The utility programs for index creation can actually benefit from having a known ordering. Unfortunately this trick does not always work in SQL. The indexes maintain the uniqueness and referential integrity constraints and they cannot be easily dropped and restored.
