SQL with Manoj

 
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 | 24%

– CREATE/ALTER/DROP Database, [link], [video]

– CREATE/ALTER/DROP Table, [link], [video]

– ADD/DROP/Rename table’s Columns, [video]

– Data Types [video]:
– – (XML, DATETIME, SPATIAL, VARCHAR)

– Constraints [link], [video]
– – (Primary Key, Unique Key, Foreign Key, NOT NULL, Check, Default)

– Computed Columns [video]:

– Views [link]

– 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].
 

2. Work with Data | 27%

– New Functions in SQL Server 2012 (IFF, TRY_PARSE, CONCAT, FORMAT), [link].

– FETCH-OFFSET, [link].

– SEQUENCE, [link].

– Ranking Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE), [link], [video].

– OVER() Window Function, [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
 

3. 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].
 

4. 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]))

 
For “SQL Server 2014” exam certification check here.

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

Microsoft Link for this Certification: https://www.microsoft.com/learning/en-us/exam-70-461.aspx

Books on AMAZON.com:
– Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

– Microsoft SQL Server 2012 T-SQL Fundamentals
 

–> Download “SQL Server 2014 Express” (free) version to practice T-SQL Queries:

 
Join SQL Server 2016 groups on [LinkedIn] and [Facebook] for more updates.

 
All The Best!!!