MCSA: SQL 2016 Database Development Certification Has Been Retired
This certification has been replaced by Microsoft with new certification(s).
Certification Details
Microsoft is a renowned provider of IT certifications covering different tech areas from beginner to advanced level. These certificates can be acquired through the industry proved exams and validate an individual's skills in Microsoft systems and products. One such popular and demanded option is MCSA: SQL 2016 Database Development.
Certification Overview
The MCSA: SQL 2016 Database Development accreditation proves its holder’s competence as a database professional with knowledge on both on-premises and cloud databases. In addition to that, this certification also authenticates the individual's abilities in the implementation of databases through entire organizations.
This intermediate-level certificate has no official prerequisites but it is recommended that interested candidates have about two years of practical experience. It mostly targets database professionals looking to sharpen their knowledge as well as those who work in a related area and know the ins and outs of SQL databases. These can be developers, system engineers, and other specialists willing to boost their skills in designing database files, performing tasks associated with indexes, stored procedures, etc.
Required Exams
The MCSA: SQL 2016 Database Development certification requires applicants to get a passing score in two mandatory exams that are 70-761 and 70-762. They focus on querying data with Transact SQL and the development of SQL databases, respectively.
Since these are the associate-level tests, each of them contains 40 to 60 questions that need to be completed within 120 minutes. The passing score one has to gain is 700 out of 1000. Remember to pay a registration fee of $165 for every exam.
70-761: Querying Data with Transact-SQL
The Microsoft 70-761 exam is divided into 3 domains that focus on the following skills:
- Managing data using Transact-SQL (40-45%)
- Data querying using Transact-SQL components (30-35%)
- Programming databases with Transact-SQL (25-30%)
To pass this test, the candidate should have a comprehension of Transact-SQL and should be skilled in creating queries. Also, one has to know how to differentiate between UNION and UNION ALL, use operators, write specific queries according to requirements, etc. They should also have knowledge about tables and different types of joins, be capable of implementing functions and aggregating data. This includes the construction of queries through various functions, usage of various deterministic and non-deterministic functions, modifying data using WRITE, INSERT, UPDATE, and DELETE statements, as well as the use of statements in DML and DDL.
Furthermore, the examinee should know how to query data using advanced Transact-SQL components, identify query results, differentiate between table joins, and understand clauses like CROSS APPLY and OUTER APPLY. One more topic to pay attention to covers table expressions, their types, and their differences. The applicant must have the skills needed to group data using various functions. Usage of complex clauses like GROUP BY, GROUPING SETS, and CUBE will also be tested. The candidate should know how to query temporal and non-relational data.
It is also vital to have knowledge about creating programmability objects via Transact-SQL, along with implementing error handling, data types, and NULLs. One should be competent in using table-valued and scalar-valued functions, generating error messages, performing the implementation of transaction control, and others.
70-762: Developing SQL Databases
The domains covered by the Microsoft 70-762 exam are as follows:
- Designing and implementing database objects (25-30%)
- Implementation of programmability objects (20-25%)
- Management of database concurrency (25-30%)
- Optimization of database objects and SQL infrastructure (20-25%)
This test mostly assesses the individuals’ expertise in working with database files, diverse types of data, tables, indexes, along with maintaining data integrity. The examinee needs to be knowledgeable in designing and implementing relational database schema. This includes designing tables, improving this design due to normalization, and determining effective data types. The candidate should also have all the skills required to work with indexes, contrast types of columns, and follow best practices for implementing clustered index columns. In addition, skills in designing and implementing views will also be tested in this exam as well as the understanding of columnstore indexes.
For the second section, one must ensure the integrity of data using constraints and determine results based on Data Manipulation Language (DML). It is also required for the examinee to know how to create stored procedures using components based on the requirements of the business. Implementation of table-valued parameters and error handling is also assessed in 70-762. Furthermore, the applicant should know how to work with trigger logic, DML, DDL, and various functions.
Knowledge about the implementation of transactions, their types, and roles in high-concurrency databases is vital to get through this exam. The candidate should know how to manage isolation levels and have insight into optimizing concurrency and locking. Optimization of statistics and indexes is included in this exam too, and the applicants should have the basic know-how of statistics maintenance, identification of missing indexes, and consolidating overlapping indexes. Monitoring SQL server baseline performance, solving troubleshooting issues, and determining the best practice for the extended event along with other related topics are tested in this exam.
Career Opportunities
Earning the MCSA: SQL 2016 Database Development certification has its perks. It will expose one to new career paths with a decent average annual salary. These are the most popular options an accredited professional can apply for with the average annual salaries taken from the PayScale website:
- Database Administrator: $74k
- Senior Database Engineer: $116k
- Database Developer: $76k
- IT Manager: $89k
- IT Director: $121k
- Systems Administrator: $63k
- Senior System Engineer: $107k
Certification Path
Once obtained the MCSA: SQL 2016 Database Development certification, one will have different tracks to choose from. The first one is to opt for MCSE: Data Management and Analytics certificate. Also, an individual can earn other associate-level accreditations to expand the area of competence. These include MCSA: SQL Server 2012/2014 and MCSA: SQL 2016 Database Administrator.