About Exam

Exam number/code: 70-442
Exam name: PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
Exam no. of questions: 55 Questions
Duration: 120 minutes
Passing score: 700
Question types: Multiple choice questions (MCQ's), Multiple choice single answer, Multiple choice multiple answer, Drag and drop & Simulations (optional)

This certification exam measures your ability to design and optimize data access by using SQL Server 2005. Before you take the exam, you should be proficient in the job skills that are listed as following:

• Designing Efficient Access to a SQL Server Service
• Designing a Database Query Strategy
• Designing Error-Handling Routines
• Designing a Transaction Strategy
• Performance Tuning a Database and a Database Application

Exam Objectives

• Designing Efficient Access to a SQL Server Service
• Design appropriate data access technologies.
• Design an appropriate data access object model.
• Design a cursor strategy for a data access component.
• Decide when to use cursors.
• Decide how to maximize cursor performance.
• Detect which applications are using cursors and evaluate whether to remove them.
• Design caching strategies.
• Select ADO.NET caching.
• Design custom caching functionality.
• Design a refresh strategy for cached data.
• Design client libraries to write applications that administer a SQL Server service.
• Design server management objects (SMO) applications.
• Design replication management objects (RMO) applications.
• Design automation management objects (AMO) applications.
• Design SQL Server Networking Interface (SNI) for asynchronous queries.
• Design queries that use multiple active result sets (MARS).
• Decide when MARS queries are appropriate.
• Choose an appropriate transaction isolation level when you use MARS.
• Choose when to use Asynchronous queries.
• Designing a Database Query Strategy
• Write and modify queries.
• Write queries.
• Modify queries to improve query performance.
• Design queries for retrieving data from XML sources.
• Select the correct attributes.
• Select the correct nodes.
• Filter by values of attributes and values of elements.
• Include relational data, such as columns and variables, in the result of an XQuery expression.
• Include XML attribute or node values in a tabular result set.
• Update, insert, or delete relational data based on XML parameters to stored procedures.
• Debug and troubleshoot queries against XML data sources.
• Design a cursor strategy.
• Design cursor logic.
• Design cursors that work together with dynamic SQL execution.
• Select an appropriate cursor type.
• Design cursors that efficiently use server memory.
• Design cursors that minimize blocking.
• Design a strategy that minimizes or eliminates the use of cursors.
• Designing Error-Handling Routines
• Design code that validates input data and permissions.
• Design code that detects and reacts to errors.
• Design user-defined messages to communicate application events.
• Designing a Transaction Strategy
• Manage concurrency by selecting the appropriate transaction isolation levels.
• Design the locking granularity level.
• Design transaction scopes.
• Design code that uses transactions.
• Performance Tuning a Database and a Database Application
• Optimize and tune queries for performance.
• Evaluate query performance.
• Analyze query plans.
• Modify queries to improve perf