Joe Celko's SQL Programming Style
Regular price
16.250 KD
inc. VAT
Couldn't load pickup availability
Table of contents
- Cover
- Table of contentsvii
- Introductionxv
- 1.1 Purpose of the Bookxvi
- 1.2 Acknowledgmentsxvii
- 1.3 Corrections, Comments, and Future Editionsxviii
- 1. Names and Data Elements1
- 1.1 Names2
- 1.1.1 Watch the Length of Names2
- 1.1.2 Avoid All Special Characters in Names3
- 1.1.3 Avoid Quoted Identifiers4
- 1.1.4 Enforce Capitalization Rules to Avoid Case- Sensitivity Problems6
- 1.2 Follow the ISO-11179 Standards Naming Conventions7
- 1.2.1 ISO-11179 for SQL8
- 1.2.2 Levels of Abstraction9
- 1.2.3 Avoid Descriptive Prefixes10
- 1.2.4 Develop Standardized Postfixes12
- 1.2.5 Table and View Names Should Be Industry Standards, Collective, Class, or Plural Nouns14
- 1.2.6 Correlation Names Follow the Same Rules as Other Names . . . Almost15
- 1.2.7 Relationship Table Names Should Be Common Descriptive Terms17
- 1.2.8 Metadata Schema Access Objects Can Have Names That Include Structure Information18
- 1.3 Problems in Naming Data Elements18
- 1.3.1 Avoid Vague Names18
- 1.3.2 Avoid Names That Change from Place to Place19
- 1.3.3 Do Not Use Proprietary Exposed Physical Locators21
- 2. Fonts, Punctuation, and Spacing23
- 2.1 Typography and Code23
- 2.1.1 Use Only Upper- and Lowercase Letters, Digits, and Underscores for Names25
- 2.1.2 Lowercase Scalars Such as Column Names, Parameters, and Variables25
- 2.1.3 Capitalize Schema Object Names26
- 2.1.4 Uppercase the Reserved Words26
- 2.1.5 Avoid the Use of CamelCase29
- 2.2 Word Spacing30
- 2.3 Follow Normal Punctuation Rules31
- 2.4 Use Full Reserved Words33
- 2.5 Avoid Proprietary Reserved Words if a Standard Keyword Is Available in Your SQL Product33
- 2.6 Avoid Proprietary Statements if a Standard Statement Is Available34
- 2.7 Rivers and Vertical Spacing37
- 2.8 Indentation38
- 2.9 Use Line Spacing to Group Statements39
- 3. Data Declaration Language41
- 3.1 Put the Default in the Right Place41
- 3.2 The Default Value Should Be the Same Data Type as the Column42
- 3.3 Do Not Use Proprietary Data Types42
- 3.4 Place the PRIMARY KEY Declaration at the Start of the CREATE TABLE Statement44
- 3.5 Order the Columns in a Logical Sequence and Cluster Them in Logical Groups44
- 3.6 Indent Referential Constraints and Actions under the Data Type45
- 3.7 Give Constraints Names in the Production Code46
- 3.8 Put CHECK() Constraint Near what they Check46
- 3.8.1 Consider Range Constraints for Numeric Values47
- 3.8.2 Consider LIKE and SIMILAR TO Constraints for Character Values47
- 3.8.3 Remember That Temporal Values Have Duration48
- 3.8.4 REAL and FLOAT Data Types Should Be Avoided48
- 3.9 Put Multiple Column Constraints as Near to Both Columns as Possible48
- 3.10 Put Table-Level CHECK() Constraints at the End of the Table Declaration49
- 3.11 Use CREATE ASSERTION for Multi-table Constraints49
- 3.12 Keep CHECK() Constraints Single Purposed50
- 3.13 Every Table Must Have a Key to Be a Table51
- 3.13.1 Auto-Numbers Are Not Relational Keys52
- 3.13.2 Files Are Not Tables53
- 3.13.3 Look for the Properties of a Good Key54
- 3.14 Do Not Split Attributes62
- 3.14.1 Split into Tables63
- 3.14.2 Split into Columns63
- 3.14.3 Split into Rows65
- 3.15 Do Not Use Object-Oriented Design for an RDBMS66
- 3.15.1 A Table Is Not an Object Instance66
- 3.15.2 Do Not Use EAV Design for an RDBMS68
- 4. Scales and Measurements69
- 4.1 Measurement Theory69
- 4.1.1 Range and Granularity71
- 4.1.2 Range72
- 4.1.3 Granularity, Accuracy, and Precision72
- 4.2 Types of Scales73
- 4.2.1 Nominal Scales73
- 4.2.2 Categorical Scales73
- 4.2.3 Absolute Scales74
- 4.2.4 Ordinal Scales74
- 4.2.5 Rank Scales75
- 4.2.6 Interval Scales76
- 4.2.7 Ratio Scales76
- 4.3 Using Scales77
- 4.4 Scale Conversion77
- 4.5 Derived Units79
- 4.6 Punctuation and Standard Units80
- 4.7 General Guidelines for Using Scales in a Database81
- 5. Data Encoding Schemes83
- 5.1 Bad Encoding Schemes84
- 5.2 Encoding Scheme Types86
- 5.2.1 Enumeration Encoding86
- 5.2.2 Measurement Encoding87
- 5.2.3 Abbreviation Encoding87
- 5.2.4 Algorithmic Encoding88
- 5.2.5 Hierarchical Encoding Schemes89
- 5.2.6 Vector Encoding90
- 5.2.7 Concatenation Encoding91
- 5.3 General Guidelines for Designing Encoding Schemes92
- 5.3.1 Existing Encoding Standards92
- 5.3.2 Allow for Expansion92
- 5.3.3 Use Explicit Missing Values to Avoid NULLs92
- 5.3.4 Translate Codes for the End User93
- 5.3.5 Keep the Codes in the Database96
- 5.4 Multiple Character Sets97
- 6. Coding Choices99
- 6.1 Pick Standard Constructions over Proprietary Constructions100
- 6.1.1 Use Standard OUTER JOIN Syntax101
- 6.1.2 Infixed INNER JOIN and CROSS JOIN Syntax Is Optional, but Nice105
- 6.1.3 Use ISO Temporal Syntax107
- 6.1.4 Use Standard and Portable Functions108
- 6.2 Pick Compact Constructions over Longer Equivalents109
- 6.2.1 Avoid Extra Parentheses109
- 6.2.2 Use CASE Family Expressions110
- 6.2.3 Avoid Redundant Expressions113
- 6.2.4 Seek a Compact Form114
- 6.3 Use Comments118
- 6.3.1 Stored Procedures119
- 6.3.2 Control Statement Comments119
- 6.3.3 Comments on Clause119
- 6.4 Avoid Optimizer Hints120
- 6.5 Avoid Triggers in Favor of DRI Actions120
- 6.6 Use SQL Stored Procedures122
- 6.7 Avoid User-Defined Functions and Extensions inside the Database123
- 6.7.1 Multiple Language Problems124
- 6.7.2 Portability Problems124
- 6.7.3 Optimization Problems124
- 6.8 Avoid Excessive Secondary Indexes124
- 6.9 Avoid Correlated Subqueries125
- 6.10 Avoid UNIONs127
- 6.11 Testing SQL130
- 6.11.1 Test All Possible Combinations of NULLs130
- 6.11.2 Inspect and Test All CHECK() Constraints130
- 6.11.3 Beware of Character Columns131
- 6.11.4 Test for Size131
- 7. How to Use VIEWS133
- 7.1 VIEW Naming Conventions Are the Same as Tables135
- 7.1.1 Always Specify Column Names136
- 7.2 VIEWs Provide Row- and Column-Level Security136
- 7.3 VIEWs Ensure Efficient Access Paths138
- 7.4 VIEWs Mask Complexity from the User138
- 7.5 VIEWs Ensure Proper Data Derivation139
- 7.6 VIEWs Rename Tables and/or Columns140
- 7.7 VIEWs Enforce Complicated Integrity Constraints140
- 7.8 Updatable VIEWs143
- 7.8.1 WITH CHECK OPTION clause143
- 7.8.2 INSTEAD OF Triggers144
- 7.9 Have a Reason for Each VIEW144
- 7.10 Avoid VIEW Proliferation145
- 7.11 Synchronize VIEWs with Base Tables145
- 7.12 Improper Use of VIEWs146
- 7.12.1 VIEWs for Domain Support146
- 7.12.2 Single-Solution VIEWs147
- 7.12.3 Do Not Create One VIEW Per Base Table148
- 7.13 Learn about Materialized VIEWs149
- 8. How to Write Stored Procedures151
- 8.1 Most SQL 4GLs Are Not for Applications152
- 8.2 Basic Software Engineering153
- 8.2.1 Cohesion153
- 8.2.2 Coupling155
- 8.3 Use Classic Structured Programming156
- 8.3.1 Cyclomatic Complexity157
- 8.4 Avoid Portability Problems158
- 8.4.1 Avoid Creating Temporary Tables158
- 8.4.2 Avoid Using Cursors159
- 8.4.3 Prefer Set-Oriented Constructs to Procedural Code161
- 8.5 Scalar versus Structured Parameters167
- 8.6 Avoid Dynamic SQL168
- 8.6.1 Performance169
- 8.6.2 SQL Injection169
- 9. Heuristics171
- 9.1 Put the Specification into a Clear Statement172
- 9.2 Add the Words "Set of All..." in Front of the Nouns173
- 9.3 Remove Active Verbs from the Problem Statement174
- 9.4 You Can Still Use Stubs174
- 9.5 Do Not Worry about Displaying the Data176
- 9.6 Your First Attempts Need Special Handling177
- 9.6.1 Do Not Be Afraid to Throw Away Your First Attempts at DDL177
- 9.6.2 Save Your First Attempts at DML178
- 9.7 Do Not Think with Boxes and Arrows179
- 9.8 Draw Circles and Set Diagrams179
- 9.9 Learn Your Dialect180
- 9.10 Imagine That Your WHERE Clause Is ÏSuper AmebaÓ180
- 9.11 Use the Newsgroups and Internet181
- 10. Thinking in SQL183
- 10.1 Bad Programming in SQL and Procedural Languages184
- 10.2 Thinking of Columns as Fields189
- 10.3 Thinking in Processes, Not Declarations191
- 10.4 Thinking the Schema Should Look Like the Input Forms194
- Appendix. Resources197
- Military Standards197
- Metadata Standards197
- ANSI and ISO Standards198
- U.S. Government Codes199
- Retail Industry199
- Code Formatting and Naming Conventions200
- Appendix. Bibliography203
- Reading Psychology203
- Programming Considerations204
- Index207
- About the Author217
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!
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!