Microsoft Learning:

Exam 70-442:

PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005

Published:April 28, 2006
Language(s):English, French, German, Japanese, Chinese (Simplified)
Audience(s):Developers
Technology:Microsoft SQL Server 2005
Type:Proctored Exam
Preparing for an Exam
This exam will be retired on June 30th, 2011.
Audience Profile
Candidates for this exam are professional database developers who design and implement database solutions. They have three or more years dedicated to database work, which may include writing Transact-SQL queries, designing and implementing programming objects, optimizing databases, designing databases at both the conceptual and logical levels, and implementing databases at the physical level. The typical work environment is an enterprise or a medium-sized organization. Candidates should be experienced in using Microsoft SQL Server 2005.
Candidates for this exam should have expertise in the following areas:
  • Writing Transact-SQL queries
  • Programming databases
  • Troubleshooting programming objects (stored procedures, triggers, user-defined functions (UDFs), user-defined types (UDTs), and queries)
  • Performing database performance tuning and optimization
  • Designing databases, at both the conceptual and logical levels
  • Implementing databases at the physical level
  • Designing and troubleshooting the data access layer of the application
  • Gathering business requirements
Credit Toward CertificationExam 70-442: PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005: counts as credit toward the following certification(s):
Note This preparation guide is subject to change at any time without prior notice and at the sole discretion of Microsoft. Microsoft exams might include adaptive testing technology and simulation items. Microsoft does not identify the format in which exams are presented. Please use this preparation guide to prepare for the exam, regardless of its format.
Skills Being MeasuredThis exam measures your ability to accomplish the technical tasks listed below.The percentages indicate the relative weight of each major topic area on the exam.The higher the percentage, the more questions you are likely to see on that content area on the exam.

The information after “This objective may include but is not limited to” is intended to further define or scope the objective by describing the types of skills and topics that may be tested for the objective. However, it is not an exhaustive list of skills and topics that could be included on the exam for a given skill area. You may be tested on other skills and topics related to the objective that are not explicitly listed here.
Designing Efficient Access to a SQL Server Service (19%)
  • 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 (31%)
  • Write and modify queries.
    • Write queries.
    • Modify queries to improve query performance.
  • Design queries for retrieving data from XML sources.
    • Debug and troubleshoot queries against XML data 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.
  • Design a cursor strategy.
    • Design a strategy that minimizes or eliminates the use of cursors.
    • 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.
Designing Error-Handling Routines (10%)
  • 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 (16%)
  • 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 (23%)
  • Optimize and tune queries for performance.
    • Design queries that have search arguments (SARGs).
    • Convert single-row statements into set-based queries.
    • Evaluate query performance.
    • Analyze query plans.
    • Modify queries to improve performance.
    • Test queries for improved performance.
    • Detect locking problems.
    • Modify queries to optimize client and server performance.
    • Rewrite subqueries to joins.
  • Optimize indexing strategies.
    • Design an index strategy.
    • Analyze index use across an application.
    • Add, remove, or redesign indexes.
    • Optimize index-to-table-size ratio.
  • Scale database applications.
    • Design applications to distribute data and workload transparently.
    • Identify code or processes that can be moved to a different tier to improve performance.
    • Rewrite algorithms to improve efficiency.
    • Specify a data-partitioning model.
    • Design queries that target multiple servers.
    • Implement scale-out techniques like federated database, service broker, distributed partitioned views.
  • Resolve performance problems.
    • Analyze application performance across multiple users.
    • Capture workload information.
    • Find out the causes of performance problems.
    • Specify resolutions such as: changing algorithms, scaling up, and scaling out, terminating a session.
  • Optimize data storage.
    • Choose column data types to reduce storage requirements across the enterprise.
    • Design appropriate use of varchar across the enterprise.
    • Denormalize entities to minimize page reads per query.
    • Optimize table width.
Preparation Tools and ResourcesTo help you prepare for this exam, Microsoft Learning recommends that you have hands-on experience with the product and that you use the following training resources. These training resources do not necessarily cover all of the topics listed in the "Skills Measured" tab.
Learning Plans and Classroom Training
Microsoft E-Learning
Microsoft Press Books There are no Microsoft Press books currently available.
Practice Tests
Microsoft Online Resources
  • Learning Plan: Get started by creating a step-by-step study guide that is based on recommended resources for this exam.
  • Microsoft SQL Server 2005 – Learning Portal: Find special offers and information on training and certification.
  • TechNet: Designed for IT professionals, this site includes how-to instructions, best practices, downloads, technical resources, newsgroups, and chats.
  • MSDN: Designed for developers, the Microsoft Developer Network (MSDN) features code samples, technical articles, downloads, newsgroups, and chats.
  • Microsoft SQL Server TechCenter: Find SQL Server–related resources from Microsoft and the broader SQL Server community.
  • Microsoft SQL Server Developer Center: Find SQL Server 2005 articles, downloads, support, and community resources for developers.
  • Microsoft Learning Community: Join newsgroups and visit community forums to connect with peers for suggestions on training resources and advice on your certification path and studies.
Have Questions? For advice about training and certification, connect with peers: For questions about a specific certification, chat with a Microsoft Certified Professional (MCP): To find out about recommended blogs, Web sites, and upcoming Live Meetings on popular topics, visit our community site:
Exam index

How To Get It

Schedule your exam through the following exam provider: