Microsoft SQL 2005 Exam 70-442

By Kalman Toth, Business Intelligence Architect

July 21, 2007

Microsoft Exam 70-442 is titled PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 . It is the second required exam for the MCITP: Database Developer certification.

The exam has a new twist to it: 6 business cases(testlets, scenarios). 15 questions (45 minutes) are general. Altogether 7 individually timed sections. The questions against the business cases are multiple selection items, most radio buttoned, some check marked, and some pulling blocks to their places. The 6 business cases start whereby you get a complete description of a business operation, small, medium or large, with technical and business requirements, including performance, and you are asked 5-8 questions (17-23 minutes typical for each business case). An arranging blocks in order question maybe the sequence of programming a transaction, or outlining a stored procedure solution.

There are 55 questions altogether on the 3-hour test. Important to remember: each business case has its own time limit! When finished with a business case, you can go back a review all questions in that section, change the answers, however, once you are out of it, you cannot go back it and the time leftover is just gone. Time remaining is shown in the upper right corner. The question number of total number of questions is shown on the lower right corner.

The entire exam is extremely practical in nature; there are no “trick” questions. A SQL Server 2005 development expert can pass it easily. Also a beginner who mastered “SQL 2005 Grand Slam”, or "SQL 2005/2000 & BI Triple Crown" can breeze through it. For beginners though probably requires a couple of tries.  Perusing Microsoft exam prep books helpful but I am quite doubtful that they are sufficient for passing. Also helpful internet available exam preps. While some of the exam preps usually have old, obsolete test questions, nevertheless, they are good to practice, get used to timing, buildup confidence and also some of the explanations may prove to be good knowledge. Occasionally though the exam preps may be giving the wrong answer and the wrong explanation. Buyers beware!  Also some of the blogs discussing exam experiences maybe helpful.  Just Google it.

The multiple choice 15 questions part stresses on the new features of SQL Server 2005: applying different trancaction isolations including snapshot, retrieving data from XML columns, T-SQL queries using the new syntax (INNER JOIN, LEFT JOIN...), CTE-s for tree processing, data access with MARS and CLR applications. Not all new though: distributed transaction, various cursor types, tracing slow running/blocked queries or index design.

The business case studies part is really interesting. After a page or two of description existing environment, problems and business requirements, you are given a few questions. You cannot select the answer based on the question. You have to go back to description part and check, for example, the solution requirement for system response time.

The passing score is 700.

Overall 70-442 is a very well-done, practical and fairly enjoyable test. If you don’t pass the first time make sure you have paper and pencil for writing down the items you missed as soon as you leave the exam center(braindump). Do some studying and working practical T-SQL multi-step scripts with Management Studio and schedule a retake within 1-4 weeks.  When you take the second time you have several advantages: some of the questions will be the same, you are also used to the style of exam and time management.

Here is a helpful hint: when 3 of the radio-buttoned answers are from SQL Server 2000 and one is a new feature of SQL Server 2005, the likelyhood is very high that the SQL 2005 answer is the correct one.