Joe Celko s SQL for Smarties - Advanced SQL Programming P8. 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. | 42 CHAPTER 1 DATABASE DESIGN Besides the usual problems with exposed physical locators each GUID requires 16 bytes of storage while a simple INTEGER needs only 4 bytes on most machines. Indexes and primary keys built on GUIDs will have worse performance than shorter key columns. This applies to compound keys of less than 16 bytes too. I mention this because many newbies justify a GUID key on the grounds that it will improve performance. Besides being false that level of performance is not a real problem in modern hardware. Computers built on 64-bit hardware are becoming common and so are faster and faster disk drives. The real problem is that GUIDs are difficult to interpret so it becomes difficult to work with them directly and trace them back to their source for validation. In fact the GUID does not have any sorting sequence so it is impossible to spot a missing value or use them to order results. All you can do is use a CHECK with a regular expression for string of 36 digits and the letters A to F broken apart by four dashes. The GUID cannot participate in queries involving aggregate functions you would first have to cast it as a CHAR 36 and use the string value. Your first thought might have been to make it into a longer integer but the two data types are not compatible. Other features of this data type are very proprietary and will not port out of a Microsoft environment. Sequence Generator Functions COUNTER NUMBER IDENTITY and so on are proprietary features that return a new incremented value each time this function is used in an expression. This is a way to generate unique identifiers. This can be either a function call or a column property depending on the product. It is also a horrible nonstandard nonrelational proprietary extension that should be avoided whenever possible. We will spend some time later on ways to get sequences and unique numbers inside Standard SQL without resorting to proprietary code or the use of exposed physical locators in the
