| Laying the Groundwork | |
| Choosing a Troubleshooting Environment | p. 3 |
| Query Analyzers | p. 8 |
| Graphical Tools | p. 12 |
| Development Environments | p. 16 |
| Facing Reality | p. 17 |
| Summary | p. 19 |
| Using Best Practices | p. 21 |
| A Guided Tour of Best Practices | p. 24 |
| Make Your Code Self-Documenting | p. 24 |
| Make Your Code Readable | p. 36 |
| Undertake All Actions Explicitly | p. 38 |
| Include Narrative Comments | p. 40 |
| Stick to Supported Options | p. 40 |
| Optimizing Queries | p. 41 |
| Protecting Data | p. 44 |
| Protecting Data Integrity | p. 45 |
| Summary | p. 47 |
| Preparing Your Troubleshooting Environment | p. 49 |
| What to Have Handy | p. 50 |
| What to Make Ready | p. 53 |
| What to Set Up | p. 54 |
| What to Run | p. 55 |
| How to Run Your Environment | p. 57 |
| Summary | p. 58 |
| Analyzing Problems | |
| Creating Databases | p. 61 |
| Troubleshooting Setup Problems | p. 62 |
| Database Objects and User Relationships | p. 65 |
| Organizing Tables | p. 68 |
| Putting the Database on Disk | p. 78 |
| Protecting Response Time | p. 82 |
| Creating Indexes | p. 82 |
| Archiving Data | p. 84 |
| Managing Logs and Files | p. 85 |
| Securing the Data | p. 88 |
| Defining Users | p. 90 |
| Defining Roles | p. 94 |
| Summary | p. 97 |
| Normalizing Tables | p. 99 |
| Thirteen Rules | p. 101 |
| First Normal Form | p. 105 |
| Further Normal Forms | p. 108 |
| Second Normal Form | p. 109 |
| Third Normal Form | p. 110 |
| Boyce-Codd Normal Form | p. 111 |
| Fourth Normal Form | p. 112 |
| Fifth Normal Form and Beyond | p. 113 |
| Optimizing Tables | p. 115 |
| Normalizing to Optimize | p. 115 |
| Considering Denormalization | p. 116 |
| Summary | p. 116 |
| Using Data Types | p. 117 |
| Using Data Types | p. 118 |
| Numeric Data Types | p. 120 |
| Time-Related Data Types | p. 123 |
| Character Data Types | p. 126 |
| Converting Data Types from One Database to Another | p. 129 |
| Summary | p. 131 |
| Selecting Data | p. 133 |
| The Basic SELECT Statement | p. 135 |
| Aggregates as Complicating Factors | p. 136 |
| Joins as Complicating Factors | p. 139 |
| WHERE Clauses as Complicating Factors | p. 141 |
| A Look at a Complex Query | p. 142 |
| A Few Practical Suggestions | p. 147 |
| Summary | p. 148 |
| Inserting Data | p. 149 |
| The Basic INSERT Statement | p. 151 |
| Inserting into Multiple Tables | p. 152 |
| Common Complicating Factors | p. 154 |
| Database Design | p. 154 |
| Constraints | p. 156 |
| Null Values | p. 158 |
| Missing Values | p. 159 |
| Multiple Values | p. 159 |
| Examining a Complex INSERT Statement | p. 160 |
| A Few Practical Suggestions | p. 167 |
| Summary | p. 167 |
| Updating Data | p. 169 |
| Transactional Integrity | p. 170 |
| The ACID Test | p. 171 |
| Types of Locks | p. 174 |
| Lock Granularity | p. 175 |
| Optimistic and Pessimistic Locking | p. 176 |
| Making Optimistic Locking Work | p. 176 |
| Deadlocks | p. 178 |
| Transaction Suggestions | p. 180 |
| The Basic UPDATE Statement | p. 180 |
| WHERE Is Your Best Friend | p. 181 |
| Using the FROM Clause | p. 181 |
| Updating to Calculated Values | p. 182 |
| Things That Prevent Updates | p. 183 |
| Undocumented Schemas | p. 184 |
| Data Type Incompatibility | p. 184 |
| Unique Primary Key Constraints | p. 185 |
| Foreign Key Constraints | p. 187 |
| Unique Index Constraints | p. 187 |
| Allow Nulls and Defaults | p. 187 |
| Check Constraints | p. 188 |
| Triggers | p. 188 |
| Views, with Check | p. 189 |
| Security Settings | p. 189 |
| Summary | p. 190 |
| Deleting Data | p. 191 |
| The Basic DELETE Statement | p. 192 |
| Just in Case | p. 192 |
| The Data Saving WHERE Clause | p. 193 |
| FROM-FROM | p. 193 |
| Things That Prevent Deletions | p. 194 |
| Referential Integrity | p. 195 |
| Optional Foreign Keys | p. 195 |
| Cascade Deletes | p. 196 |
| Cascade Delete Triggers | p. 197 |
| Logical Deletion | p. 198 |
| Logical Delete Flag | p. 198 |
| Logical Delete Triggers | p. 199 |
| Cascading Logical Deletes | p. 201 |
| Truncating a Table | p. 202 |
| Summary | p. 202 |
| Grouping and Aggregating Data | p. 203 |
| Common Aggregate Functions | p. 205 |
| The COUNT() Function | p. 205 |
| The SUM() Function | p. 208 |
| The AVG() Function | p. 209 |
| The MIN() and MAX() Functions | p. 209 |
| GROUP BY | p. 210 |
| Cleaning Up a GROUP BY Query | p. 211 |
| The SQL Order with Aggregates | p. 213 |
| Generating Cube Subtotals | p. 214 |
| Recap | p. 215 |
| Summary | p. 216 |
| Using Joins | p. 217 |
| The Join Within the SQL Statement | p. 219 |
| Inner Joins | p. 219 |
| Changes in the Resulting Row Count | p. 219 |
| Working with Graphic Query Tools | p. 220 |
| Execution Order of the SQL Statement | p. 221 |
| Self Joins | p. 222 |
| Outer Joins | p. 225 |
| Right Outer Joins | p. 226 |
| Data Scrubbing with Nulls | p. 226 |
| Full Outer Joins | p. 227 |
| An Eighteenth-Century Analogy | p. 227 |
| Legacy Joins | p. 228 |
| Cross Joins | p. 229 |
| Union Joins | p. 229 |
| Complex Joins | p. 230 |
| Multiple Tables | p. 230 |
| Multiple Join Conditions | p. 231 |
| Non-Equal Join | p. 231 |
| A Readable Style | p. 232 |
| Summary | p. 232 |
| Using Subqueries | p. 233 |
| Subquery Basics | p. 234 |
| Substituting Subqueries | p. 235 |
| Substituting a Column Name | p. 236 |
| Substituting a Column Value | p. 239 |
| Dynamically Setting the Top Row Count | p. 241 |
| Referencing a Derived Table | p. 242 |
| Building a Dynamic WHERE Clause | p. 243 |
| Altering the GROUP BY and ORDER BY | p. 245 |
| Correlated Subqueries | p. 246 |
| Summary | p. 247 |
| Using Views | p. 249 |
| Using Views | p. 251 |
| Views to Project Columns | p. 252 |
| Nesting Views | p. 252 |
| Partitioned Views and Federated Databases | p. 255 |
| Views as Security | p. 259 |
| Using the WITH CHECK Option | p. 259 |
| Problems with Views | p. 260 |
| Speaking of Locks, Updates, and Views | p. 260 |
| Performance | p. 261 |
| Views Are Often Nonupdatable | p. 262 |
| Schema Changes | p. 262 |
| Debugging Difficulties | p. 263 |
| Multiple Table References | p. 264 |
| Editing Views | p. 264 |
| Rebuilding Database Objects from Scripts | p. 265 |
| Summary | p. 265 |
| Solving Complex Problems | |
| Triggers, Stored Procedures, and Parameters | p. 269 |
| Why Use Triggers and Stored Procedures? | p. 271 |
| Eliminating Code Troubles with Triggers and Stored Procedures | p. 271 |
| Scenario | p. 272 |
| Option 1: Modifying the Code | p. 276 |
| Triggers | p. 277 |
| Option 2: Using a Trigger | p. 279 |
| Option 3: Using Stored Procedures | p. 279 |
| Syntax and Types of Stored Procedures | p. 282 |
| Debugging Stored Procedures | p. 284 |
| Using Parameters | p. 286 |
| What Are Parameters? | p. 286 |
| Parameter Fundamentals | p. 288 |
| Summary | p. 290 |
| Transactions | p. 291 |
| Transaction Processing Requirements | p. 293 |
| Atomic | p. 293 |
| Consistent | p. 293 |
| Isolated | p. 294 |
| Durable | p. 294 |
| Transaction Fundamentals | p. 294 |
| Database Locks | p. 295 |
| Understanding Locks | p. 295 |
| Using Locks | p. 297 |
| Using Transactions | p. 298 |
| Transactions in Oracle | p. 298 |
| Controlling Transactions | p. 301 |
| Transactions in Transact-SQL | p. 301 |
| Transactions and Stored Procedures | p. 302 |
| Monitoring Transactions: Using the Transaction Log | p. 303 |
| Summary | p. 304 |
| Using Cursors and Exceptions | p. 305 |
| Understanding Cursors | p. 306 |
| Creating and Using Cursors | p. 307 |
| Cursors for Transact-SQL | p. 308 |
| Creating SQL Cursors | p. 308 |
| Opening Cursors | p. 309 |
| Updating and Deleting Cursors | p. 311 |
| Closing Cursors | p. 312 |
| Understanding Cursors for PL/SQL | p. 313 |
| Declaring a Cursor | p. 313 |
| Exceptions in PL/SQL | p. 317 |
| Exception Types | p. 317 |
| Exception Handling | p. 319 |
| RAISE and RAISE_APPLICATION_ERROR Statements | p. 320 |
| Summary | p. 323 |
| Trees | p. 325 |
| Introducing Trees | p. 326 |
| Understanding Trees and Hierarchies | p. 327 |
| Rules for Trees | p. 332 |
| Limits of the CONNECT BY Clause | p. 333 |
| Extracting Information Within Trees | p. 335 |
| Tree Operations | p. 336 |
| Deleting a Subtree | p. 336 |
| Subtree Incorporation | p. 337 |
| Summary | p. 337 |
| Index | p. 339 |
| Table of Contents provided by Syndetics. All Rights Reserved. |
