tailieunhanh - SQL PROGRAMMING STYLE- P46

SQL PROGRAMMING STYLE- P46:Im mot trying to teach you to program in SQL in this book. You might want to read that again. If that is what you wanted, there are better books. This ought to be the second book you buy, not the first. I assume that you already write SQL at some level and want to get better at it. If you want to learn SQL programming tricks, get a copy of my other book, SQL for Smarties (3rd edition, 2005). | x CONTENTS Enumeration Encoding 86 Measurement Encoding 87 Abbreviation Encoding 87 Algorithmic Encoding 88 Hierarchical Encoding Schemes 89 Vector Encoding 90 Concatenation Encoding 91 General Guidelines for Designing Encoding Schemes 92 Existing Encoding Standards 92 Allow for Expansion 92 Use Explicit Missing Values to Avoid NULLs 92 Translate Codes for the End User 93 Keep the Codes in the Database 96 Multiple Character Sets 97 6 Coding Choices 99 Pick Standard Constructions over Proprietary ConstructionslOO Use Standard OUTER JOIN Syntax 101 Infixed INNER JOIN and CROSS JOIN Syntax Is Optional but Nice 105 Use ISO Temporal Syntax 107 Use Standard and Portable Functions 108 Pick Compact Constructions over Longer Equivalents 109 Avoid Extra Parentheses 109 Use CASE Family Expressions 110 Avoid Redundant Expressions 113 Seek a Compact Form 114 Use Comments 118 Stored Procedures 119 Control Statement Comments 119 Comments on Clause 119 Avoid Optimizer Hints 120 Avoid Triggers in Favor of DRI Actions 120 Use SQL Stored Procedures 122 CONTENTS xi Avoid User-Defined Functions and Extensions inside the Database 123 7 Multiple Language Problems 124 Portability Problems 124 Optimization Problems 124 Avoid Excessive Secondary Indexes 124 Avoid Correlated Subqueries 125 Avoid UNIONs 127 Testing SQL 130 Test All Possible Combinations of NULLs 130 Inspect and Test All CHECK Constraints 130 Beware of Character Columns 131 Test for Size 131 How to Use VIEWS 133 VIEW Naming Conventions Are the Same as Tables 135 Always Specify Column Names 136 VIEWs Provide Row- and Column-Level Security 136 VIEWs Ensure Efficient Access Paths 138 VIEWs Mask Complexity from the User 138 VIEWs Ensure Proper Data Derivation 139 .