70-461 Querying Microsoft SQL Server 2012

Introduction

70-461 Querying Microsoft SQL Server 2012 is an exam designed by Microsoft to test the skills and experience of SQL Server users. It’s recommended that you have two years experience before sitting the exam, however, it’s not a prerequisite and you can sit it at any time. For example I sat the exam with 1 year and 10 months experience (close enough!) and passed.

You book the exam on Microsoft website, however, you are actually taken through to the Pearson Vue website (the people who administer the exams). You will then choose the test centre, date and time that you want to sit the exam (if it’s available).

When attending the exam you MUST take two forms of ID showing the name that you book the exam in. Make sure you read your booking confirmation for any other requirements as the specific exam centre may have it’s own in house rules. My exam centre had CCTV in place to record all exams and you had to put your hand up if you had a question or needed the toilet.

If possible book your exam when Microsoft are offering a free resit, they run this offer fairly regularly and it’s a cheap way of re-sitting if your a unfortunate enough not to pass on the first attempt like me!

The Exam

There are between 42-55 questions in the exam, however, you will only find out on the day how many questions you’ll be answering. My time slot was 2.5 hours long, however, this included a questionnaire before hand and the ability to give comments on questions afterwards.

There are different types of questions within the exam.

Radio Button, multiple choice.
Check box, multiple choice to select all answers that apply
Drag and Drop, drag SQL fragments from one side of the screen to the other, then correctly order them
Writing SQL, there were only two questions!

To pass the exam you are required to score 700 out of 1000. The score is NOT a percentage score, it is an actual score and questions vary in what they are worth. The score is for you and Microsoft, when you are qualified no one else will see the result – you’ll just be an MCP, which is always good for the C.V!

The exam consists of questions from the following headings, along with the split of questions from each category:

Create database objects – 24%

Calculated Columns

Constraints – Primary Key, Foreign Key, Unique, Check & Default

CREATE, ALTER & DROP for both tables & columns

Data Types – all of them

Indexed Views

Stored Procedures

Triggers INSERTED & UPDATED

Triggers for views – INSTEAD OF & UPDATE

User Defined Functions Scalar, Table Valued & Multiple Line

Views

Work with data – 27%

ALL, ANY & SOME

SQL 2012 Funtions – CONCAT, FORMAT, IFF & TRY_PARSE

CASE versus COALESCE versus ISNULL

CROSS APPLY & OUTER APPLY

CTE’s & Sub-Queries

CUBE, ROLLUP & GROUPING SETS

Dynamic SQL

FETCH & OFFSET

Joins – INNER, LEFT OUTER, RIGHT OUTER, CROSS

PIVOT & UNPIVOT

Ranking Functions

SEQUENCE

Window Functions

XML – FOR XML RAW/AUTO/PATH & ELEMENTS

XML Schemas

Modify data – 24%

.WRITE()

EXCEPT & INTERSECT

MERGE

SCALAR vs TABLE Valued Functions.

Stored Procedures – ENCRYPTION, RECOMPILE & EXECUTE AS

UDFs – CROSS APPLY & OUTER APPLY usage

UNION & UNION ALL

Troubleshoot & optimise – 25%

Cursors

Error Handling – TRY .. CATCH, RAISERROR, THROW

Isolations Levels – READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT & SERIALIZABLE

Joins – NESTED, MERGE & HASH

Query Hints – all of them

Table Hints – all of them

Transactions – BEGIN, COMMIT, ROLLBACK, XACT_ABORT & TRANCOUNT

Using Statistics

The questions are not limited to the above list and they may come in varying formats. You need to make sure that you study for the exam. Although, my biggest tip is READ the questions carefully. I found that quickly reading the questions meant me missing one important word, also read the answers, many of them look the same but have one minor change!

I encourage anyone and everyone to become Microsoft Certified, it can only help your future, it can’t harm it!