MCTS EXAM 70-433: Microsoft SQL Server 2008 Database Development

This year I decided to get a Microsoft SQL Server 2008 Database Developer Certification (MCTS EXAM 70-433). While I was doing consulting work at Avanade, getting at least one new certification was an annual requirement. I ended up changing jobs before I could do so, but decided to go ahead and get one anyway. I felt like my computer studies were in a rut; I did a lot of reading, but not enough coding. I figured that studying for a certification would be one good way of motivating myself to take a more hands on approach to my ongoing education. Writing articles for this website would be another. Combining the two resulted in this article about my experience with the exam.

I have never gotten a certification in anything before, so I was going into this with no idea what to expect. I ended up being really surprised. My overall take on this is that certifications are not a very good way to assess a candidate’s knowledge. It does seem like a good business to be in, for the software companies that offer the certifications, the testing companies that administer the tests, and the educational companies that offer preparation courses/guides. Despite business being good, there are definitely lots of improvements that can be made to make it a better experience for the test taker. Unfortunately, I have a sneaking suspicion that all parties involved feel that things are “good enough”, and feel that ironing out some of the kinks will yield diminishing returns in terms of profit. All this may come off sounding cynical, so I do want to state that I am happy to have gotten my database certification, as there is nothing more rewarding than setting a goal and achieving it. The process of studying for the exam has drastically improved my knowledge of SQL Server, and for that I am grateful.

The sign up process on the other hand, could use some streamlining. There are too many clicks involved and information about the test is hard to come by What I had expected was to see a prominently displayed sign up link on the Microsoft Learning homepage, where I could type in the name of the test I wanted to take (which would populate an AJAX autocomplete dropdown), enter in some registration information, and sign up for the test. Instead, I had to do an old fashioned search on the home page. I eventually ended up here: http://www.microsoft.com/learning/en/us/Exam.aspx?ID=70-433. Surprisingly, the sign up link is located off to the side, as though it were an afterthought. I’m not sure why they didn’t put this front and center. After all, isn’t the whole point of these exams to charge users cold hard cash to take them? Hiding the sign up link kind of defeats the purpose. Of course, one of the things that threw me off was that the testing is handled by a third party company, Prometric, so the hyperlink is actually an image of their company logo. As a first time test taker, I would not have known to look for this.

After clicking the link, I was redirected to the Prometric website. Once there, I ended up having to re-select which exam I wanted to take. Technically, it would not have been difficult for the hyperlink on the Microsoft website to contain a test id in the query string, and then have the Prometric website do some parsing and prepopulating of the test information on their end. This is one of those examples of the experience being “good enough” and not worth the effort to fix. The sign up process was pretty straightforward but did involve a lot of clicking and tedious filling in of webforms. After a few minutes of selecting a time and test location and filling out my credit card information, I finally managed to complete the registration process. The test costs about two hundred dollars. Its roughly what I expected, but it would have been nice to see the pricing information available earlier on in the process instead of all the way at the end. One good thing to note is that Prometric has the infrastructure in place to offer multiple testing centers at a variety of locations and business hours, and as a result, provides convenient and flexible scheduling.

I chose Bellevue Community College as my test center, but was confused because my registration confirmation did not tell me where exactly on campus the test would be. I tried to contact Prometric, but there was no oddly no phone numbers or email listed on their website. There was a Contact Us link where I managed to fill out a webform with my inquiry about the test location. Of course I never got a response. Perhaps this is their way of keeping costs low by not having to devote resources to respond to pesky customers. Luckily, my confirmation email did provide information about the test center, which included a phone number. I had first thought that this was the number for the college itself and was worried that the front desk would have no clue what I was talking about when I asked them where my Microsoft Certification exam would be located. It turns out the number provided was actually the number for the Bellevue College Testing Center itself, which is its own separate entity inside the school. The woman on the phone was able to provide me with all the relevant information I needed.

Try as I might however, I could not find any official information about the format of the exam. This was conspicuously missing on the Microsoft Learning website, the Prometric website, and even the official MCTS 70-433 Training Kit. Granted, the training kit came with a CD ROM that contained practice exams, but it was not altogether clear to me that these practice exams would be in the same format as the real test. I had expected there to be at least some “free response” type questions where I’d be given some sort of real world problem and would need to come up with a good database schema, write some queries against it, and perhaps come up with some intelligent choices for indexes. In other words, I expected the exam to contain questions similar to the ones I’d be asked in a real interview. Nope! It wasn’t until I took the test itself that I found out it would be done via testing software on a computer, consisted of roughly 55 multiple choice questions¹, with a little over two and half hours to complete. Belongings would be stowed away in a locker, and an erasable mat with markers would be provided. 700 points would be considered a passing score. I found it funny that they’d mention this, yet give no indication how many points each question was worth.

I have mixed feelings about the official Microsoft Training Kit. It consists of a 422 page preparation guide, a CD ROM that contains practice tests, and a discount code for the exam. The book was not too helpful. It sacrifices depth for breadth, giving each topic only cursory coverage. This became quite apparent after taking a few tests on the CD; many of the questions are not covered by the book at all. This is not so much the fault of the authors, as the book would need to be thousands of pages long in order to be sufficient. As a result, this is also not the book you want to read to learn Microsoft SQL Server. It does however, serve as a good jumping off point: Numerous other publications do deep dives on the topics discussed. For example, Inside Microsoft® SQL Server® 2008: T-SQL Querying by Itzik Ben-Gan does a great job teaching TSQL querying fundamentals, query tuning, and execution plans (it also happens to be one of my favorite books that I found myself referencing constantly during my studies).

My advice to any remotely competent database developer would be to focus on the practice exams, using the preparation guide as a reference, and supplementing it with other books on SQL Server as necessary. The testing software provides a Certification Mode that simulates an actual exam, a Study Mode that lets you answer questions at your own leisure, and a Custom Mode that lets you control various settings such as what categories of questions to ask, and whether or not to include questions answered from previous test attempts. The software is written by Measure Up, which is apparently a company that provides practice tests for Oracle, Cisco, Microsoft, Comp TIA and other such certifications. It is surprisingly solid, fast, easy on the eyes, comprehensive, and (most importantly) does not crash or cause blue screens of death. The UI is intuitive, which is what I’d expect from a straightforward multiple choice test. Unfortunately, when it came time to take the actual test at Bellevue Community College, the, the software was written by Prometric. It was a lot slower, the graphics were dated and ugly, but at least it didn’t crash. I’m not sure why Prometric didn’t just contract out the test software to Measure Up, because Measure Up had the far more polished product.

The test questions fall under a few general categories which are outlined in the preparation guide. I won’t rehash them all here, but the bulk of it consists of things an intermediate level database developer would have done throughout their career: creating and managing objects in a database schema and writing queries. The former involves knowing how to create tables, views, partitions, indexes, triggers, stored procs, functions, CLRs and managing the permissions associated with these objects. The latter involves understanding CTEs, recursive CTEs, pivoting, unpivoting, subqueries, joins, merges, groups, ranking functions (ROWNUMBER, DENSE RANK, NTILE etc), cubes, and rollups. You are also expected to know how to manipulate the XML data type, so be sure to brush up on your XPath and XQuery.

One of the test categories, SQL Server Components, assesses your knowledge of miscellaneous SQL Server components, including the Power Shell, full text indexes, Dbmail, and the Service Broker. Unfortunately, I felt that this category of questions was written in such a way that they ended up feeling more like trivia questions than an actual test of my understanding. For example, one question asks you how to enable message forwarding in the Service Broker. In the real world, you’d just use google to look up the command and its parameters; MSDN provides incredibly detailed documentation on SQL Server. In the testing center however, you are not allowed to use google, and this artificial constraint turns it into a test of how well you can do rote memorization instead.

Of course, the problem stems from the multiple choice format. It lends itself to forced cramming of knowledge with little understanding or retention. I remember how my computer science finals were typically all open book, and involved writing proofs. There was just no way to bs your way through like you can with multiple choice. Any good test taker can make educated guesses, eliminate incorrect answers, and even utilize information derived from previous questions. With all these drawbacks, why don’t they make these tests better? Well, as I said before, testing-for-profit is a lucrative business, and multiple choice exams make sense from a business perspective. You don’t need a person who understands the test material to grade the test, you just need a computer that can run some software. This makes the tests easy to grade and administer: Write once, get paid multiple times. This scales well, keeps costs low, and profits high.

All this means that a certification does not really mean much in and of itself. So is it even worth doing? Sure, you get a nice and shiny certification that’s signed by Steve Ballmer², but so what? Well for one thing, many consulting/contracting companies such as Avanade and Accenture do put a heavy emphasis on certifications, as it typically allows them to bill more for their headcount. Thus, lots of certs listed on your resume will make you look good when applying to these companies. Also, if you’re going to put in the effort to keep up to date on your tech education, paying a few hundred dollars for some sort of proof is better than nothing. It certainly won’t hurt. The certification is really for one’s personal edification; you only get what you put into your studies. If you focus on cramming to pass the test rather than learning the material, then its not going to do much good. When it comes time for the actual job interview, it will immediately be obvious which approach you took.

1 Some questions aren’t multiple choice and involve slightly more user interaction. These types of questions are the closest thing resembling a free response question that I could find. For example, one question might ask you for the correct TSQL statement needed to create a DML trigger that inserts into a history table. It provides you with a list of randomly ordered words (not all of which are needed), which you then need to assemble into the correct statement.

2 You have to fork out an additional 20 bucks for a hard copy of the certification.