I want to pass 70-461 – Querying Microsoft SQL Server 2012

Hi,

I just want to share some of the thoughts that I have about how you in the most easy way can Pass exam. 70-461.

Basically, the easiest way is to learn SQL, but it is my opinion that you should have been using TSQL and the SQL server for at least 3-5 years, if you want to be sure to pass the exam.

The Exam is split into the following topics.

  1. Create Database Objects (24%)
  2. Work with Data (27%)
  3. Modify Data (24%)
  4. Troubleshoot & Optimize (25%)

At present (on 2012-10-22) there is no trainingmaterial from Microsoft, so you must learn a lot of stuff your self. That stuff includes (but is not 100% perfect):

  1. What is the syntax to
    1. Create/drop/alter a table
    2. Create/alter/drop  a filetable
    3. Create /alter/drop a view
    4. Create an indexed view.
    5. What types of functions are there.
    6. How to extract data to XML format (What is the difference between e.g. FOR XML AUTO, RAW, PATH)
    7. How do you insert, update, delete records.
    8. Understand different types of constraint (Unique, Foreign key e.g.).
    9. How SELECT works, and how to use Joins (at least inner, Outer and Cross joins), APPLY.
    10. How to Insert, Update and Delete data.
    11. How MERGE is working.
  2. Dynamic management views you must underStand:
    1. Views to tell you about missing indexes.
    2. Views to tell you about index usage, including physical info.
  3. Other things that you might need to know:
    1. How collation works.
    2. New features in SQL 2012 in relation to querying.
    3. General understanding of different types of objects (e.g. views, tables, Stored Procedures, Functions) and when to use what.
    4. Data types (especially the new ones).
    5. How Transactions works (also what the difference between implicit and explict transactions are).
    6. How Executionplans works
    7. Table hints
    8. Query hints

And there are much more. I would recommend also to read the studyplan at microsoft.

So how would you prepare. Well, I would start downloading the Adventureworks databases from CODEPLEX.COM.
When they are installed (at least just choose one of them, then I would do the following:

  1. Search Books online to read about the topics.
  2. Open SQL Server Management Studio (or other tools if they are required) and then start to code.
  3. If any doubts then try searching http://www.google.com (but be carefull. there is a lot of crap out there).

When you have learned a new topic, then you can move on to the next topic. However!!!!! What about the level of knowledge?

Well, that is always the question. Often Microsoft has this “Free shot” campaign, and if that is the situation (or if you have to much money), then try to take the exam. If you pass then you are fantastic. If you fail, you are still fantastic, but then you have learned something about the level of knowledge.

AND!!!!! At some point http://www.measureup.com and http://www.selftestsoftware.com will have some preparation questions ready, and Microsoft will send out the training material, and then you can use that for your preparations.

Good Luck (and please call me if you need any specific advises).

Søren

 

1) Print out a checklist (that could e.g. be mine, eventhough there are more topics to be covered)

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s