Passing Exam 70-461 – Querying SQL Server 2012

tl;dr – Study. A lot. Hard.

I recently took and passed MS exam 70-461. Here are my reflections on it. I am, of course, forbidden to tell you exactly what’s on the test, and I don’t know what’s in the entire bank of questions, so this is more of a guideline than anything else. Your mileage may vary, as usual.

Study Materials

I had previously been through the official class with a local training company, as well has having studied the official MS Press book, which I got from the library. (If you’re buying the books just to pass the tests, you’re wasting money.) However, I didn’t take the test immediately after. I’m kind of glad I didn’t since we didn’t actually cover some of the most important things on the exam in the course material. I got my best results by using the MeasureUp practice exam, and reading the suggested BOL articles on MSDN. Between those two things, as well as mucking around with the code on my own, I feel like got enough of a review of the material and exposure to new stuff to be able to pass.

The MeasureUp practice test isn’t perfect – not by a long shot. I sent in at least three corrections on their test. The only time that actually caused me a problem was when I answered a question incorrectly, and was shown an answer in the “explanation” that wasn’t an available answer for the question. Once or twice, the “correct” code was just flat-out wrong. However, even that was close enough, and I already had enough experience with the concept to get what they were driving at. Also, it’s not exactly cheap, but not much more expensive than the book. I would get the book from the library and pay for a 30 day pass to the practice exam. Even with the minor issues I described, I would still recommend it as a study tool.

One more thing about the MeasureUp exam: It’s nowhere near like the actual test, no matter how much they say it is. It’s a great study tool, but a poor excuse for a simulation of the actual exam. Example: Most of the questions on the MeasureUp exam are multiple choice, with some drag and drop. On the official exam, you will be asked to actually write code. So don’t get used to just picking the correct code snippet out of the list. Know how the code works, and be able to actually write it. This is where practicing with the examples in a copy of Management Studio will come in handy.

Subject Matter

There are four sections to the test. I’ll take each one in turn and talk a little about the types of things I think you should practice. Now the way I describe these doesn’t exactly line up with the exam objectives, but I think this is a decent way to study them, since they make more sense to me in this grouping.

Create Database Objects – I did pretty well on this section. Know how to create tables to work with FILESTREAM, and brush up on the ins and outs of views. Pay particular attention to options required for indexed views like SCHEMABINDING, as well as what VIEW_METADATA and CHECK OPTION do. Finally, know how SEQUENCE objects work, and when they’re a good choice.

Troubleshoot and Optimize – I did moderately well on this. Here, I’d recommend knowing isolation levels really well. Also know what the various *physical* join types are, and when they are to be expected. Know how to get execution plan information from T-SQL, and how to optimize queries by doing things different ways. Many of the questions involved optimizing queries not by altering an existing function or CTE, but by taking a different approach all together.

Modify Data – This was my best section. Know the intricacies of MERGE, as well as INSERT/UPDATE/DELETE. Know what the set operators do, like UNION (ALL), INTERSECT and EXCEPT. Often, you’ll see those being used to filter a result set in a derived table, so keep in mind what their output should look like. I’d also recommend careful review of the details of creating stored procedures and functions. More so table valued functions than scalar ones, at least in my experience. COALESCE and IIF are important functions. Know those well.

Work With Data – I did poorest on this section. Caveat: I am not a developer, and rarely spend my time writing queries to work with business data. If I were to study for this section again, I would spend a lot more time on the FOR XML set of functions, things like GROUPING SETS and ranking functions like RANK vs DENSE_RANK. I would recommend reviewing data types very carefully. A few of the questions relied on some of the more intricate details of them. (i.e., there’s only one date/time type that stores time zone information, and you should know the precision levels of the various non-integer numeric types.) Also, I recommend reviewing some of the more obscure types like ‘cursor’.

Exam Generalizations

Most of passing a Microsoft exam is a matter of reading the questions extremely carefully, and understanding what they’re asking for. No lie: I spent the most amount of my time on the very first question, trying to decide what a particular word in the question meant. I definitely recommend marking questions, and returning to them later if they take you more than a few minutes or so to answer. There were 42 questions in 120 minutes on my exam. I completed the test in a little over an hour, but going back to that first question, and deciding what they really wanted as the result took the bulk of my time. Don’t worry about skipping questions – you’ll get the chance to come back and review anything you marked or skipped at the end of the exam. When in doubt, mark it and come back to it later. You’ll make up the time on the simpler, “gimme” questions, and there are quite a few of those as well.

Often, you can tell what the correct answer is just by the context clues in the question. i.e., You’re joining data from two tables, and you want a result set for the first table, regardless of whether or not there are matching rows in the second table… it’s a good bet the correct answer is going to have a LEFT JOIN in it, effectively eliminating answers with INNER JOIN in them quickly. Process of elimination is a perfectly valid way to take this exam. Use it.

Hope that helps.

-David.