MCITP Administrator: Microsoft SQL Server 2005 Optimization and Maintenance (Exam 70-444) Study Guide

Before you take the exam, you should understand the following:

• Know how to establish the business requirements for the quality of your data.   Be familiar with the strategies that are used in the process of gathering information and capturing data requirements. You must understand the attributes of data quality and have knowledge of the tools that are available for you to manage and verify data.

• Know how to create queries to inspect your data.   Understand how to use queries to validate data. You must understand the difference between the various join types and how each can be used in analyzing your data. Know how to use set statements to analyze your data and when to use them to your advantage.

• Know how to use checksum when verifying data.   You need to know the principle of the checksum algorithm and how it is applied to verify data in your database.

• Know the methods for cleaning data.   Understand how the initial data sources and their use in the final output data set affect the cleaning process. You need to be familiar with some of the SQL Server Information Services transformations involved with data cleaning.

• Know how to reconcile data conflict.   Be aware of the various approaches to keeping a master data copy and the concerns for reconciling data in each approach.

• Know how to assign explicit constraints to your data.   Understand the constraints you can define to enforce entity, domain, and domain referential integrity. You also need to understand how default definitions and null values are used.

• Know your data types.   You need to be familiar with the built-in data types. Know when to use each data type and how it relates to data integrity. As an example, you should know when to use an integer versus a real or fixed-length data type versus one of variable length. Also be aware of the restrictions for the smalldatetime and datetime data types as well as the smallmoney and money data types. Know the data types that are being deprecated.