The MCTS 70-431 Exam Cram is a must-have resource in your MCTS 70-431 exam preparation. The Exam Cram focuses on exactly what you need to know to get certified. This includes features like: Complete coverage of all 70-431 exam objectives
Exam alerts, practice questions, notes, tips, sidebars, cautions, test-taking strategies, and time-saving tips
Access to MeasureUp practice questions and innovative testing software
The popular Cram Sheet tear card, giving you the key facts you should review before you enter the testing center
Insight from author Thomas Moore, a Microsoft Certified Trainer (MCT) and SQL Server expert who has helped hundreds of students earn their SQL Server certifications
Table of Contents
Introduction
Self Assessment
Chapter 1: Installing and Configuring SQL Server 2005
Installing and Configuring SQL Server 2005
Installation Requirements
The Installation Process
Installation Preparations
Postinstallation Procedures
Exam Prep Questions
Answers to Exam Prep Questions
Chapter 2: Creating Database Objects
Creating and Defining Databases
Using T-SQL to Create and Alter a Database
The Makeup of a Database
Standard Views, Indexed Views, and Partitioned Views
Miscellaneous SQL Server Objects
Defining SQL Server Tables
Using Types and Schemas
Using Columns with Automated or Calculated Values
Maintaining Order by Using Indexes
Primary/Foreign Keys and Relationships
Using Cascading Actions
Using DML and DDL Triggers
Partitioning Tables
Creating Partitioned Tables
SQL Server Programmability Objects
Stored Procedures
Creating CHECK Constraints
Creating Your Own Functions
English Query Capabilities with Full-Text Catalogs
Objects with Security Context
Exam Prep Questions
Answers to Exam Prep Questions
Chapter 3: Implementing Database Objects
Data Querying and Reporting
Listing the Contents of a Table
Making a Report More Presentable
Querying a Sampling of the Data Stored
Relating Data from Multiple Tables
Applying Conditional Data Filtering
Data Querying Using Full-Text Indexes
Creating and Populating a Catalog
Using System Tables and Views
Getting Information from System Tables
Information Retrieval from System Stored Procedures
Using Dynamic Management Views and Other System Views
Inserting Data
Using UDT and the CLR to Control Data Input
Using the CLR Within Stored Procedures
Inserting Individual Records
Using a Query to Insert Complete Recordsets
Disabling Functionality During Data Insertion
Changing What Is Already Stored
Updating a Single Record
Doing Updates That Affect Multiple Records
Performing Transaction Processing
Upgrading Data from Previous Releases of SQL Server
Removing Unwanted Data
Directly Removing Records from a Table
Indirectly Removing Data from a Table
Escalating Privileges to Allow Deletion
Controlling Privileges by Using GRANT, DENY, and REVOKE
Exam Prep Questions
Answers to Exam Prep Questions
Chapter 4: Supporting the XML Framework
Managing XML Data
Newly Supported XML Features
XML: The Basics
The xml Data Type and Methods
XML Method Interactions
Other SQL Server XML Support
Indexing XML Data
Creating Primary and Secondary Indexes
Native XML Web Service Support
Exam Prep Questions
Answers to Exam Prep Questions
Chapter 5: Data Consumption and Throughput
Importing and Exporting Data
Using the Bulk Copy Program (BCP)
Using OPENROWSET for Importing Data
Using SQL Server Integration Services (SSIS)
Implementing Service Broker
Designing a Service
Exam Prep Questions
Answers to Exam Prep Questions
Chapter 6: Database Maintenance
SQL Server 2005 Database Maintenance
What's New in SQL Server Maintenance?
Performing Database Backups
Recovery Models and Backups
Recovery Models Using T-SQL
Backup Types and Scenarios
Setting the Options of a Backup
Options of the T-SQL BACKUP Statement
Restoring Data from a Backup
Using the T-SQL RESTORE Statement
Using Database Snapshots
Creating and Deleting Database Snapshots
Automating Maintenance with Job Scheduling
Viewing Job Details and History
Exam Prep Questions
Answers to Exam Prep Questions
Chapter 7: Monitoring SQL Server Performance
SQL Server Management Studio
Monitoring and Recording Performance
Using Activity Monitor for the Here and Now
Management Studio: Log File Viewer
Server-Maintained Information
Using Windows System Monitor
Using SQL Server Profiler
Defining a Profiler Trace
Using Profiler Traces to Diagnose Locking
Trace Playback and Diagnosis
Using Profiler to Gather a Workload
Exam Prep Questions
Answers to Exam Prep Questions
Chapter 8: Troubleshooting and Optimizing SQL Server
Data Analysis and Problem Diagnosis
Tuning the Operating System and Hardware
Creating and Maintaining Statistics
Locks, Blocks, and Deadlocks
Tuning the Database Structure
Indexing Strategies
Data Partitioning Across Servers
Using the DTA
Server Configuration Maintenance
Using the Database Console Command (DBCC)
Setting Alerts to Automate Problem Identification
Exam Prep Questions
Answers to Exam Prep Questions
Chapter 9: Implementing High Availability
High-Availability Solutions
Implementing Log Shipping
Using Database Mirroring
Using Failover Clustering
Using Replication
Replication Strategies
Types of Replication
Elements of Replication
Microsoft Analysis Services
Exam Prep Questions
Answers to Exam Prep Questions
Chapter 10: Practice Exam 1
Exam Questions
Chapter 11: Answers to Practice Exam 1
Chapter 12: Practice Exam 2
Exam Questions
Chapter 13: Answers to Practice Exam 2
Appendix A: Suggested Readings and Resources
Appendix B: Accessing Your Free MeasureUp Practice Test
Appendix C: MeasureUp's Product Features
Glossary
