A few weeks ago, I glowed about how much I thought the MCITP exams had improved since the old braindump days. I had a really good experience with the 70-432 and 70-450 exams on SQL Server database administration, and I was happy that the exams focused on good decisions, not mindless memorization of syntax.
Now, about the 70-433 and 70-451 exams for SQL Server database developers….
I’m totally bummed out.
Just Because You Can….
The exams were a syntax-fest that focused on features that, quite frankly, I don’t want my SQL Server developers ever using, let alone memorizing.
Need to shred XML on a regular basis? I’d like to introduce you to a spiffy concept: application servers. Get your CPU-intensive queries the hell off my SQL Server, because Enterprise Edition costs $30,000 per socket. Do your XML processing on an app server that costs less to maintain and that scales easier. What’s that, you say? You desperately need to access it often and quickly in day-to-day queries? Here’s another novel concept: store it natively as relational data. Other developers, views, stored procs, and reports will be able to access it without doing the XMLambada.
Storing XML in the database is a crutch for a bad schema design. If you like regularly querying XML columns in the database, you’ll probably love the NoSQL movement. (Let me know how that works out for you. (On second thought, don’t.))
I half-expected the next question to ask about the new scissors designed with pistol grips so that they’re easier to run with. This is probably a personal gripe on my part about some of SQL Server’s new features lately, not the exam, but it felt like the exam focused a little too much on things I’d rather not see in production.
Where are the questions asking when it’s appropriate to use cursors? Triggers? UDFs? Don’t tell me that good SQL developers already know this stuff, because I see way too many people abusing features that come back to haunt them later.
What’s the Best Way to Punch Yourself in the Junk?
Several times, I read the question, shook my head, and wanted to meet the person who wrote the question. They must be wearing an armor-plated codpiece. I can’t repeat the questions here since the test involves an NDA, but here’s one I made up:
In your high-volume, mission-critical database, you need to create a series of nested triggers to call web services hosted in South Africa when any record is inserted. Which of the following commands will work best:
I’ve got your command right here, and it starts with UPDATE dbo.Employees SET IsActive = 0 WHERE LastName = ‘Ozar’. Reading questions like this, I wanted to run to the bathroom for some Calgon soap. Here’s another made-up question:
You’re a database developer and all of your queries against a certain table are slow. Should you create a new index, update the statistics, force a query execution plan, or use an index hint?
None of the above – you should get a DBA involved. Forcing a fix for one table or one query at the developer level is a really bad idea. Zoom out, get more information about the bigger picture, and avoid making hard-coded problems for yourself down the road. Forcing a query to use a specific index makes the DBA’s life hell, because he can’t do big-picture performance tuning. He might add or remove indexes at the database level to make overall performance better, but your query won’t improve.
The more questions I answered, the more I started to understand the mindset of some of the shops where I’ve done performance tuning. So often, I’ve walked in, taken a look at the environment, and said, “Yes, this will technically work, but it won’t scale. We need to take a step back and ask why the application is trying to (insert crazy feature here) hundreds of times per second. Let’s look at the business need behind what we’re doing, and find the most efficient way of accomplishing that goal, because this isn’t it.” During the exam, I found myself arguing with the screen because sometimes, all of the answers were bad ideas or jumped to conclusions too quickly.
How to Ignore Distracting Information
Question: Some people are Republicans, some people are Democrats, and some are independent. Independence doesn’t mean that they’re financially successful like a pop star or a CEO – it just means they haven’t made up their political mind. Which of the following people live in Illinois?
- Barack Obama, 1600 Pennsylvania Ave, Washington, DC
- Brent Ozar, 1845 S. Michigan Ave, Chicago, IL
- Lady Gaga, 3 Legs Drive, New York City, NY
- Steve Jobs, 2108 Waverly St, Palo Alto, CA
Some tests bombard you with unrelated information in an attempt to confuse you. It’s a lot like working with a junior project manager who just found the fridge with the free Red Bull.
Anytime a question has more than one sentence in it, I use the same technique I use to handle work emails. I jump down to the last sentence, read it, and then decide whether I have any real work to do. In the example above, the last sentence asks, “Which of the following people live in Illinois?” I would scan quickly through the answers, rule out anyone who doesn’t live in Illinois, and that’s my answer.
If more than one answer matches, then I scan back through the question very quickly looking for the additional criteria, but at that point it’s easier because I’ve already ruled out half of the answers.
So anyway, I passed, but until the score screens came up, I was positively convinced I failed. Want to take an exam yourself? Microsoft is running a “second shot” promotion – fail an exam, and you get a free retake. Read the details here.
25 Comments. Leave new
Brent,
I completely understand your frustration. Having worked on the 70-451 exam as a Subject Matter Expert I’m limited in what I can say but I can tell you that none of us were happy with the questions when we started and we worked very hard in a short amount of time to make the best of them. As for the bad XML questions, the team hated the XML questions and after several intense rounds of rock, paper, scissors, lizard, spock, I ended up having to re-write half of the XML questions myself.
If I’m ever honored with being chosen to be an SME on an exam again I’ll call you for ideas on the XML questions. Oh wait, can’t do that… What we can do (I think) is to tell MS Learning that their skills measured list does not reflect our reality and work with them to test on features that make sense to the community. I don’t know who picks what goes on the list but maybe we should do our own lists and see what we come up with:
http://www.microsoft.com/learning/en/us/exam.aspx?ID=70-451&locale=en-us#tab2
Thanks for taking the time to give your opinion about the exam!
@SQLvariant
Wow! Congrats on being chosen as an SME for that, that’s quite an accomplishment. Very cool.
It’d be interesting for PASS to put together a set of recommended tasks to master. This is what a production DBA needs to know, this is what a developer needs to know, this is what a data warehouse admin needs to know, etc. That might help guide MS Learning better.
Good write up and I like the tip about multiple sentences in a question. I’ll have to try that for the rest of my MCITP exams. Other than your hateful fear-mongering, nice job!
I read the last sentence or two of your post and got all excited (free retake!), so I read the rest of the post and was totally bummed (free junk re-punch?). No, but this was entertaining, reminding me of the 2000 MCDBA exam I took (70-228 I think).
I’ve fought the XML battle at work before. Business folks want reports off of XML data stored in SQL Server and I’m like No, it’s too slow, we need to convert it to relational data first. I’ll be crying if I’m forced to shred XML for some report.
I am going to try 70-433 next week, but after reading your post I must admit I am a little confused. I am not going to memorize syntax and if I will fail because of this, then so be it, this means 70-433 it’s not for me.
Similar to Aaron, I also worked as a SME on the 2008 exams. I can’t recall if I did anything for the 451, but I know I worked on the 433. I won’t claim to have worked on more than one or so of the XML questions. I do like the 2005 exams over the 2008 exams, but I’m probably biased.
That’s a good post Brent.
I took the DEV path last year for 2005 and I also was hmmm disappointed with amount of attention the XML got in favor of other features. I am (behold) DB developer and a bit of DBA and it is smaller problem (to me) if as you write certain ‘features haunt them’ developers later, the bigger problem is that they also haunt me, not being selfish here.
I remember taking the 2005 exam in spring 2006 (about a month before the textbook came out). Like you, I passed, but decided that maybe this is icing I don’t need.
Slightly off topic… Who am I kidding, this is way off topic… but I noticed that you used nested asides (i.e. nested parentheses) in this article.
Good on you! I’ve found myself beginning to use them and I’ve always chickened-out and rewrote the sentence to avoid it. But it actually doesn’t look too bad for a blog post. I’m going to have to try again some time.
Heh, yeah, I get a laugh out of the nested parens. It’s one of those things that only works well in programmer-oriented blogs, I think.
G’day Brent, wow – I remember feeling similar to you (angry and sure I’d failed) at the end of 70-441.
I’m no DBA but too many questions seemed to be saying “how would you best utilise [shiny new feature] to solve this problem – pick one” where all the options smelled vaguely funny. And ya, there were questions on syntax too.
I’m giving the MS exams a bit of a rest. Judging from the other comments, you’re not alone in feeling like venting 🙂
Thanks for your thoughts on the DB Dev exams.
I’m new to all this, so when I found out that Microsoft was so fond of XML and that that is even a type for table columns, it left me disappointed. Why all the effort of developing in a relational way only to be encourage sloppy XML data storage? As far as I’ve read, XML wasn’t meant for all it’s being used – but it’s terrible even for what it was meant to solve, YAML being a better syntax.
I don’t know if the SQL Server 2005 exams are retired yet, but do you think it’d be more valuable to take one of those exams now instead of a 2008? Do you think the HR people are up to speed on what the experts think about the latest exams? It’s hard trying to second-think them.
I’m reading the Microsoft Press books to eventually take these tests, but the short term reason is really to learn SQL Server and be able to apply what I’ve learned so that I have more questions and have to read more to answer them. I already have a project in need of a solution – implementing a 4GB text corpus and use full-text search to lookup words. I need that to work with leading wildcards too so I’ll have to find a way to index the words in reverse; I also need to return 20ish words to the right and to the left of all the occurrences of the word the user searched for. That’ll teach me some T-SQL.
The funny thing is that I could write a program to index all the words straight and reversed and look them up faster than I can implement the same solution in SQL Server. (that program is actually almost done because I couldn’t pass the opportunity to have some fun). The goal is to change that and to be as comfortable with SQL Server as I’m now with .Net.
This came out too long. I promise I’ll make it shorter next time. 🙂
I took and passed 70-443 and I was going to shoot you an eMail to see what you thought of 70-451. From the looks of it, not good. In my scoring for 70-433, the XML section was the lowest. Everything else was tip top. So, what do you recommend as study material for 70-451? Is there anything out there that you would recommend(besides an athletic supporter)?
Sadly, no, I don’t have any good ideas for study material other than the official stuff. I’d read the Microsoft Press books, because you won’t find this kind of advice in the field.
Take heart, though – I’d take advantage of the second chance program they’re running now. Go try it and see how you do before you put time into studying.
I took the exam this Friday and passed it with a pretty good score I think (925).
The first 4-5 questions were XML only at which point I thought there is no way I would pass but finally all went fine. Starting to prepare for 70-451 now
Having just taken 70-433 this morning, I feel your pain. It wouldn’t have been so bad if they had at least followed standard indenting and reserved word capitalization. Having passed it, I can’t believe I stressed so much over each question; then again, I only wanted to take it twice.
The only reason I’m going the DBD route is someday MCM might come into the picture. Just be glad they didn’t require the MCITP for BI, too.
Hello,
i would like to say thank you for this topic Brent.
Every time when i went for the cert. exam i found myself angry. Because questions are terrible. As you say some are out of real world. Others are answered in previous questions that’s funny. But when you get a q like :
“Design backup strategy for your SQL servers against total disaster of datacenter where they are located. And do not store your backup off site ”
I started to think they are joking and having fun on us when writing questions like my example.
So i make some comments after testing under some questions, but without hope for change.
On next exam it was same again .. 🙁
Hi Brent
Thought I would add to this, after just sitting the 70-451. I couldnt agree with you more. The strange thing for me is I was pretty confident I had passed very comfortably, but ended up with 785. What really bugs me is that you can’t review and learn from your mistakes before you leave. And the mystery scoring / weighting system leaves you guessing how “good” a pass you got, and just how many you got wrong. Its fine when you come across one you are debating on, but what if you got one wrong that you were 100% sure on… now you think you know something you don’t!
Ironically on my 70-433 I was less confident but ended up with 940.
I find myself guessing on some questions not what is the correct answer, but which is the least ridiculous or which is the one they want me to pick.
Any my god the amount of useless info in there, they could cut 60min off the time if they just got to the point!
Anyway, rant over…
Brent i missed to ask you yesterday,
Do you have or where can i get the sample test paper of MCM certification? Just want to see how it look like
Thanks.
Have you changed your mind since becoming an MCM? Would you get rid of sparse columns as well? Take your point I started wirh SQL on OS2 (Microsoft Sybase edition) and always hate the new features apart from the CASE stetment, but maybe I am behind the times 🙂
Geoff – no, I haven’t changed my mind on these at all. I’ve changed my mind about specific features – for example, I really like Service Broker – but the flip side is that I just don’t see this stuff in production often enough to consider syntax memorization to be part of a basic DBA certification. (Wow, that was a run-on sentence.) DBAs might deploy new XML datamodels once every 6 months, max – why force them all to memorize BS syntax for it?
Do you have any e-books that I can use while preparing for my 70-451 exam? Thanks!!
Reyam – you can buy plenty of them through Amazon.com and get them delivered immediately through the Kindle app. You can read those on your PC or handheld, too.
Right on the mark! Large, fast, scalable databases are those which force as much crunching to the middle tier as possible. XML indeed! Who the hell is storing data as xml? I want the contract to optimize their POS.
I have to agree about the xml questions that was my worst section too mostly as I don’t get to use it at all. I have an MCITP in database administration 2008 and decided to do the developer exams too in order to increase my knowledge of all things sql and also in order to do the mcm at some point. There is also a new offer on that gives you a free shot at the 2012 exams for each exam taken before the end of june which seemed like an easy way to get a first go at the new exams.
I also don’t like having to learn the syntax of commands when I can quite easily find the answers in books online etc seems like a wasted question I would much rather know be tested on the method or pattern to solving an issue that I can follow or implement as a solution.