Passed 70-461 Exam : Querying Microsoft SQL Server 2012

  • Few days back (Nov 15th 2012) I went through this exam (70-461) and passed it. From long back I was looking an opportunity to give at least a single SQL Server Certification Exam to check my worth.

    Just a week back I scheduled a slot for this Exam (for yesterday). I was looking for some reading material for the same but I couldn’t get any. Though there is a book from Microsoft Press i.e. “Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012″, but it was not available at my nearest.

    As I maintain this blog on SQL Server and also evangelize on SQL Server 2012 latest features from long back, so I thought to give it a try and wow I went thru in a single shot!!!

    Here by this post I want to provide my readers some information and knowledge about this Exam, so that they can plan well for this and look forward to take up this exam.

    –> Exam Highlights:

    - There were total 54 Questions
    - Total time to attempt the paper was 3 Hrs.
    - Total Marks: 1000
    - Passing Marks: 700
    - The Questions in the Exam were:
    1. Mostly Objective Type.
    2. Some were Subjective: where you’ve to write code, like SELECT statement and/or View Definition, etc.
    3. And very few were Multiple Choice.
    4. A few Question were where you’ve to pick up the appropriate code snippet and arrange them in correct sequence.

    –> The Exam is divided into 4 modules:
    1. Create Database Objects
    2. Work with Data
    3. Modify Data
    4. Troubleshoot & Optimize

    You need to brush up on following Skills in the these Modules:
    -:Check links with the below items for more information:-

    –> Create Database Objects | 24%
    - CREATE/ALTER/DROP TABLE/COLUMN
    - Data Types (XML, DATETIME, SPATIAL, VARCHAR)
    - Constraints (Primary Key, Foreign Key, Unique, Check, Default)
    - Calculated Column
    - Views
    - Indexed Views (SCHEMABINDING, COUNT_BIG(*), CLUSTERED INDEX)
    - Stored Procedures, [link].
    - DML Triggers (INSERTED, UPDATED, UPDATE function) vs CHECK Constraint
    - TRIGGER for VIEWS (INSTEAD OF)
    - UDF (Functions), [link].
    - SP vs UDF, [link].

    –> Work with Data | 27%
    - New Functions in SQL Server 2012 (IFF, TRY_PARSE, CONCAT, FORMAT), [link].
    - FETCH-OFFSET, [link].
    - SEQUENCE, [link].
    - Ranking and Window Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE), [link].
    - JOINS (INNER, OUTER LEFT, OUTER RIGHT, CROSS), [link].
    - APPLY Operators (CROSS APPLY vs OUTER APPLY), [link].
    - CTE and Sub-Queries, [link].
    - PIVOT, [link].
    - ROLLUP, CUBE & GROUPING SETS, [link].
    - Dynamic SQL
    - ANY, SOME, ALL
    - CASE vs ISNULL vs COALESCE, [link].
    - FOR XML RAW/AUTO/PATH [ELEMENTS], [link].
    - Implementing XML Schemas and Handling of XML data

    –> Modify Data | 24%
    - Stored Procedure (with EXECUTE AS, RECOMPILE)
    - MERGE Statement (TARGET, SOURCE, WHEN MATCHED, WHEN NOT MATCHED, OUTPUT),[link].
    - EXCEPT vs INTERSECT
    - UNION vs UNION ALL, [link].
    - SCALAR vs TABLE Valued Functions.
    - Use of APPLY with UDFs, [link].
    - VARCHAR(MAX) and .WRITE(), [link].

    –> Troubleshoot & Optimize | 25%
    - Using Statistics
    - SQL Internal JOINS (NESTED – Small, MERGE – Large Sorter, HASH – Large Unsorted), [link].
    - TRANSACTIONS (BEGIN, COMMIT, ROLLBACK, XACT_ABORT, TRANCOUNT), [link].
    - ISOLATION Levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, SERIALIZABLE)
    - TRY/CATCH, [link].
    - RAISE vs THROW [link]
    - CURSORS (Row-Based) vs SET Based Approach
    - Table Hints (UPDLOCK, ROWLOCK, TABLOCK, …etc)
    - Query Hints (OPTION (OPTIMIZED FOR … [UNKNOWN]))

    –> You can visit following Study materials to prepare for this Exam:

    Microsoft Link for this Certification: http://www.microsoft.com/learning/en/us/exam.aspx?id=70-461

    Book “Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012″ on AMAZON.com: http://www.amazon.com/Training-Kit-Exam-70-461-Microsoft/dp/0735666059?tag=651998669-20

    Book “Microsoft SQL Server 2012 T-SQL Fundamentals” on AMAZON.com:http://www.amazon.com/Microsoft-Server-2012-T-SQL-Fundamentals/dp/0735658145/ref=sr_1_3&tag=651998669-20

    All The Best!!!

  • Thanks really appreciate it. I am using the study aid and would also recommend it especially to SQL newbies.  Also well done on your exams.  

    Chronicle Data Connect

  • How long did you took to study and pass the exam?

  • I didn't study or did any preparation separately as such.

    It was all about experience, as I'm working with SQL Server from long time.

  • thanks!

  • thanks Man !

  • Great, many thank's. This will be my roadmap.

  • I got the book Book “Microsoft SQL Server 2012 T-SQL Fundamentals” and have been doing the exam prep.  I'm dismayed to see that the exam is not multiple choice.  because the practice exams for this book are all multiple choice questions :-/ if this is the official exam prep from Microsoft, it doesn't make any sense why they would give practice tests in a different format to the actual exam... :-/

  • I just passed the exam and I used, Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012, listed above.  It took me about 3-4 days (~3 hour sessions)  to make two passes through the book.  First time, I just tried answering the questions at the end of the chapter, and the second time, I looked into into the chapters where I was a bit lost.   It's pretty dense with information and to me the book questions seemed harder than the test questions.  But that's ok, it's alright with me to be over-prepared for an exam, and ultimately it'll be more useful for your job to know more.  

  • Hello everyone !

    I have a question regarding the book : Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012 . I saw in the description that it has a voucher discount of 15% the price of exam. Do you know if in the eBook edition it has the voucher ?

    Thanks in advance !

  • Yes, the eBook has the voucher, but you have to ask for it. I bought that eBook (Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012) from OReilly, and wrote to them asking about the 15% voucher. They emailed the voucher number to me.

  • A word of warning: I bought the kindle version of the book from Amazon and I received neither a link to a downloadable version of the CD content that comes with the printed book, nor a voucher for the exam.

    The product information clearly states that the kindle version comes with both.

    I have contacted them about it and they said they would look into it but I'm not holding my breath.

  • whats more important when studying for this ?must i use trainer handbook, or training kit?

  • Thanks for the post. Regarding the Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012, does the CD come with a 'Test Database' that one can use to test SQL statements or how does it work?

  • Hi ,is full text search in the syllabus for the test 70-461? it is included in the Microsoft training kit but not on the module list , I spent a lot of time on it ,should I spend anymore time on it ?

    Thanks!