Celko Joe Celko's SQL Programming Style

1. Auflage 2005
ISBN: 978-0-08-047883-8
Verlag: Elsevier Science & Techn.
Format: EPUB
Kopierschutz: 6 - ePub Watermark

E-Book, Englisch, 272 Seiten

Are you an SQL programmer that, like many, came to SQL after learning and writing procedural or object-oriented code? Or have switched jobs to where a different brand of SQL is being used, or maybe even been told to learn SQL yourself?

If even one answer is yes, then you need this book. A Manual of Style for the SQL programmer, this book is a collection of heuristics and rules, tips, and tricks that will help you improve SQL programming style and proficiency, and for formatting and writing portable, readable, maintainable SQL code. Based on many years of experience consulting in SQL shops, and gathering questions and resolving his students' SQL style issues, Joe Celko can help you become an even better SQL programmer.

+ Help you write Standard SQL without an accent or a dialect that is used in another programming language or a specific flavor of SQL, code that can be maintained and used by other people.
+ Enable you to give your group a coding standard for internal use, to enable programmers to use a consistent style.
+ Give you the mental tools to approach a new problem with SQL as your tool, rather than another programming language - one that someone else might not know!

Autoren/Hrsg.

Celko, Joe

Inhaltsverzeichnis

1;Front cover;1
2;Title page;4
3;Copyright page;5
4;Table of contents;8
5;front matter;16
6;Introduction;16
6.1;1.1 Purpose of the Book;17
6.2;1.2 Acknowledgments;18
6.3;1.3 Corrections, Comments, and Future Editions;19
7;First chapter;20
8;1. Names and Data Elements;20
8.1;1.1 Names;21
8.1.1;1.1.1 Watch the Length of Names;21
8.1.2;1.1.2 Avoid All Special Characters in Names;22
8.1.3;1.1.3 Avoid Quoted Identifiers;23
8.1.4;1.1.4 Enforce Capitalization Rules to Avoid Case- Sensitivity Problems;25
8.2;1.2 Follow the ISO-11179 Standards Naming Conventions;26
8.2.1;1.2.1 ISO-11179 for SQL;27
8.2.2;1.2.2 Levels of Abstraction;28
8.2.3;1.2.3 Avoid Descriptive Prefixes;29
8.2.4;1.2.4 Develop Standardized Postfixes;31
8.2.5;1.2.5 Table and View Names Should Be Industry Standards, Collective, Class, or Plural Nouns;33
8.2.6;1.2.6 Correlation Names Follow the Same Rules as Other Names . . . Almost;34
8.2.7;1.2.7 Relationship Table Names Should Be Common Descriptive Terms;36
8.2.8;1.2.8 Metadata Schema Access Objects Can Have Names That Include Structure Information;37
8.3;1.3 Problems in Naming Data Elements;37
8.3.1;1.3.1 Avoid Vague Names;37
8.3.2;1.3.2 Avoid Names That Change from Place to Place;38
8.3.3;1.3.3 Do Not Use Proprietary Exposed Physical Locators;40
9;2. Fonts, Punctuation, and Spacing;42
9.1;2.1 Typography and Code;42
9.1.1;2.1.1 Use Only Upper- and Lowercase Letters, Digits, and Underscores for Names;44
9.1.2;2.1.2 Lowercase Scalars Such as Column Names, Parameters, and Variables;44
9.1.3;2.1.3 Capitalize Schema Object Names;45
9.1.4;2.1.4 Uppercase the Reserved Words;45
9.1.5;2.1.5 Avoid the Use of CamelCase;48
9.2;2.2 Word Spacing;49
9.3;2.3 Follow Normal Punctuation Rules;50
9.4;2.4 Use Full Reserved Words;52
9.5;2.5 Avoid Proprietary Reserved Words if a Standard Keyword Is Available in Your SQL Product;52
9.6;2.6 Avoid Proprietary Statements if a Standard Statement Is Available;53
9.7;2.7 Rivers and Vertical Spacing;56
9.8;2.8 Indentation;57
9.9;2.9 Use Line Spacing to Group Statements;58
10;3. Data Declaration Language;60
10.1;3.1 Put the Default in the Right Place;60
10.2;3.2 The Default Value Should Be the Same Data Type as the Column;61
10.3;3.3 Do Not Use Proprietary Data Types;61
10.4;3.4 Place the PRIMARY KEY Declaration at the Start of the CREATE TABLE Statement;63
10.5;3.5 Order the Columns in a Logical Sequence and Cluster Them in Logical Groups;63
10.6;3.6 Indent Referential Constraints and Actions under the Data Type;64
10.7;3.7 Give Constraints Names in the Production Code;65
10.8;3.8 Put CHECK() Constraint Near what they Check;65
10.8.1;3.8.1 Consider Range Constraints for Numeric Values;66
10.8.2;3.8.2 Consider LIKE and SIMILAR TO Constraints for Character Values;66
10.8.3;3.8.3 Remember That Temporal Values Have Duration;67
10.8.4;3.8.4 REAL and FLOAT Data Types Should Be Avoided;67
10.9;3.9 Put Multiple Column Constraints as Near to Both Columns as Possible;67
10.10;3.10 Put Table-Level CHECK() Constraints at the End of the Table Declaration;68
10.11;3.11 Use CREATE ASSERTION for Multi-table Constraints;68
10.12;3.12 Keep CHECK() Constraints Single Purposed;69
10.13;3.13 Every Table Must Have a Key to Be a Table;70
10.13.1;3.13.1 Auto-Numbers Are Not Relational Keys;71
10.13.2;3.13.2 Files Are Not Tables;72
10.13.3;3.13.3 Look for the Properties of a Good Key;73
10.14;3.14 Do Not Split Attributes;81
10.14.1;3.14.1 Split into Tables;82
10.14.2;3.14.2 Split into Columns;82
10.14.3;3.14.3 Split into Rows;84
10.15;3.15 Do Not Use Object-Oriented Design for an RDBMS;85
10.15.1;3.15.1 A Table Is Not an Object Instance;85
10.15.2;3.15.2 Do Not Use EAV Design for an RDBMS;87
11;4. Scales and Measurements;88
11.1;4.1 Measurement Theory;88
11.1.1;4.1.1 Range and Granularity;90
11.1.2;4.1.2 Range;91
11.1.3;4.1.3 Granularity, Accuracy, and Precision;91
11.2;4.2 Types of Scales;92
11.2.1;4.2.1 Nominal Scales;92
11.2.2;4.2.2 Categorical Scales;92
11.2.3;4.2.3 Absolute Scales;93
11.2.4;4.2.4 Ordinal Scales;93
11.2.5;4.2.5 Rank Scales;94
11.2.6;4.2.6 Interval Scales;95
11.2.7;4.2.7 Ratio Scales;95
11.3;4.3 Using Scales;96
11.4;4.4 Scale Conversion;96
11.5;4.5 Derived Units;98
11.6;4.6 Punctuation and Standard Units;99
11.7;4.7 General Guidelines for Using Scales in a Database;100
12;5. Data Encoding Schemes;102
12.1;5.1 Bad Encoding Schemes;103
12.2;5.2 Encoding Scheme Types;105
12.2.1;5.2.1 Enumeration Encoding;105
12.2.2;5.2.2 Measurement Encoding;106
12.2.3;5.2.3 Abbreviation Encoding;106
12.2.4;5.2.4 Algorithmic Encoding;107
12.2.5;5.2.5 Hierarchical Encoding Schemes;108
12.2.6;5.2.6 Vector Encoding;109
12.2.7;5.2.7 Concatenation Encoding;110
12.3;5.3 General Guidelines for Designing Encoding Schemes;111
12.3.1;5.3.1 Existing Encoding Standards;111
12.3.2;5.3.2 Allow for Expansion;111
12.3.3;5.3.3 Use Explicit Missing Values to Avoid NULLs;111
12.3.4;5.3.4 Translate Codes for the End User;112
12.3.5;5.3.5 Keep the Codes in the Database;115
12.4;5.4 Multiple Character Sets;116
13;6. Coding Choices;118
13.1;6.1 Pick Standard Constructions over Proprietary Constructions;119
13.1.1;6.1.1 Use Standard OUTER JOIN Syntax;120
13.1.2;6.1.2 Infixed INNER JOIN and CROSS JOIN Syntax Is Optional, but Nice;124
13.1.3;6.1.3 Use ISO Temporal Syntax;126
13.1.4;6.1.4 Use Standard and Portable Functions;127
13.2;6.2 Pick Compact Constructions over Longer Equivalents;128
13.2.1;6.2.1 Avoid Extra Parentheses;128
13.2.2;6.2.2 Use CASE Family Expressions;129
13.2.3;6.2.3 Avoid Redundant Expressions;132
13.2.4;6.2.4 Seek a Compact Form;133
13.3;6.3 Use Comments;137
13.3.1;6.3.1 Stored Procedures;138
13.3.2;6.3.2 Control Statement Comments;138
13.3.3;6.3.3 Comments on Clause;138
13.4;6.4 Avoid Optimizer Hints;139
13.5;6.5 Avoid Triggers in Favor of DRI Actions;139
13.6;6.6 Use SQL Stored Procedures;141
13.7;6.7 Avoid User-Defined Functions and Extensions inside the Database;142
13.7.1;6.7.1 Multiple Language Problems;143
13.7.2;6.7.2 Portability Problems;143
13.7.3;6.7.3 Optimization Problems;143
13.8;6.8 Avoid Excessive Secondary Indexes;143
13.9;6.9 Avoid Correlated Subqueries;144
13.10;6.10 Avoid UNIONs;146
13.11;6.11 Testing SQL;149
13.11.1;6.11.1 Test All Possible Combinations of NULLs;149
13.11.2;6.11.2 Inspect and Test All CHECK() Constraints;149
13.11.3;6.11.3 Beware of Character Columns;150
13.11.4;6.11.4 Test for Size;150
14;7. How to Use VIEWS;152
14.1;7.1 VIEW Naming Conventions Are the Same as Tables;154
14.1.1;7.1.1 Always Specify Column Names;155
14.2;7.2 VIEWs Provide Row- and Column-Level Security;155
14.3;7.3 VIEWs Ensure Efficient Access Paths;157
14.4;7.4 VIEWs Mask Complexity from the User;157
14.5;7.5 VIEWs Ensure Proper Data Derivation;158
14.6;7.6 VIEWs Rename Tables and/or Columns;159
14.7;7.7 VIEWs Enforce Complicated Integrity Constraints;159
14.8;7.8 Updatable VIEWs;162
14.8.1;7.8.1 WITH CHECK OPTION clause;162
14.8.2;7.8.2 INSTEAD OF Triggers;163
14.9;7.9 Have a Reason for Each VIEW;163
14.10;7.10 Avoid VIEW Proliferation;164
14.11;7.11 Synchronize VIEWs with Base Tables;164
14.12;7.12 Improper Use of VIEWs;165
14.12.1;7.12.1 VIEWs for Domain Support;165
14.12.2;7.12.2 Single-Solution VIEWs;166
14.12.3;7.12.3 Do Not Create One VIEW Per Base Table;167
14.13;7.13 Learn about Materialized VIEWs;168
15;8. How to Write Stored Procedures;170
15.1;8.1 Most SQL 4GLs Are Not for Applications;171
15.2;8.2 Basic Software Engineering;172
15.2.1;8.2.1 Cohesion;172
15.2.2;8.2.2 Coupling;174
15.3;8.3 Use Classic Structured Programming;175
15.3.1;8.3.1 Cyclomatic Complexity;176
15.4;8.4 Avoid Portability Problems;177
15.4.1;8.4.1 Avoid Creating Temporary Tables;177
15.4.2;8.4.2 Avoid Using Cursors;178
15.4.3;8.4.3 Prefer Set-Oriented Constructs to Procedural Code;180
15.5;8.5 Scalar versus Structured Parameters;186
15.6;8.6 Avoid Dynamic SQL;187
15.6.1;8.6.1 Performance;188
15.6.2;8.6.2 SQL Injection;188
16;9. Heuristics;190
16.1;9.1 Put the Specification into a Clear Statement;191
16.2;9.2 Add the Words "Set of All..." in Front of the Nouns;192
16.3;9.3 Remove Active Verbs from the Problem Statement;193
16.4;9.4 You Can Still Use Stubs;193
16.5;9.5 Do Not Worry about Displaying the Data;195
16.6;9.6 Your First Attempts Need Special Handling;196
16.6.1;9.6.1 Do Not Be Afraid to Throw Away Your First Attempts at DDL;196
16.6.2;9.6.2 Save Your First Attempts at DML;197
16.7;9.7 Do Not Think with Boxes and Arrows;198
16.8;9.8 Draw Circles and Set Diagrams;198
16.9;9.9 Learn Your Dialect;199
16.10;9.10 Imagine That Your WHERE Clause Is ÏSuper AmebaÓ;199
16.11;9.11 Use the Newsgroups and Internet;200
17;10. Thinking in SQL;202
17.1;10.1 Bad Programming in SQL and Procedural Languages;203
17.2;10.2 Thinking of Columns as Fields;208
17.3;10.3 Thinking in Processes, Not Declarations;210
17.4;10.4 Thinking the Schema Should Look Like the Input Forms;213
18;back matter;216
19;Appendix. Resources;216
19.1;Military Standards;216
19.2;Metadata Standards;216
19.3;ANSI and ISO Standards;217
19.4;U.S. Government Codes;218
19.5;Retail Industry;218
19.6;Code Formatting and Naming Conventions;219
20;Appendix. Bibliography;222
20.1;Reading Psychology;222
20.2;Programming Considerations;223
21;Index;226
22;About the Author;236