This page is a one-stop solution for any information you may require for Querying Microsoft SQL Server 2012/2014 (70-461) Certification exam. The Microsoft 70-461 Exam Summary, Syllabus Topics and Sample Questions provide the base for the actual MCSA - SQL Server 2012/2014 exam preparation, we have designed these resources to help you get ready to take your dream exam.
The Querying Microsoft SQL Server 2012/2014 credential is globally recognized for validating Querying Microsoft SQL Server knowledge. With the MCSA - SQL Server 2012/2014 Certification credential, you stand out in a crowd and prove that you have the Querying Microsoft SQL Server knowledge to make a difference within your organization. The Querying Microsoft SQL Server 2012/2014 Certification (70-461) exam will test the candidate's knowledge on following areas.
Microsoft 70-461 Exam Summary:
| Exam Name | Querying Microsoft SQL Server 2012/2014 |
| Exam Code | 70-461 |
| Exam Duration | 120 minutes |
| Exam Questions | 40-60 |
| Passing Score | 700 out of 1000 |
| Exam Price | $165 (USD) |
| Books | 20461C: Querying Microsoft SQL Server (five days) |
| Exam Registration | Pearson VUE |
| Sample Questions | Querying Microsoft SQL Server Certification Sample Question |
| Practice Exam | Querying Microsoft SQL Server Certification Practice Exam |
Microsoft 70-461 Exam Topics:
| Objective | Details |
|---|---|
Create database objects (20–25%) |
|
| Create and alter tables using T-SQL syntax (simple statements) | - Create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE |
| Create and alter views (simple statements) | - Create indexed views; create views without using the built in tools; CREATE, ALTER, DROP |
| Design views | - Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications |
| Create and modify constraints (simple statements) | - Create constraints on tables; define constraints; unique constraints; default constraints; primary and foreign key constraints |
| Create and alter DML triggers | - Inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers |
Work with data (25–30%) |
|
| Query data by using SELECT statements | - Use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms, and joins (except, intersect); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; case versus isnull versus coalesce |
| Implement sub-queries | - Identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement |
| Implement data types | - Use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance, when to use what data type for columns |
| Implement aggregate queries | - New analytic functions; grouping sets; spatial aggregates; apply ranking functions |
| Query and manage XML data | - Understand XML datatypes and their schemas and interop w/, limitations and restrictions; implement XML schemas and handling of XML data; XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export XML; XML indexing |
Modify data (20–25%) |
|
| Create and alter stored procedures (simple statements) | - Write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of storeproc result; create stored procedure for data access layer; program stored procedures, triggers, functions with T-SQL |
| Modify data by using INSERT, UPDATE, and DELETE statements | - Given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement |
| Combine datasets | - Difference between UNION and UNION all; case versus isnull versus coalesce; modify data by using MERGE statements |
| 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 (25–30%) |
|
| 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 |
The Microsoft has created this credential to assess the knowledge and understanding of a candidate in the area as above via the certification exam. The Querying Microsoft SQL Server (70-461) Certification exam contains a high value in the market being the brand value of the Microsoft attached with it. It is highly recommended to a candidate to do a thorough study and also get a hand full of the practice to clear Querying Microsoft SQL Server 2012/2014 exam without any hiccups.