Introduction70-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!
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
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
- Window Functions
- XML – FOR XML RAW/AUTO/PATH & ELEMENTS
- XML Schemas
Modify data – 24%
- EXCEPT & INTERSECT
- SCALAR vs TABLE Valued Functions.
- Stored Procedures – ENCRYPTION, RECOMPILE & EXECUTE AS
- UDFs – CROSS APPLY & OUTER APPLY usage
- UNION & UNION ALL
Troubleshoot & optimise – 25%
- 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!