Just little about C#, .NET, SQL Server, SharePoint and SAP

Exam 70-442: PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005

Posted by zieglers on April 24, 2007

Database Developer

Today I got the exam 70-442: PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005. I passed with a score 980 / 1000. It’d be nice if I were able to see my incorrect answer…

 Anyways, here are my recommendations to the ones preparing for getting this exam soon:

There were 55 questions and 7 testlets again as usual (for North America). Namely, those testlets are

  • Tailspin Toys
  • Margie’s Travel
  • Consolidated Messenger (asked twice with different set of questions)
  • Contoso Ltd.
  • Woodgrove Bank
  • Choice Part

I want to mention about some keywords you need to know while dealing with those questions in the exam.

As for Tailspin Toys, make sure you understand the meaning and usage of the terms: DataSet, Read Committed, MARS.

For Margie’s Travel, the keywords are ‘set operations’, DataSet ( again 🙂 ), Repeatable Read, Data Reader.

For Consolidated Messenger (my favorite one 🙂 ) : Read Committed Snapshot, Keyset ( I strongly recommend you to fully understand Cursor options ), MARS, Data Reader, XML Reader.

For Contoso Ltd. : MARS, READPAST, Snapshot Isolation Level, Data Reader.

For the multiple choice part: RANK, CTEs (recursive stuff 🙂 , especially for hierarchical reporting in a single table if the columns are refering to each other. Since CTE is an important concept, understand where it is used and try to write one on your own. 😉 ), XML functions like .exist, .value, .nodes, Cursor types (Static, Dynamic, Keyset, …), CUBE, PIVOT, PERCENT, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS APPLY, CONTAINS …

Hope you also get a good exam experience.. 🙂


51 Responses to “Exam 70-442: PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005”

  1. showstoppa said



    can you check this document and confirm questions?

  2. zieglers said

    They are pretty accurate as well..


  3. Jacky said

    Hi Zieglers,

    Would you mind sending me the questions? I passed 70-431 last weekend, and would like moving to 70-442 & 70-441 now. Do you recommend which one I should do first?

  4. zieglers said

    Hi Jacky,

    Since you got your 70-431 and became MCTS, now you can continue with MCITP certification. In this track you have several sub-tracks.

    If you get 70-441 and then 70-442, then you become MCITP: Database Developer.

    You can also choose to go with 70-443 & 70-444. Once you pass these two, then you become MCITP: Database Administrator.

    Thay are independent sub-tracks. So you can choose any of both to go first.

    I also recommend you to check Microsoft Learning site for MCITP:


  5. bravorex7 said

    Hi guys,

    congrats zeiglers, n thanks for starting the thread.

    does anyone have PDFs / questions for 441 n 442?? cud u plz post em.

    thanks guys

  6. Jacky said

    Hi Mates,

    I passed 70-442 with 980/1000 today. Not too bad… It contains 7 testlets. The questions I had in the exams are:
    1. Choice – 15 questions
    2. WoodGrove Bank – 8 questions
    3. Blue Yonder Airlines – 5 questions
    4. City Power & Light – 5 quesitons
    5. Consolidated Messenger – 9 questions
    6. Humongous Insurance – 5 questions
    7. Contoso Ltd – 8 questions

    Guys, word hard and to get it… haha…


  7. Jacky said

    Hi Mates,

    Another thing I would like to mention is every testlet has its own time limit. So please be careful the clock when sitting there…



  8. zieglers said

    Congrats, Jacky! Thanks for sharing your exam experience…


  9. krajeck said

    Hey Guys – well done to you all!!

    Do you know if there are any blogs for 70-443 / 444???

    Thanks again.

  10. sql7999 said

    Yeah, does anybody know any blogs or forums for 70-443? The 70-431 here is excellent.

  11. dishanf said

    can anybody pls check the version of pass4sure? Is it still 2.29 ??

  12. duanxn said


    While reading the testking or actualtest Q&A, I found the answer for the first question is incorrect:

    You need to write a query that retrieves all product categories that exist either directly or indirectly under category 7. Which query should you use?


    SELECT * FROM Categories AS c2 WHERE c2.CategoryID IN ( SELECT
    CategoryID FROM Categories WHERE CategoryID = 7 EXCEPT SELECT
    cs.CategoryID FROM Categories AS cs WHERE ParentCategoryID = 7)


    SELECT * FROM Categories AS c2 WHERE c2.CategoryID IN ( SELECT
    CategoryID FROM Categories WHERE ParentCategoryID = 7 UNION ALL SELECT
    cs.CategoryID FROM Categories AS cs INNER JOIN Categories AS c ON
    c.ParentCategoryID = cs.CategoryID)


    WITH c AS ( SELECT CategoryID FROM Categories WHERE ParentCategoryID = 7
    UNION ALL SELECT cs.CategoryID FROM Categories AS cs INNER JOIN c ON
    c.ParentCategoryID = cs.CategoryID)SELECT * FROM Categories AS c2 WHERE
    c2.CategoryID IN (SELECT c.CategoryID FROM c)


    WITH c AS ( SELECT CategoryID FROM Categories WHERE CategoryID = 7
    UNION ALL SELECT cs.CategoryID FROM Categories AS cs INNER JOIN c ON
    c.ParentCategoryID = cs.CategoryID)SELECT * FROM Categories AS c2 WHERE
    c2.CategoryID IN (SELECT c.CategoryID FROM c)

    The answers both Q&A are “C”.But I think it should be

    WITH c AS ( SELECT CategoryID FROM Categories WHERE ParentCategoryID = 7
    UNION ALL SELECT cs.CategoryID FROM Categories AS cs INNER JOIN c ON

    cs.ParentCategoryID = c.CategoryID) SELECT * FROM Categories AS c2 WHERE
    c2.CategoryID IN (SELECT c.CategoryID FROM c)

    Can anybody confirm?

  13. zieglers said

    No, the answer is C. It is correct.

    Please check the classical ‘manager-employee hierarchy’ examples from the net using CTE. It has to be … WHERE ParentCategoryID = 7 …, since it’s the root.


  14. manojkumarfbd said

    Hi All

    I am preparing for the 70-442 & 70-441. Can anybody provide me the PDF for these exams.

    Email:- manojkumarfbd@gmail.com
    Mnaoj kumar

  15. jinxguy23 said

    is the question same as what is in like anyother sql exam from the testking or pass4sure

  16. jzh0mk said

    is pass4sure still working for 70-442 ?

  17. fodder74 said

    In response to #12 and #13, DuanXn is correct to point out they have the tables in their inner join backwards. “c.ParentCategoryID” doesn’t exist as the select statement associated with c is only retrieving the CategoryID column.

    Ignoring that mistake, the correct answer is D, not C. CategoryID is the root, not ParentCategoryID.

    I confirmed this by building the table and actually running each query to see the results. Answer C only brings back those records where ParentCategoryID = 7 and skips the record with CategoryID = 7.

  18. rtdba said

    Hi guys, can anybody post the exam pdfs for 441 & 442 pls.

  19. bonkster said

    Microsoft Self Paced Training Kit E-Book 70-442


    Enjoy! I also uploaded 70-441 till 70-445 e-books

  20. sleeveofwizard said

    is Pass4Sure v2.29 still working?

  21. dlangham said

    Passed today with 940 and yes Pass4Sure v2.29 is still OK to use.

  22. cobra007 said

    Guys, I need to write exam 70-447 but Microsfot say I need to go through book 7-443 and 70-444. Has any one here written exam 70-447 and did you prepare going through those two books?

  23. hardik10 said

    is Pass4Sure v2.29 still working for 70-442 exam ??


  24. marion00 said


    The above link and file resulted in “NOT FOUND” on the 4shared.com site. Anyone may provide some helpful suggestions how I can locate the v2.29 from Pass4Sure? It will be greatly appreciated!

  25. marasiso said

    Hi all my friends .
    really i need your help ..
    I want the 70-442 exam test king or pass4sure and i do not know where can i get it .
    Thanks a lot .

  26. domi666 said

    Here is the link for 70-442


  27. marasiso said

    Thanks alot domi666 but i need your opinion about this version

  28. domi666 said

    Inside of the rar you’ll find latest from Pass4sure v2.29, ExamWorx and from Actualtest.com 09.13.2006

    Hope this will help you.

  29. sam543 said

    Looks like no one has visited for a while, need some help about the exam ,is it same or modified, pls help those who wrote it recently. Appreciate your feedback.

  30. kfpanda said

    passed today with 960 and p4s v2.29 is still working …

    Good luck.

  31. mzhou said

    Would you please tell me more detail about 070-442, thanks.


  32. mzhou said

    I passed 070-432 this morning. Yes,p4s v2.29 is still working.
    Thanks for all guys who provided the information about test.

    Happy New Year.


  33. awazone said

    Passed 🙂 Good-luck everyone!

  34. deamondragon said

    Hi guys,
    I passed 70-431 and I’m planning to take the 70-441 or 70-442 as my next exam. Which exam is easier to pass? Is there some version of these two exams, which is recently often repeated in examinations at Prometric? What do you recommend? Is someone passed some of these exams recently?

  35. deamondragon said

    Hi guys,
    I passed 70-442! P4s v2.29 is still working!

  36. sqlexpressfree said


    has anyone taken this test recently?

    thanks all

  37. nabeelasif said

    Hi everyone,
    I have done my exam 70-431. Now move to 70-442. Is it possible or first I will do the exam 70-441? Another question regarding the simulations for 70-442. Is 70-442 exam includes simulations like 70-431?

  38. sqlexpressfree said

    hi guys,

    Im going to take this test in 2 weeks, is the pass4sure 2.29 still valid?

    thanks everyone

  39. deamondragon said

    I need cd materials (sample code and databases) that came with this MS Press Book: “Microsoft SQL Server 2005 Reporting Services Step by Step”.
    If anybody is interesting for this book, here is the link:

  40. booyeeka said

    hi, same question – is the pass4sure 2.29 still valid?

    thank you in advance!!!

  41. booyeeka said

    yep! 2.29 is still valid. 940/1000

  42. swagger123 said

    Hey there

    mate of mine wrote this exam on friday 4 sep 2009 and appraently it has changed he scored less than 300 can anyone confirm this for me or post the new exam

    you help would be apprecaited i write in less than a week thanks

  43. wfairb said

    hey — sorry about your friend — let me know how you make out — i’m right behind ya.

    good luck

  44. wfairb said

    p4s website says:
    “Questions and Answers : 140 Q&A
    Updated: September 7th , 2009”

    it doesn’t mention what the current version is…

  45. gbdimitrov said

    Hi guys,
    I passed 70-442 today! P4s v2.29 is still valid!
    Good luck!

  46. hitman1111 said

    Hi guys,
    is P4S v2.29 still valid?

    Many thanks

  47. rcivalero said

    I am taking the test next week. Are the questions still valid?

    • debz1 said

      Firstly Good Luck!! I am also taking it next week – so if anyone can give any advice is P4Sure v 2.29 still valid?

  48. hitman1111 said

    any updates?

    Thanks a lot!

  49. I passed this exam at 19 November, 2010. 935/1000 6 testlets and 14 common questions. Many questions from TestKings and ActualTests which are in Internet, but exists some new common questions which are i do not see before. Who need learning materials, send me a letter on email (gvozdik75@mail.ru)

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: