By this Post we want to provide our readers some information and knowledge about this Exam, so that they can plan well for this and look forward to go through 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:
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.
- DML Triggers (INSERTED, UPDATED, UPDATE function) vs CHECK Constraint
- TRIGGER for VIEWS (INSTEAD OF)
- UDF (Functions).
- SP vs UDF.
Work with Data | 27%
- New Functions in SQL Server 2012 (IFF, TRY_PARSE, CONCAT, FORMAT),
- FETCH-OFFSET.
- SEQUENCE.
- Ranking and Window Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE).
- JOINS (INNER, OUTER LEFT, OUTER RIGHT, CROSS), .
- APPLY Operators (CROSS APPLY vs OUTER APPLY).
- CTE and Sub-Queries.
- PIVOT.
- ROLLUP, CUBE & GROUPING SETS.
- Dynamic SQL
- ANY, SOME, ALL
- CASE vs ISNULL vs COALESCE.
- FOR XML RAW/AUTO/PATH [ELEMENTS].
- 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).
- EXCEPT vs INTERSECT
- UNION vs UNION ALL.
- SCALAR vs TABLE Valued Functions.
- Use of APPLY with UDFs.
- VARCHAR(MAX) and .WRITE().
Troubleshoot & Optimize | 25%
- Using Statistics
- SQL Internal JOINS (NESTED – Small, MERGE – Large Sorter, HASH – Large Unsorted).
- TRANSACTIONS (BEGIN, COMMIT, ROLLBACK, XACT_ABORT, TRANCOUNT).
- ISOLATION Levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, SERIALIZABLE)
- TRY/CATCH.
- RAISE vs THROW
- CURSORS (Row-Based) vs SET Based Approach
- Table Hints (UPDLOCK, ROWLOCK, TABLOCK, …etc)
- Query Hints (OPTION (OPTIMIZED FOR … [UNKNOWN]))
