About Microsoft 70-461 Exam
The Microsoft 70-461 exam is designed for those professionals who are looking to validate their knowledge and skills in writing queries. This certification test covers skills in both SQL Server 2012 and SQL Server 2014. The successful completion of this exam leads to the attainment of the MCSA: SQL Server 2012/2014 certificate. Microsoft 70-461 is not the only qualifying test for this associate-level certification. You are also required to pass two other exams to get certified.
Microsoft 70-461: Target Audience
The 70-461 certification exam is intended for the professionals performing the following job roles: SQL Server Database Administrator, System Engineer, and Developer. The test does not have any official prerequisites. All a candidate needs to do in order to attempt it is to fully understand the exam objectives. Although not required, it is recommended that you have two to three years of experience writing queries.
Microsoft 70-461: Exam Details
This certification test consists of 40-60 questions and the time allotted for the completion is 120 minutes. However, the total time for the exam is 150 minutes. The applicants are supposed to pay $165 and register on the Pearson VUE platform to schedule their test. The fee for the Microsoft 70-461 exam only applies to a single delivery. This means that if you have to retake it, you will need to pay the same amount again. The passing score for the test is 700 and any score lower than this has a fail status.
The exam is available in English, Simplified Chinese, French, Brazil Portuguese, German, and Japanese. Although Microsoft does not reveal the details of its tests, you can expect certain question types during the delivery of your 70-461 exam. These include short answer, multiple choice, drag and drop, best answer, build list, case studies, active screen, and mark review, among others.
Microsoft 70-461: Exam Topics
The Microsoft 70-461 exam is designed to evaluate specific skills. The learners will be measured based on their ability to perform some technical tasks. Therefore, to develop competence, you must understand the following in detail:
Create Database Objects (20-25%):
- Create & alter tables with the use of T-SQL syntax: the candidates should be able to create tables with the use of built-in tools, such as ALTER, DROP, CREATE, and ALTER COLUMN.
- Create & alter views: this domain requires that the students have abilities to create indexed views and those views without the use of built-in tools, such as CREATE, DROP, and ALTER.
- Design views: the examinees should have skills in ensuring code non-regressing by maintaining consistent signature for views, procedures, and function as well as security implications.
- Create & modify constraints: this area requires one’s competence in creating constraints on tables, defining constraints, defaulting constraints, unique constraints, and foreign and primary key constraints.
- Create & alter DML triggers: the individuals should understand everything about deleted and inserted tables, triggers types, nested triggers, handling multi-rows within a session, updating functions, and implications of performance for triggers.
Work with Data (25-30%):
Query data using SELECT statements: the test takers will need the ability to use a ranking function to choose top (X) rows for compound categories in one query; efficiently write and perform queries with the latest code items such as joins and synonyms; implement logic that utilizes system metadata and dynamic SQL; write effective and technically complex SQL queries; determine code that may or many to implement based on given tables; determine statements set that would load tables; use various data technologies; analyze given tables with constraints.
- Implement subqueries: this domain requires your competence in identifying challenging components in a query plan, applying operator, un-pivot and pivot, with the statement, and the cte statement.
- Implement data types: one should know how to understand the limitation and uses of different data types; use relevant data; identify the impact of GUID on database performance; identify when to use specific data type for columns.
- Implement aggregate queries: the applicants should understand how to group sets, apply ranking functions, spatial aggregates, and new analytics functions.
- Query & manage XML data: the examinees need to understand XML datatypes as well as their interop and schemas with restrictions and limitations; how to implement handling of XML data and XML schemas; how to handle XML data in SQL Server; when and when not to utilize it; how to export and import XML and XML indexing.
Modify Data (20-25%):
- The learners need to have skills in writing stored procedure to fulfill specific sets of prerequisites; creating stored procedures as well as other programmatic objects; branching logic; approaches to developing stored procedures; creating stored procedures for data access layer; differentiating among types of storeproc results; programming stored procedures, functions, and triggers with T-SQL.
- Modify data through the use of INSERT, DELETE, and UPDATE statements: the candidates should be able to decide on the output of specific DDL sets given specific code with defaults, triggers, and constraints; understand SQL statements that are best for solving basic requirements using output statements.
- Combine datasets: the individuals should develop their skills in distinguishing UNION and UNION ALL; modifying data with the use of MERGE statements; understanding case vs. isnull vs. coalesce.
- Work with Functions: this area requires one’s competence in explaining deterministic and non-deterministic functions, table and scalar values; creating and altering user-defined functions, and applying built-in scalar functions.
Troubleshoot & Optimize (25-30%):
- Optimize queries: the candidates should have a good understanding of statistics and be able to read query plans. They should also know how to plan guides, statistics IO, hints, DMVs, dynamic versus parameterized queries, as well as explain joint type and scenarios they can be used in.
- Manage transactions: the skills required here include marking transactions; understanding begin tran; committing and rolling back; explicit versus implicit transactions; types and scope of locks; isolation levels; and trancount.
- Evaluate the usage of set-based operations versus row-based operations: the test takers should know when to use cursors, combine multiple DML operations, effect of scalar UDFs.
- Implement error handling: this domain requires that the students demonstrate their skills in implementing throw/catch/ try, utilizing set-based instead of row-based logic, and managing transactions.
Microsoft 70-461: Job Tiles and Salaries
According to Payscale.com, the individuals with the associated certification for the Microsoft 70-461 exam can explore a good number of job opportunities. Depending on their level of experience, the certified professionals can earn an average of $43,088 and as high as $137,560 per annum. Some job titles that these specialists can hold include an IT Manager, an IT Director, a Database Designer, a Database Administrator, a Database Developer, a Senior Systems Engineer, and a Systems Administrator.