Moore, Th: MCAD/MCSE/MCDBA 70-229 Exam Cram 2

Settings for Automatic Actions Moving the Cursor Settings for Recovery Set Coding Reaction with SQL Options The State of the Server Other Multiple Machine Database Implementations Standby Servers and Log Shipping Replicating Data Replication Strategies Types of Replication Exam Prep Questions 5. Retrieving and Modifying Data. Introduction Moving a Mountain of Data Mass Movements of Data via BCP Using Backup/Restore for More Than Recovery Data Movement with Manipulation Selecting Information from the Data Narrowing the Scope of Data Retrieval Conditional Filtering of Data Putting It All in ORDER Displaying Groups in Output More Advanced Query Groups Joining Multiple Sets of Data Return Output Only When It Matches-Inner Return Output Even When It Doesn't Match-Outer Joining Everything to Everything-Crossing Multiple Datasets-Unions Adding and Modifying Data Extensible Markup Language (XML) Simplifying XML Use Moving Data Using XML Exam Prep Questions 6. Programming Business Logic. Introduction Start with Data Analysis Prior to Coding Making Data Presentable Functions Make Data Work Aggregate Collective Functions Functions Used for Deviation and Variance Checksum Operations Of Calendars and Clocks Using Mathematical Functions in T-SQL String It Up Working with System-Level Functions Information Schema Views Providing the Orders to Be Followed Coding with Variables Setting and Using Variables Server Provided Global Values Document As You Go Statement Blocks with BEGIN...END Conditional Statements with IF...ELSE Multiple Conditions with CASE Loops in T-SQL Traditional Data Processing Converting Constructs to Code Using Stored Procedures Creating Stored Procedures Results from Procedure Execution True Customization with User-Defined Functions Alternative Coding Mechanisms Code Responses to Actions Appropriate Use of INSTEAD OF Triggers Exam Prep Questions 7. Tuning and Optimizing Analysis. Introduction Information Gathering Processes Operating in the Environment External Tools Provided by the Operating System Monitor Activity with the Profiler Using Profiler Traces to Diagnose Locking Trace Playback and Diagnosis Query Optimization The SQL Query Analyzer Query Execution Plans Reading Execution Plans Execution Plan Caching and Sharing Optimizer Hints Exam Prep Questions 8. Designing for Optimized Data Access. Introduction Concurrency and Locking Controlling Lock Behavior Controlling Business Logic Using Transactions Data Access Using Stored Procedures Optimization Through Design Pointing an Index in the Right Direction Clustered Indexes Nonclustered Indexes Index Maintenance over Time Using the Index Tuning Wizard Partitioned Views to Optimize Access Indexing Views for Optimization Maintaining the System to Get Back Performance Stored Procedures Used for Maintenance Problem Analysis Through Statistical Functions Database Console Command (DBCC) Exam Prep Questions 9. Designing a Database Security Plan. Introduction The SQL Server Login/User Model Using Security Roles Defining Your Own Roles Using Application Roles Encryption Can Secure Definitions Auditing User Activity Security Audits Statement Permissions Object Permissions Using Statement and Object Permissions Using Views to Enhance Security Stored Procedures Security Implementations Controlling, Filtering, and Enforcing Data Access Troubleshooting Security Issues Dealing with Broken Ownership Chains Exam Prep Questions 10. Completing a Business System Design. Introduction SQL Server Supporting Applications One- and Two-Tier Systems Three- or More Tier Systems Internet Application Architecture SQL Server 2000 Back Office Integration Third-Party Database Interactions Data Replication Very Large Database Applications Coding for Distributed Data A Brief Discussion on OLE-DB Using OPENROWSET Creating a Linked Server SQL Server Automation of Processes Job, Operator, Alert Integration Jobs Perform Recurring Tasks Operators Can Respond to Events Alerts Inform Based on Events Exam Prep Questions 11. Practice Exam One. 12. Answers to Practice Exam One. 13. Practice Exam Two. 14. Answers to Practice Exam Two. Appendix A. What's on the CD? Multiple Test Modes Study Mode Certification Mode Custom Mode Missed Question Mode Non-Duplicate Mode Question Types Random Questions and Order of Answers Detailed Explanations of Correct and Incorrect Answers Attention to Exam Objectives Installing the CD Creating a Shortcut to the MeasureUp Practice Tests Technical Support Appendix B. Need to Know More? Glossary. Index.