MCAD/MCSD/MCSE Training Guide (70-229): SQL Server 2000 Database Design and Implementation

by Moore, Thomas; Tittel, Ed

• ISBN13: 9780789729972
• ISBN10: 0789729970
• Edition: CD
• Format: Paperback w/CD
• Copyright: 2003-01-01
• Publisher: Que

Summary

This self-study Training Guide is organized according to the actual 70-229 objectives, equipping you with the knowledge you need to pass this exam, which measures your ability to complete the tasks required by a database professional by concentrating on subjects such as database normalization and denormalization; SQL Server data types; heterogeneous database connectivity; replication; user-defined data types and functions; Transact-SQL; XML; stored procedures and extended stored procedures; trigger rules; constraints; defaults; and performance tuning. All of these subjects are applicable to the exam and the tasks of the SQL Server database designer or administrator. Book jacket.

Table of Contents

Exam Preparation
Introducing SQL Server 2000	p. 11
Introduction	p. 14
Getting to Know SQL Server 2000	p. 14
Structured Query Language (SQL)	p. 19
The Complete SQL Server 2000 Software	p. 23
Books Online	p. 35
SQL Server 2000 Limitations	p. 40
SQL Server 2000 Versions	p. 41
Database Design for SQL Server 2000	p. 53
Introduction	p. 56
A Case Scenario Approach	p. 57
Entity Relationship Modeling	p. 59
Designing Entity Keys	p. 68
Understanding Database Normalization	p. 75
Maintaining Data Integrity	p. 80
The Client/Server Model	p. 91
Physical Database Design and Implementation	p. 107
Introduction	p. 110
Creating and Altering Databases	p. 111
Creating and Altering Database Objects	p. 129
Multiple Server Implementations	p. 158
Troubleshooting SQL Server Objects	p. 161
Querying and Modifying Data	p. 191
Introduction	p. 193
Data Retrieval	p. 193
Filtering and Formatting Data	p. 203
Data Summary	p. 217
Making Data Modifications	p. 231
Advanced Data Retrieval and Modification	p. 249
Retrieve and Filter Data Using Transact-SQL	p. 252
Grouping Data with Transact-SQL	p. 271
Summarizing Data with Transact-SQL	p. 280
Manipulating Heterogeneous Data with OPENQUERY, OPENROWSET, and Linked Servers	p. 285
Extracting Data into XML	p. 293
Using OPENXML to Read XML Data	p. 299
Importing and Exporting Data	p. 304
Programming SQL Server 2000	p. 329
Introduction	p. 332
Scripts, Batches, and Transactions	p. 332
Working with Variables	p. 334
Comments	p. 343
Statement Blocks with BEGIN...END	p. 345
Conditional Statements and Branching with IF...THEN...ELSE	p. 346
WHILE loops	p. 349
Managing Result Sets by Using Cursors and Transact-SQL	p. 351
Concurrency and Locking	p. 360
Enforcing Procedural Business Logic Using Transactions	p. 364
Managing Data Manipulation Using Transactions	p. 370
Implementing Security	p. 372
Working with Views	p. 389
Introduction	p. 392
Understanding Views	p. 393
Create and Alter Views	p. 396
Modifying Data Through Views	p. 414
Alter Database Objects to Support Partitioned Views	p. 424
Control Data Access by Using Views	p. 429
Triggers	p. 445
Introduction	p. 448
Constraining Data Modifications	p. 449
Triggers	p. 454
Trigger Actions and Order	p. 468
Security Implications with Triggers	p. 483
Troubleshooting Triggers	p. 487
Stored Procedures and User-Defined Functions	p. 505
Introduction	p. 508
Managing Data Manipulation Using Stored Procedures	p. 508
Calling Stored Procedures; Passing and Returning Parameters To and From Stored Procedures	p. 516
Enforcing Procedural Business Logic with Stored Procedures	p. 524
Filtering Data Using Stored Procedures	p. 527
Implementing Error Handling in Stored Procedures	p. 529
Managing Data Manipulation with User-Defined Functions	p. 532
Passing and Returning Parameters To and From User-Defined Functions	p. 541
Enforcing Procedural Business Logic Using User-Defined Functions	p. 542
Filtering Data with User-Defined Functions	p. 543
Implementing Error Handling in User-Defined Functions	p. 544
Boosting Performance with Indexes	p. 557
Introduction	p. 560
Overview of Indexing	p. 560
Creating and Altering Indexes	p. 571
Index Maintenance	p. 579
Indexed Views	p. 591
Full-Text Searches	p. 596
Implementing and Understanding Replication Methodologies	p. 617
Introduction	p. 620
Replication	p. 620
Benefits of Replication	p. 621
Understanding Replication Components: The Publisher/Subscriber Metaphor	p. 622
Replication Configurations	p. 626
Types of Replication	p. 632
Replication Agents	p. 640
Replication Internals	p. 644
Updating Subscribers	p. 649
Heterogeneous Interoperability	p. 652
Enabling Replication in Your Application	p. 653
Configuring the Distribution Server	p. 654
Monitoring and Tuning SQL Server Databases	p. 673
Introduction	p. 676
Tools Available for Monitoring and Tuning	p. 677
Optimizing the OS Configuration	p. 694
Optimizing SQL Server Configuration	p. 704
Monitor Activity with the Profiler	p. 714
Trace Playback and Diagnosis	p. 723
SQL Server Optimizer	p. 727
Securing Objects	p. 743
Final Review
Fast Facts	p. 757
SQL Server 2000 Requirements	p. 759
Database Design for SQL Server 2000	p. 760
Physical Database Design and Implementation	p. 763
Querying and Modifying Data	p. 767
Advanced Data Retrieval and Modification	p. 769
Programming SQL Server 2000	p. 772
Working with Views	p. 777
Use of Triggers	p. 778
Stored Procedures and User-Defined Functions	p. 779
Boosting Performance with Indexes	p. 780
Implementing and Understanding Replication Methodologies	p. 781
Study and Exam Prep Tips	p. 785
Learning Styles	p. 785
Study Tips	p. 786
Exam Prep Tips	p. 787
Final Considerations	p. 798
Practice Exam	p. 799
Exam Questions	p. 800
Answers to Exam Questions	p. 815
Appendixes
Glossary	p. 825
Overview of the Certification Process	p. 837
Types of Certification	p. 837
Certification Requirements	p. 838
What's on the CD-ROM	p. 843
PrepLogic Practice Tests, Preview Edition	p. 843
Using the PrepLogic Practice Tests, Preview Edition Software	p. 845
Exam Simulation	p. 845
Question Quality	p. 845
Interface Design	p. 845
Effective Learning Environment	p. 845
Software Requirements	p. 846
Installing PrepLogic Practice Tests, Preview Edition	p. 846
Using PrepLogic Practice Tests, Preview Edition	p. 846
Get More Exams	p. 848
Customer Service	p. 849
Index	p. 851
Table of Contents provided by Rittenhouse. All Rights Reserved.