Cover image for Microsoft SQL Server 2000 database development from scratch
Microsoft SQL Server 2000 database development from scratch
Hawthorne, Rob.
Personal Author:
Publication Information:
Indianapolis, Ind. : Que, [2001]

Physical Description:
xv, 476 pages : illustrations ; 23 cm + 1 computer optical disc (4 3/4 in.).
General Note:
Includes index.

CD includes Microsoft's SQL Server 2000 120-day Enterprise evaluation version.
Title Subject:
Format :


Call Number
Material Type
Home Location
Item Holds
QA76.9.D3 H42 2001 Book and Software Set Central Closed Stacks

On Order




Author Notes

Rob Hawthorne, MCSD, is a consultant for KPMG Consulting based in Wellington, New Zealand. KPMG Consulting deals in all aspects of E-Commerce, and is a leading provider of Internet Integration services around the globe. Rob is a specialist in Microsoft technologies. The web applications that Rob builds are mainly in Active Server Pages (ASP), VB (utilizing COM and MTS) over SQL Server 7.0/2000 databases. Consequently he has skills in front-end design, middle-tier and back-end architecture. Rob has a bachelor of commerce (BCom) from the University of Otago (also in New Zealand). He double majored in information science and operations management.

Table of Contents

Introductionp. 1
Chapter 1 Taking Spy Net from Idea to SQL Server Databasep. 11
A Case Study for Spy Net Limitedp. 11
An Overview of the SQL Spy Net Applicationp. 12
Determining What the Application Should Dop. 13
What Are We Going to Achieve?p. 14
Modeling the SQL Spy Net Applicationp. 14
Designing the Spy Net Tablesp. 15
Using Relational Theory to Model Spy Net's Applicationp. 16
Building Relationships Between Enemiesp. 22
Taking a Broad Overview of Spy Netp. 26
Summaryp. 27
Next Stepsp. 27
Chapter 2 The Tools of SQL Server 2000 for Managing an Instancep. 29
Exploring Database Objects with Enterprise Managerp. 30
Why You're Sure to Love Enterprise Managerp. 31
How This Tool Fits into Our SQL Spy Net Applicationp. 31
Getting the Answers with Query Analyzerp. 32
What You Can Do with Query Analyzerp. 35
Using This Tool for the SQL Spy Net Applicationp. 35
Tracking Code Crimes with Profilerp. 36
When You'll Use Profilerp. 37
Profiling and the SQL Spy Net Applicationp. 38
Importing and Exporting Data the Easy Wayp. 38
Why You Should Use DTSp. 40
Generating SQL Spy Net Data with DTSp. 40
Checking Out SQL Server 2000's Other Toolsp. 40
Summaryp. 41
Next Stepsp. 42
Chapter 3 Making Virtual Spies--Creating Spy Net in SQL Server 2000p. 43
Playing Program Director (Sys Admin) for SpyNetp. 43
Setting the sa Passwordp. 44
Configuring the Model Database to Meet Our SQL Spy Net Application Requirementsp. 47
Creating the SQL Spy Net Application Databasep. 57
Using Enterprise Manager to Create Our Application Databasep. 58
Using the Data Definition Language (DDL) to Create Databases and Objectsp. 64
Building Tables for Our Spiesp. 66
Revisiting the Analysis of the SQL Spy Net Applicationp. 68
Bringing Our Data Model and Database Togetherp. 70
Developing the First Database Table for the SQL Spy Net Applicationp. 71
Creating Tables the Fun Way--With Codep. 85
Filling in the Blanksp. 94
Summaryp. 99
Next Stepsp. 100
Chapter 4 Manipulating Data with Transact-SQLp. 101
What Is This SELECT Statement Then?p. 101
The First Building Block for SELECTp. 103
Where Does the Data Come FROM?p. 104
Limiting Data with WHEREp. 106
Put Those Spies in Order!p. 109
Let's Get Some Data in with the INSERT Statementp. 110
Defining the Table and Columns We Want to Insert Intop. 110
Let's Put In Some New Data!p. 111
Keeping Everything Up-to-Datep. 113
Which Table, Please?p. 114
Correcting Data to Fix Data Problemsp. 115
WHERE to Put the New Valuep. 116
When a Good Spy Dies...DELETE 'emp. 117
What Else Can We Do with Transact-SQL Besides DMLs and DDLs?p. 119
Declaring Variablesp. 120
Assigning Values to Our Variables with the SET Statementp. 120
The IF Test--Is What We Have in Our Variable What We Expect?p. 121
Using WHILE Loopsp. 124
Summaryp. 127
Next Stepsp. 127
Chapter 5 Pushing the Boundaries of Our DDL Knowledge to View and Update Datap. 129
Adding Information About Our Spiesp. 129
Finding a Good Viewp. 131
Getting Your First Look at a Viewp. 132
Looking at JOINsp. 134
Comparing Views to Tablesp. 137
Restrictions on Viewsp. 138
Streamlining Updates with Stored Proceduresp. 138
Creating the Stored Procedure Codep. 140
Checking the Table for Places to Fillp. 143
Letting the Stored Procedure Do the Workp. 144
Creating the Stored Procedure to Insert into Both the Person and BadGuy Tablesp. 145
Checking For and Filling In NULLsp. 146
Guidelines for Using Stored Proceduresp. 147
Pulling Triggers to Catch Eventsp. 148
Creating a Trigger to Catch Double Spiesp. 149
Testing the Triggerp. 152
Setting a Trigger on the Spy Tablep. 153
Dropping Objects All Over the Placep. 155
Going Round and Round Using Cursorsp. 156
Summaryp. 157
Next Stepsp. 157
Chapter 6 Getting Clearer Results with Functionsp. 159
Understanding the Role of Functionsp. 159
Using Built-In Functionsp. 161
Using CONVERT to "Make Money"p. 161
COUNT-ing Spiesp. 165
Using the SUM Function to Tally Paychecksp. 166
Putting STUFF into Stringsp. 168
Creating Your Own Functions to Manipulate Datap. 169
Tweaking Dates from Around the Globep. 170
Executing a User-Defined Functionp. 171
Building Your Function Libraryp. 172
Scalar Functionsp. 173
Aggregate and Rowset Functionsp. 173
Summaryp. 174
Next Stepsp. 174
Chapter 7 Securing Data Entry with Custom Rules and Defaultsp. 175
Introductionp. 175
Ruling Your Data Worldp. 176
Checking for Underaged Spiesp. 176
Binding and Testing the Rulep. 177
Setting Default Valuesp. 179
Filling in the Missing Data for Our Usersp. 180
Testing the Defaultp. 181
Adding Custom Data Types to Keep Data Uniformp. 183
Validating Phone Numbersp. 184
Using the Data Typep. 187
Summaryp. 189
Next Stepsp. 190
Chapter 8 Ensuring Data Consistency with Transactions, Locks, and Error Trappingp. 191
Keeping Data Consistent with Transaction Wrappersp. 192
Making Transactions Pass the ACID Testp. 194
Choosing a Transaction Typep. 195
Creating a Transaction for Spy Netp. 197
Getting the Most from Your Transactionsp. 199
Maintaining Data Integrity with Locksp. 200
How SQL Server 2000 Automates Lockingp. 200
Developing a Locking Strategyp. 202
Locking Considerationsp. 206
Handling Errors So Our Application Doesn't Breakp. 206
What Is an Error Composed of in SQL Server 2000?p. 207
Catching an Errorp. 209
Making Errors Work for Youp. 209
Changing Our Stored Procedure to Implement Error Trappingp. 210
Summaryp. 212
Next Stepsp. 213
Chapter 9 Implementing Security in Spy Netp. 215
Sharing Spy Net with Other Usersp. 216
Creating Logins to Spy Netp. 217
Logging In as SQL SpyNetUser to Test Permissionsp. 220
Assigning Roles for Our Usersp. 221
Assigning Roles to the SQL Server Instancep. 223
Creating a Role Modelp. 224
Exercising Our Rightsp. 231
Auditing: Yes, Big Brother Really Is Watching!p. 233
Basic Auditing in SQL Server 2000p. 234
Looking at C2 Auditingp. 235
Considerations for Developing a Security Strategyp. 236
Summaryp. 237
Next Stepsp. 237
Chapter 10 Ensuring Data Availabilityp. 239
Creating a Database Backup Strategyp. 240
How to Prevent Losing Everythingp. 241
Using Transaction Logs in Backup and Recoveryp. 241
Choosing the Appropriate Recovery Modelp. 242
Finding the (Right) Timep. 244
Where Do I Back Up To?p. 245
What Do I Back Up?p. 245
Backing Up Spy Net Using Enterprise Managerp. 246
Backing Up Spy Net Using Transact-SQL in Query Analyzerp. 251
Restoring Spy Netp. 252
How to Get It All Backp. 253
Applying Transaction Logsp. 254
Restoring Spy Net Using Enterprise Managerp. 256
Restoring Spy Net Using Transact-SQL in Query Analyzerp. 258
Finalizing Your Planp. 259
Keep Track of Copiesp. 259
Mark the Transaction Logp. 260
Create a Warm Serverp. 260
Create a Checklist of Resources and Practicesp. 261
Make and Follow Your Recovery Stepsp. 261
Summaryp. 262
Next Stepsp. 262
Chapter 11 Administering Spy Netp. 263
Configuring SQL Server 2000p. 264
Performing General Administration Tasksp. 266
Scheduling Jobsp. 266
Calling on SQL Server Operatorsp. 273
Using Alertsp. 276
What Do SQL Server Logs Do?p. 276
Running Consistency Checksp. 278
Generating a Maintenance Plan for Database Consistency and Availabilityp. 280
Using the Maintenance Plan Wizard to Create Our Maintenance Planp. 280
Identifying Indexesp. 289
How Indexes Workp. 290
When to Use an Indexp. 290
Creating an Index on Our Person Table to Increase Query Performancep. 291
Maintaining Our Indexes to Keep Our Application Performing Efficientlyp. 295
Performance Monitoringp. 297
Monitoring Current Activityp. 297
Summaryp. 299
Next Stepsp. 299
Chapter 12 Designing a Front End to Support Our SQLSpyNet Databasep. 301
Understanding Basic Client/Server Architecture to Implement for Our SpyNet Applicationp. 302
What Is a Fat Client?p. 303
Why Use a Thin Client?p. 304
Which Configuration Is Best?p. 306
Choosing a Front-End Development Environment for Our Applicationp. 306
Microsoft Visual Basic (VB)p. 307
Microsoft Accessp. 307
Microsoft Active Server Pages (ASP)p. 309
Establishing a Connection to Our SQLSpyNet Databasep. 311
Using the Tried and True Method of ODBC (MSDASQL)p. 311
Using the New-Fangled OLE DB Provider for SQL Server (SQLOLEDB)p. 312
Building the SpyNet User Interfacep. 312
Determining the SpyNet Web Pagesp. 313
Setting Up Your Machine to Run the SpyNet Applicationp. 314
Setting Up Our Web Site to Run Under PWSp. 314
Creating a New User to Establish a Connectionp. 315
Creating Our First Page, the Global.asap. 316
Creating the Default.htm Pagep. 317
Building the Include Pagesp. 318
Validating the User's Loginp. 321
Greeting Users with the Welcome.asp Pagep. 325
Creating the Search.asp Page to Retrieve Data from SpyNetp. 329
Watching Speed and Consistencyp. 340
Surfing the SpyNet Sitep. 340
Summaryp. 342
Next Stepsp. 342
Chapter 13 Putting All the Pieces Togetherp. 343
Putting New Data into SQLSpyNet with the DTS Wizardp. 344
Backing Up the Database Before the Transferp. 345
Creating a Stored Procedure to Clean Out the Datap. 345
Using Enterprise Manager to Create a Stored Procedurep. 346
Denying Access to the Power of Deletionp. 348
Running the Stored Procedurep. 349
Using the DTS Wizard to Reload Our SQLSpyNet Database with Datap. 349
Checking the Results of the Importp. 358
Where Does Our Application Go Now?p. 360
Summaryp. 361
Next Stepsp. 362
Chapter 14 Troubleshooting and Debugging in SQL Server 2000p. 363
Using SQL Profiler to Find Errorsp. 363
Creating the Tracep. 364
Spying on Usersp. 369
Using the Client Network Utility to Resolve Connection Problemsp. 370
Debugging Stored Proceduresp. 372
What? No More Room?p. 373
How Memory Affects Database Transactionsp. 374
Shrinking Your Data Files to Reduce the Databasep. 375
Altering the Size of the Transaction Log Filesp. 376
Truncating the Transaction Logp. 377
Keeping the Faith with Fail-Over Clusteringp. 377
Summaryp. 378
Next Stepsp. 378
Chapter 15 Exploring SQL Server 2000 on Your Ownp. 379
Using Online Resources to Explore SQL Server 2000p. 379
Search and Retrieval Tips for Books Onlinep. 380
Finding Help on the Webp. 382
Seeing Double (or Triple, or...)p. 383
Running Multiple Instances on One Machinep. 384
Using Multiple Collationp. 385
Keeping Multiple Database Applications in Sync with Replicationp. 386
Leveraging Enhancements to Improve Developmentp. 386
Using Cascading Declarative Referential Integrity (Cascading DRI)p. 387
Mimicking Built-In Functions with User-Defined Functionsp. 388
Coding with Three New Data Typesp. 388
Getting More from Query Analyzerp. 389
Scripting Database Objectsp. 390
Using AFTER and INSTEAD OF Triggersp. 392
Looking at Distributed Viewsp. 393
Creating Enhanced Indexesp. 394
Leveraging XML Supportp. 394
Using the New Wizards in SQL Server 2000p. 396
Tuning Indexesp. 396
Copying Databasesp. 398
Improving Securityp. 399
Understanding Kerberos and Security Delegationp. 399
Using Password Security on Database Backupsp. 399
Using C2 Auditingp. 400
Summaryp. 400
Appendix A Setting Up Your PC as a Web Serverp. 403
Appendix B Installing and Configuring SQL Server 2000p. 411
Selecting the Type of Installationp. 412
Installing the Application Following the Step-By-Step Wizardp. 414
What Happens If I Select the Custom Install Option?p. 423
Checking the Installation's Successp. 430
Configuring SQL Server 2000p. 431
Connecting to SQL Server 2000 for the First Timep. 432
Using a Wizard to Connect to SQL Server 2000p. 437
How Does It All Work?p. 441
Appendix C Where to Go from Herep. 443
Microsoft Certificationp. 444
How to Become a Microsoft-Certified Professional (MCP)p. 445
How to Become a Microsoft-Certified Solution Developer (MCSD)p. 445
How to Become a Microsoft-Certified Systems Engineer (MCSE)p. 446
How to Become a Microsoft-Certified Database Administrator (MCDBA)p. 447
How to Plan for Microsoft Certificationp. 447
Study Guidesp. 448
Job Opportunitiesp. 449
A Thank You to You, the Readerp. 450
Indexp. 451