The first exam tests your ability to query a Microsoft SQL Server 2012, which means it will mainly test your SQL knowledge.
The following are the skills measured:
- Create database objects (24%)
- Tables – ALTER; DROP; ALTER COLUMN; CREATE
- Views – CREATE, ALTER, DROP
- Procedures and Functions
- Contraints – define constraints; unique constraints; default constraints; primary and foreign keys
- DML Triggers – nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers
- Work with data (27%)
- SELECT
- Sub Queries – query plans; pivot and unpivot; apply operator; cte statement; with statement
- Data Types – Using data types, impact of GUID (newid, newsequentialid) on database performance
- Aggregate queries – New analytic functions; grouping sets; spatial aggregates; apply ranking functions
- Query and manage XML data
- Modify data (24%)
- Create and alter stored procedures
- Modify data by using INSERT, UPDATE, and DELETE statements
- UNION and UNION ALL
- Work with functions – Understand deterministic, non-deterministic functions; scalar and table values; apply built-in scalar functions; create and alter user-defined functions (UDFs)
- Troubleshoot and Optimize
- Optimize queries – Understand statistics; read query plans; plan guides; DMVs; hints; statistics IO; dynamic vs. parameterized queries; describe the different join types (HASH, MERGE, LOOP) and describe the scenarios they would be used in
- Manage Transactions – Mark a transaction; understand begin tran, commit, and rollback; implicit vs explicit transactions; isolation levels; scope and type of locks; trancount
- Evaluate the use of row-based operations vs. set-based operations – When to use cursors; impact of scalar UDFs; combine multiple DML operations
- Implement error handling – Implement try/catch/throw; use set based rather than row based logic; transaction management
Number of Questions: 45-55
Type of Questions: Multiple Choice (May have scernario questions)
Passing Score: 700 (This does not mean 70%. Each question is given a score.)
Time Limit: 120 minutes
