Effective SQL. 61 Specific Ways

Description

Contents......Page 3 Foreword......Page 7 Intro......Page 9 Item 1: Verify That All Tables Have a Primary Key......Page 18 Item 2: Eliminate Redundant Storage of Data Items......Page 22 Item 3: Get Rid of Repeating Groups......Page 26 Item 4: Store Only One Property per Column......Page 28 Item 5: Understand Why Storing Calculated Data Is Usually a Bad Idea......Page 32 Item 6: Define Foreign Keys to Protect Referential Integrity......Page 37 Item 7: Be Sure Your Table Relationships Make Sense......Page 40 Item 8: When 3NF Is Not Enough, Normalize More......Page 44 Item 9: Use Denormalization for Information Warehouses......Page 50 Item 10: Factor in Nulls When Creating Indexes......Page 54 Item 11: Carefully Consider Creation of Indexes to Minimize Index and Data Scanning......Page 59 Item 12: Use Indexes for More than Just Filtering......Page 63 Item 13: Don’t Go Overboard with Triggers......Page 68 Item 14: Consider Using a Filtered Index to Include or Exclude a Subset of Data......Page 72 Item 15: Use Declarative Constraints Instead of Programming Checks......Page 75 Item 16: Know Which SQL Dialect Your Product Uses and Write Accordingly......Page 77 Item 17: Know When to Use Calculated Results in Indexes......Page 81 Item 18: Use Views to Simplify What Cannot Be Changed......Page 85 Item 19: Use ETL to Turn Nonrelational Data into Information......Page 91 Item 20: Create Summary Tables and Maintain Them......Page 96 Statements to “Unpivot” Non-normalized Data......Page 100 Item 22: Understand Relational Algebra and How It Is Implemented in SQL......Page 106 Item 23: Find Non-matches or Missing Records......Page 113 to Solve a Problem......Page 115 Item 25: Know Techniques to Solve Multiple-Criteria Problems......Page 120 Item 26: Divide Your Data If You Need a Perfect Match......Page 125 Item 27: Know How to Correctly Filter a Range of Dates on a Column Containing Both Date and Time......Page 129 Item 28: Write Sargable Queries to Ensure That the Engine Will Use Indexes......Page 132 Item 29: Correctly Filter the “Right” Side of a “Left” Join......Page 137 Works......Page 140 Clause Small......Page 147 to Solve Complex Problems......Page 150 Item 33: Find Maximum or Minimum Values Without Using......Page 155 When Using......Page 161 Some Number......Page 164 to Get Distinct Counts......Page 168 Item 37: Know How to Use Window Functions......Page 171 Item 38: Create Row Numbers and Rank a Row over Other Rows......Page 174 Item 39: Create a Moving Aggregate......Page 177 Item 40: Know Where You Can Use Subqueries......Page 183 Item 41: Know the Difference between Correlated and Non-correlated Subqueries......Page 188 Item 42: If Possible, Use Common Table Expressions Instead of Subqueries......Page 194 Item 43: Create More Efficient Queries Using Joins Rather than Subqueries......Page 201 Item 44: Learn to Use Your System’s Query Analyzer......Page 204 Item 45: Learn to Get Metadata about Your Database......Page 215 Item 46: Understand How the Execution Plan Works......Page 220 Item 47: Produce Combinations of Rows between Two Tables and Flag Rows in the Second That Indirectly Relate to the First......Page 229 Item 48: Understand How to Rank Rows by Equal Quantiles......Page 233 Item 49: Know How to Pair Rows in a Table with All Other Rows......Page 237 Item 50: Understand How to List Categories and the Count of First, Second, or Third Preferences......Page 242 Item 51: Use a Tally Table to Generate Null Rows Based on a Parameter......Page 248 Item 52: Use a Tally Table and Window Functions for Sequencing......Page 253 Item 53: Generate Multiple Rows Based on Range Values in a Tally Table......Page 258 Item 54: Convert a Value in One Table Based on a Range of Values in a Tally Table......Page 262 Item 55: Use a Date Table to Simplify Date Calculation......Page 269 Item 56: Create an Appointment Calendar Table with All Dates Enumerated in a Range......Page 276 Item 57: Pivot Data Using a Tally Table......Page 279 Modeling Hierarchical Data......Page 285 Item 58: Use an Adjacency List Model as the Starting Point......Page 286 Item 59: Use Nested Sets for Fast Querying Performance with Infrequent Updates......Page 288 Item 60: Use a Materialized Path for Simple Setup and Limited Searching......Page 291 Item 61: Use Ancestry Traversal Closure for Complex Searching......Page 294 IBM DB2......Page 299 Microsoft Access......Page 303 Microsoft SQL Server......Page 305 MySQL......Page 308 Oracle......Page 313 PostgreSQL......Page 315 Index......Page 317