Passed 70-461 Exam : Querying Microsoft SQL Server 2012
Yesterday (Nov 15th 2012) I went through this exam 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
- 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!!!
- BIDS (1)
- Datatypes (3)
- DB Concepts (20)
- DBA Task (14)
- Differences (25)
- Excel (6)
- Hekaton (6)
- Indexes (6)
- Informatica (1)
- Integration Services (4)
- Interview Questions (6)
- JOINS (4)
- Microsoft (5)
- Misconception (2)
- MS SQL SERVER 2005 (8)
- MS SQL SERVER 2008 (8)
- MS SQL SERVER 2014 (8)
- MS SQL Server Denali (26)
- MS.net (1)
- New Features (10)
- Optimization Performance (11)
- Power BI (2)
- SQL DB Engine (2)
- SQL Errors (5)
- SQL Tips (48)
- Stored Procedures (9)
- tempdb (3)
- Uncategorized (7)
- VBA Macro (1)
- web apps (1)
- Windows (1)
- XML (13)
- March 2014 (2)
- December 2013 (4)
- November 2013 (1)
- October 2013 (2)
- September 2013 (2)
- July 2013 (3)
- June 2013 (3)
- May 2013 (3)
- April 2013 (2)
- February 2013 (2)
- January 2013 (2)
- December 2012 (4)
- November 2012 (3)
- October 2012 (3)
- September 2012 (5)
- August 2012 (1)
- July 2012 (5)
- June 2012 (1)
- May 2012 (4)
- April 2012 (7)
- March 2012 (1)
- February 2012 (2)
- January 2012 (3)
- December 2011 (11)
- November 2011 (1)
- October 2011 (6)
- September 2011 (3)
- August 2011 (2)
- July 2011 (12)
- June 2011 (2)
- May 2011 (3)
- April 2011 (1)
- March 2011 (5)
- February 2011 (9)
- January 2011 (7)
- December 2010 (9)
- November 2010 (6)
- October 2010 (4)
- September 2010 (3)
- August 2010 (3)
- July 2010 (1)
- June 2010 (1)
- May 2010 (1)
- March 2010 (1)
- February 2010 (1)
- January 2010 (1)
- December 2009 (1)
- November 2009 (1)
- September 2009 (2)
- August 2009 (1)
- April 2009 (2)
- March 2009 (2)
- February 2009 (3)
- Passed 70-461 Exam : Querying Microsoft SQL Server 2012
- SQL Server 2012 does not support Linked Server to SQL Server 2000, workaround
- Using OUTPUT Parameters in Stored Procedures
- Difference between TRUNCATE, DELETE and DROP?
- CUBE, ROLLUP, COMPUTE, COMPUTE BY, GROUPING SETS
- TSQL Interview Questions - Part 1
- Query Excel file source through Linked Server
- Best Practices while creating Stored Procedures
- Denali - 2012
- DIRTY reads and PHANTOM reads
- 535,800 hits
StatCounter …since April’2012
Global Stats… since Jan 2014
This is my personal blog site.
The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MS Books On Line and/or MSDN/TechNet.
My employer do not endorse any tools, applications, books, or concepts mentioned on the blog. I have documented my personal experience on this blog.