70-461 Lessons Learned

For those that may do 70-461 in the future, I have some lessons learned from the experience. These are just my opinions, and this may not work for everyone.

1) CBTNuggets will get you about 25% of the required knowledge. Only do this course if you want to get the foundation information, otherwise ignore it as it takes up a lot of time to complete and offers little value.

2) The PluralSight course is significantly better. Learn to tolerate the extreme pitch fluctuation in the trainers voice (seriously it’s so annoying) and you’ll get much better coverage and explainations of the concepts. However this will only cover about 75% of the material.

3) Use CBTNuggets for the practice exams. There are links on the right of the course where you can launch into some external practice exam providers. These practice exams are identical in format to the real one. They are not as hard as the real exam, but they do provide good explanations for the correct answer, and it usually tells you why the other answers are wrong. There are 108 questions on offer. Do them all.

4) This site: http://www.mcsaquestion.com/ provides dumps of the questions from the real exams, with answers. I didn’t think it was legit until looking at it after failing the first time, and I found that the questions actually are the same as the real exam and the answers are correct. It’s not comprehensive though, and it provides no reason for why one answer is correct, and some of it fails to render. It also won’t help with the questions where you have to write queries or construct queries from a set of options, so don’t rely on just trying to remember everything from this.

5) I found it best to physically write the key words down in the questions and the unique difference in the correct answers, so it becomes sort of a mental trigger when you read the questions, like:

Minimize tempdb space and prevent reading queries from blocking writing queries? READ COMMITTED SNAPSHOT

Single-use dynamic queries? Enable optimize for ad hoc workloads option.

Readonly table with clustered index and performance problems? Add a columnstore index

Ensure throughout a transaction the data maintains the original version? SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Need to ensure modification is retained if a commit fails? IF (XACT_STATE()) = 1

Need to check if parsing fails or not and provide default if failure? Use IIF

Casting XML with a schema? DECLARE @xmlvariable XML(schema_name)

Sequences: CREATE SEQUENCE…START WITH…INCREMENT…MINVALUE…MAXVALUE…CYCLE

Run-time error occurs in a sproc? use SET XACT_ABORT ON to terminate and rollback

6) Passing the exam relies primarily on rote learning. You will need to know each keyword and the correct syntactic sequence in it’s use.

7) You will be allocated 150 minutes for the exam, and you will get assigned around 50 questions. Don’t worry, 150 minutes is loads of time for it. A lot of the questions can be answered in under 30 seconds, so you can clear a whole heap of the exam quickly. I got through it all in about 90 minutes, and spent another 30 going through every question again.

Also (for Canberrans), book your exam through PearsonVUE. Try to go with RAE Consulting for the actual place to sit it, as they have much more flexible times than DDLS (as in, you can do the exam on a Saturday).