Exam #1: Questions on Exam #70-433

I need to blog this now... before I forget. Everybody's exam is different, because they (randomly?) pull 50 questions for you from an inventory of several hundred possible questions. But here are some questions that were on my exam #70-433. (Somebody please tell me if I'll get in trouble with Microsoft for blogging about this!)

1. My first questions was on UNION ALL. Funny that I stumbled on this one at first, then went back and changed my answer. As you know, Union All will return all rows, whereas UNION will return only distinct rows. If you know that, you'll get this question right.
   
2. MERGE TOP 3 .... Of the 50 questions on my exam, this was my only question on the MERGE operation, which surprised me. I had studied the MERGE but I had never seen it with the TOP clause. The possible answers were 0, 3, 6, and 9. I guessed 3 and later learned that was correct! The TOP 3 clause will affect a maximum of 3 records total... this is true for INSERT, UPDATE, DELETE, as well as the MERGE.
   
3. To my surprise, I didn't get any questions about simple XML. In other words, the words "FOR XML RAW", "FOR XML AUTO", or "FOR XML EXPLICIT" were not even on my exam at all! But the exam is 12% XML, per Microsoft. For me, the XML questions were mostly about XPATH and XQUERY. Luckily, studying the examples of XPATH/XQUERY that came on the CD was enough to understand the basics and be able to answer the questions.
   
4. What file types do you feed into the Data Tuning Advisor (DTA)? There were 6 options. I think I got this one wrong... I chose XML, SQL, and something else. The more correct answer is this: trace files, trace tables, and SQL scripts.
   
5. One question I remember was straight from the practice exam. ALTER TABLE SWITCH was the answer. The question was: "You have two partitioned tables named Transaction and TransactionHistory. You need to archive one of the partitions of the Transaction table to the TransactionHistory table. Which method should you use?"
   

All the questions were multiple choice. A few questions were checkboxes where you choose more than one answer... but the question would tell you to "choose 2" or "choose 3" so that was nice.

It seemed to me like the questions were either easy SQL questions, or more difficult configuration questions. Maybe that's because I know SQL itself better than the components (Profiler, DTA, SQL Mail, etc) that go along with the SQL database development. I really feel that the 200 study questions on the CD that came with the book were excellent preparation for the exam... the training kit was an excellent bargain at $40.

The Goal

I've been working with SQL Server for about 12 years. And now I've decided I want to be certified. It'll be a learning experience for sure... but mainly, I want to be able to say I've done it. I'll blog a little about each exam. This is kinda like Julie & Julia... I'll be blogging as I go. But I won't blog that often, I'll blog once every few weeks or so. My goal is to pass all 6 SQL Server 2008 exams in the next year. Julie (in the movie) did it... she cooked all 365 recipes in a year. So I think I can pass 6 exams in a year. Stay tuned...

Exam #1: 70-433 (Technology Specialist: Database Development)

Slight confession.... I had passed my first exam (last week) before I started this blog. So I really only have 5 SQL exams to pass during the next one year. But common... that first exam is the biggest hurdle! It took me 6 months to get ready for this exam! Yes, I bought the book last November (2009) and read it in about a month, then took a break for the holidays, then started back studying in February of this year. I had a few weekends where all I really did was study the sample questions. And then I took the exam in June (2010).

And oh did I pass it... You only need 70% to pass the exam, well I scored 94% !! I'm pretty proud of myself for that :-)

So... because I passed my first Microsoft SQL Server 2008 exam, I have the MCTS (microsoft certified technology specialist) for database development. One test and I already get a real nice logo on my resume...

Here's how I prepared for this exam:

1) Buy the Microsoft SQL Server 2008 Database Development Training Kit (blue book with CD by Microsoft Press) for exam 70-433 for $40. Read the book and highlight anything you feel is important as you go. I didn't go through the labs, that wouldda taken forever. It took only about a month to read through the book. I then studied the info that I had highlighted.

2) Go through the 200 study questions that are on the CD. This is critical. And these questions are very accurate to what you'll see on the exam itself. It took me months to go through all these questions. I copied and pasted anything new into a notepad and saved it. I then printed these notes (about 30 pages worth) and read through them and highlighted anything new again.

Then I hoped for the best! (I'll blog about some of the specific questions later, so stay tuned!)