Troubleshooting SQL

by Forrest Houlette (Conducted by)

Overview

Explains optimizing SQL statements and procedures and creating a customized SQL debugging environment for error-free applications. This work includes programming tips and coverage of Oracle SQL and PL/SQL and T-SQL for SQL Server.

Product Details

ISBN-13:	9780072134896
Publisher:	McGraw-Hill Companies, The
Publication date:	09/01/2001
Series:	Application Development
Pages:	400
Product dimensions:	7.51(w) x 9.39(h) x 0.82(d)

Table of Contents

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