Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL (The Morgan Kaufmann Series in Data Management Systems)
Format: PDF / Kindle (mobi) / ePub
Perfectly intelligent programmers often struggle when forced to work with SQL. Why? Joe Celko believes the problem lies with their procedural programming mindset, which keeps them from taking full advantage of the power of declarative languages. The result is overly complex and inefficient code, not to mention lost productivity.
This book will change the way you think about the problems you solve with SQL programs.. Focusing on three key table-based techniques, Celko reveals their power through detailed examples and clear explanations. As you master these techniques, you’ll find you are able to conceptualize problems as rooted in sets and solvable through declarative programming. Before long, you’ll be coding more quickly, writing more efficient code, and applying the full power of SQL
• Filled with the insights of one of the world’s leading SQL authorities - noted for his knowledge and his ability to teach what he knows.
• Focuses on auxiliary tables (for computing functions and other values by joins), temporal tables (for temporal queries, historical data, and audit information), and virtual tables (for improved performance).
• Presents clear guidance for selecting and correctly applying the right table technique.
external 3GL language. 3. Write a query that does the parsing, but without any real error handling. The skeleton of a procedure with an IN() predicate from parameter is usually like this: 1. Accept a list of parameters—again, 10 to 25 is usually more than enough. Let the T-SQL, SQL/PSM, Informix 4GL, or whatever procedural language do its parsing per the rules of the vendor’s provided functions. If there are bad parameters, 284 CHAPTER 14: USING PROCEDURE AND FUNCTION CALLS the
supervisor, who must be in either job category A or job category B. 4. Let’s assume that nobody can be their own supervisor. This is a minimal set of rules that we expect to become more and more complex over time. One proposal was to divide job category X into two categories; call them XA and XB, respectively. All the XA people would have A supervisors, and all the XB people would have B supervisors. 17.2 Complex Constraints via CHECK() and CASE Constraints 303 Mr. Nolan immediately
xviii PREFACE actually think and speak that foreign language and do not have to really focus on the effort. The initial phase in using SQL programming is just copying existing code blindly from someone else’s programs. This is not really programming. You might as well be using a GUI tool that assembles SQL from text fi les without ever showing you the actual code. The next step is writing new SQL code, but doing it as if it were your original or best-known language. A symptom of this mind-set
the level of minutes or seconds. But such tables can be useful for reporting events at 9.5 Calendar Tables 187 a fi ner granularity, such as tracking a manufacturing process in 1 minute or smaller steps for a single day. You would have 1,440 minutes and 86,400 seconds per day. The trick is to create a VIEW that updates itself for you. First, create and populate a table of “clock ticks” at the level you desire. CREATE TABLE ClockTicks (start_tick INTERVAL MINUTE TO SECOND PRIMARY KEY,
Splitting Across Tables If you saw tables for “MalePersonnel” and “FemalePersonnel”, you would recognize immediately that the gender attribute had been used to split a “Personnel” table apart. However, you will constantly see table split on temporal attributes—a table for each month or year of some entity. The result is that you will UNION or UNION ALL things together constantly to get the table you should have had in the fi rst place. Do not confuse a split table with a partitioned table. A