SQLServerPedia

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?

1. Barack Obama, 1600 Pennsylvania Ave, Washington, DC
2. Brent Ozar, 1845 S. Michigan Ave, Chicago, IL
3. Lady Gaga, 3 Legs Drive, New York City, NY
   
4. 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.

MCM Prep Week: Microsoft Exams 70-433 and 70-451 is a post from: Brent Ozar – Too Much Information.
If you’d like to quote this content on other sites, please read how to use my blog’s content.