Exam 70-762 – Microsoft Developing SQL Databases
MS 70-762 – Developing SQL Databases exam is designed for database professionals who build and implement databases across organizations and ensure high level of data availability. The MS 70-762 exam is challenging and detailed preparation is required to pass the exam. These kinds of certifications increase your chances for the job and makes you feel like a pro in your field. This certification will help you to increase your chances of getting your dream job & will help you climb higher in the corporate ladder. With the help of right resources and a perfect study guide you will definitely ace the exam and will get an advantage over your other mates. So, let’s get started.
What is MS 70-762 exam?
This exam measures your ability to accomplish the technical tasks such as- Design and implement database objects, implement programmability object, manage database concurrency, Optimize database objects and SQL infrastructure. The exam may test you on this above-mentioned basis but is not limited to these topics. So, you got to be conceptually strong in order to pass the exam with flying colors.
Exam overview
The exam MS 70-762 is designed for database developer enthusiasts. MS 70-762 will contain 40-60 questions which have to be answered in 120 minutes. You have to achieve a score of 700 out of 1000 in order to clear the exam which can be achieved through thorough preparation and right guidance.
The exam will cost you USD 165 if you are in US. The price of the exam may vary from place to place. This exam is available only in English language and you can take the exam through Pearson VUE platform. This certification is credit towards certifications of MCSA and MCSE.
Here is table for your assistance:
| No. of questions asked- | 40-60 |
| Time allowed | 120 minutes |
| Scoring policy | 700 out of 1000 is desired score in order to pass MS 70-762 |
| Pricing policy | USD 165 (In USA) |
| Languages available | English |
| Exam code | 70-762 |
| Schedule exam | Pearson VUE |
| Credit towards certification | MCSA & MCSE |
MS 70-762 Exam retake policy
- If you are unable to pass the exam in first attempt, the you must at least wait 24 hours before retaking the exam. You cannot retake the exam if you achieve a passing score.
- If you fail to pass the exam in second attempt too, then you must wait for a period not less than 14 days, in order to retake the exam. This is the same process for the failure in third, fourth or fifth attempt.
- You will not be allowed to give the exam more than 5 times a year. This year, 12-month period starts on your failure on 5th attempt. If you wish to give the exam more than 5 months a year then you have to contact Microsoft.
How to register for the MS 70-762 exam?
Follow the steps written below to register for the exam:
- Go to the Microsoft site and find your certificate in the list. Then select schedule exam, to register.
- If the certificate will be available for your country then you will be able to select the exam during the registration process.
- You will also select the language of the greeter, proctor, and proctoring software.
Academic pricing on the certifications
Academic pricing on Microsoft Certified Professional exams is available in most countries (except India and China). You must verify your student status before scheduling your exam in order to be eligible for academic pricing.
Applying student status through your account profile
- Sign in with your Microsoft account credentials.
- Select Profile settings from the Account menu at the top of the page.
- In the Job function menu, select, or ensure that you have selected, “Student.”
- Look for the academic pricing notice that appears next to the Job function menu. If your student status has not yet been validated, click “Get verified” to verify your status.
Applying student status when registering for an exam
- Sign in with your Microsoft account credentials.
- On the exam for which you want to register, click Schedule exam.
- On the Confirm your exam registration details page, ensure that the Job function field displays “Student – Verified.” If it does not, click “Get verified” to validate your status, or click Edit to change your status.
Verifying your academic status
Select the method you wish to use to verify your status. The methods include:
- School-issued email account
- School network credentials
- International Student Identity Card (ISIC)
- Verification code from a Microsoft representative or your institution’s administrator
- Documentation
Other exam policies
You can refer to the official site of Microsoft for the exam policies and other terms and conditions. Make sure to properly read all the terms and conditions so that you do not miss out on anything important for taking the exam.
To know more, visit: MS 70-762 exam FAQs
Syllabus details
These are the major testing basis as prescribed by the Microsoft. Let’s go through them, one by one-
Design and implement database objects (25–30%)
Design and implement a relational database scheme
- Design tables and schemas based on business requirements (Microsoft Documentation: Design tables in Synapse SQL pool)
- Improve the design of tables by using the normalization (Microsoft Documentation: Description of the database normalization basics)
- Write table create statements (Microsoft Documentation: CREATE TABLE (Transact-SQL))
- Determine the most efficient data types to use. (Microsoft Documentation: Efficient Use of Data Types (Visual Basic))
Design and implement indexes
- Design new indexes based on provided tables, queries, or plans (Microsoft Documentation: Indexes)
- Distinguish between indexed columns and included columns (Microsoft Documentation: Create Indexes with Included Columns)
- Implement clustered index columns by using best practices (Microsoft Documentation: SQL Server Index Architecture and Design Guide)
- Recommend new indexes based on query plans. (Microsoft Documentation: Database Advisor performance recommendations for Azure SQL Database, SQL Server Index Architecture and Design Guide)
Design and implement views
- Design a view structure to select data based on user or business requirements (Microsoft Documentation: CREATE VIEW (Transact-SQL), Understand data store models)
- Identify the steps necessary to design an updateable view (Microsoft Documentation: Viewing and Managing Updates, CREATE VIEW (Transact-SQL))
- Implement partitioned views (Microsoft Documentation: Partitioned Tables and Indexes)
- Implement indexed views. (Microsoft Documentation: Create Indexed Views)
Implement columnstore indexes
- Determine use cases that support the use of columnstore indexes (Microsoft Documentation: Columnstore indexes: Overview)
- Identify proper usage of clustered and non-clustered columnstore indexes (Microsoft Documentation: Columnstore indexes: Overview)
- Design standard non-clustered indexes in conjunction with clustered columnstore indexes
- Implement columnstore index maintenance. (Microsoft Documentation: Columnstore indexes – Data Warehouse, Indexing tables in Synapse SQL pool)
Implement programmability objects (20–25%)
Ensure data integrity with constraints
- Define table and foreign key constraints to enforce business rules (Microsoft Documentation: Primary and Foreign Key Constraints)
- Write Transact-SQL statements to add constraints to tables (Microsoft Documentation: CREATE TABLE (Transact-SQL))
- Identify results of Data Manipulation Language (DML) statements given existing tables and constraints (Microsoft Documentation: DML Triggers, Transact-SQL statements)
- Identify the proper usage of PRIMARY KEY constraints. (Microsoft Documentation: Unique Constraints and Check Constraints)
Create stored procedures
- Design stored procedure components and structure based on business requirements (Microsoft Documentation: Query Processing Architecture Guide, CREATE PROCEDURE (Transact-SQL))
- Implement input and output parameters
- Implement table-valued parameters (Microsoft Documentation: Using table-valued parameters)
- Implement return codes, streamline existing stored procedure logic
- Implement error handling and transaction control logic within stored procedures. (Microsoft Documentation: TRY…CATCH (Transact-SQL))
Create triggers and user-defined functions
- Design trigger logic based on business requirements
- Determine when to use Data Manipulation Language (DML) triggers, Data Definition Language (DDL) triggers, or logon triggers (Microsoft Documentation: DML Triggers, CREATE TRIGGER (Transact-SQL))
- Recognize results based on the execution of AFTER or INSTEAD OF triggers (Microsoft Documentation: Use the inserted and deleted Tables, CREATE TRIGGER (Transact-SQL))
- Design scalar-valued and table-valued user-defined functions based on business requirements (Microsoft Documentation: Create User-defined Functions (Database Engine))
- Identify differences between deterministic and non-deterministic functions. Documentation: (Microsoft Documentation: Deterministic and Nondeterministic Functions)
Manage database concurrency (25–30%)
Implement transactions
- Identify DML statement results based on transaction behavior (Microsoft Documentation: Transaction Locking and Row Versioning Guide)
- Recognize differences between and identify usage of explicit and implicit transactions (Microsoft Documentation: Implementing an Implicit Transaction using Transaction Scope, SET IMPLICIT_TRANSACTIONS (Transact-SQL))
- Implement savepoints within transactions (Microsoft Documentation: SAVE TRANSACTION (Transact-SQL), Using savepoints)
- Determine the role of transactions in high-concurrency databases. (Microsoft Documentation: Transactions and Concurrency)
Manage isolation levels
- Identify differences between reading Uncommitted, Read Committed, Repeatable Read, Serializable, and Snapshot isolation levels (Microsoft Documentation: SET TRANSACTION ISOLATION LEVEL (Transact-SQL), Understanding isolation levels, Snapshot Isolation in SQL Server)
- Define the results of concurrent queries based on isolation level (Microsoft Documentation: Understanding isolation levels)
- Identify the resource and performance impact of given isolation levels. (Microsoft Documentation: Understanding isolation levels)
Optimize concurrency and locking behavior
- Troubleshoot locking issues
- Identify lock escalation behaviors, capture and analyze deadlock graphs (Microsoft Documentation: Transaction Locking and Row Versioning Guide, Analyze Deadlocks with SQL Server Profiler)
- Identify ways to remediate deadlocks. (Microsoft Documentation: Deadlock Detection)
Implement memory-optimized tables and native stored procedures
- Define use cases for memory-optimized tables versus traditional disk-based tables (Microsoft Documentation: Comparing Disk-Based Table Storage to Memory-Optimized Table Storage)
- Optimize the performance of in-memory tables by changing durability settings (Microsoft Documentation: Defining Durability for Memory-Optimized Objects)
- Determine best case usage scenarios for natively compiled stored procedures (Microsoft Documentation: Overview and Usage Scenarios)
- Enable collection of execution statistics for natively compiled stored procedures. (Microsoft Documentation: Monitoring Performance of Natively Compiled Stored Procedures)
Optimize database objects and SQL infrastructure (20–25%)
Optimize statistics and indexes
- Determine the accuracy of statistics and the associated impact to query plans and performance (Microsoft Documentation: Statistics, Best practices with Query Store)
- Design statistics maintenance tasks (Microsoft Documentation: Update Statistics Task (Maintenance Plan)
- Use dynamic management objects to review current index usage and identify missing indexes, consolidate overlapping indexes. (Microsoft Documentation: SQL Server Index Architecture and Design Guide), sys.dm_db_missing_index_details (Transact-SQL))
Analyze and troubleshoot query plans
- Capture query plans using extended events and traces (Microsoft Documentation: Extended events in SQL Server)
- Identify poorly performing query plan operators (Microsoft Documentation: Detectable types of query performance bottlenecks in Azure SQL Database)
- Create efficient query plans using Query Store (Microsoft Documentation: Best practices with Query Store)
- Compare estimated and actual query plans and related metadata, configure Azure SQL Database Performance Insight. (Microsoft Documentation: Query Performance Insight for Azure SQL Database), Compare execution plans)
Manage performance for database instances
- Manage database workload in SQL Server (Microsoft Documentation: Best practices with Query Store)
- Design and implement Elastic Scale for Azure SQL Database (Microsoft Documentation: Scaling out with Azure SQL Database)
- Select an appropriate service tier or edition (Microsoft Documentation: Service tiers in the DTU-based purchase model)
- Optimize database file and tempdb configuration (Microsoft Documentation: tempdb database)
- Optimize memory configuration
- Monitor and diagnose scheduling and wait statistics using dynamic management objects (Microsoft Documentation: sys.dm_os_wait_stats (Transact-SQL))
- Troubleshoot and analyze storage, IO, and cache issues (Microsoft Documentation: Monitor, diagnose, and troubleshoot Microsoft Azure Storage)
- Monitor Azure SQL Database query plans.
Monitor and trace SQL Server baseline performance metrics
- Monitor operating system and SQL Server performance metrics (Microsoft Documentation: Performance Monitoring and Tuning Tools)
- Compare baseline metrics to observed metrics while troubleshooting performance issues (Microsoft Documentation: Intelligent Insights using AI to monitor and troubleshoot database performance (preview))
- Identify differences between performance monitoring and logging tools, such as perfmon and dynamic management objects (Microsoft Documentation: SQL Server, Memory Manager Object, Performance Monitoring and Tuning Tools)
- Monitor Azure SQL Database performance
- Determine best practice use cases for extended events (Microsoft Documentation: Extended events in SQL Server)
- Distinguish between Extended Events targets (Microsoft Documentation: Extended events overview)
- Compare the impact of Extended Events and SQL Trace (Microsoft Documentation: Extended events overview)
- Define differences between Extended Events Packages, Targets, Actions, and Sessions. (Microsoft Documentation: SQL Server Extended Events Packages)
Preparatory guide for MS 70-762
Your success will depend on how well did you prepare for the exam. To ace the exam, you have to prepare from every possible resource available to you. Here is the list of some of the resources which may show you the way. But always remember that there is no end to knowledge, you have endless resources beyond this list. Let us look at some important resources:
The official site
Always make sure to visit the official site before taking any exam for essential details. The official site of Microsoft has too many resources prescribed with the course. Microsoft provide instructor led trainings for MS 70-762 exam, online training that are different for different modules as prescribed in syllabus. Microsoft has also prescribed its own book- ‘Exam Ref 70-762 Developing SQL Databases’ for reference.
It has also made available many text resources and other lectures such as- Clustered and Non clustered Indexes, Columnstore Indexes – Design Guidance, Create and update database tables, Tutorial: Writing Transact-SQL Statements and so on for the ease and assistance of the candidates taking the exams. Microsoft has also made available its videos for the assistance of candidates regarding type and no. of questions and other details.
Libraries and the books
Books are another valuable resource for the preparation. You can find many books available online or you can take any book from libraries or bookstores as per your ease. You can choose various books and prepare the topic that is best in the book. Now-a-days libraries are available online too. So, you can access the study material whenever you want just at one click. You can also prepare from the book prescribed by Microsoft- Exam Ref 70-762 Developing SQL Databases. This book covers all the concepts that are required for clearing the exam.
Online classes and Instructor-led training
There are many online resources available like online classes or instructor-led training courses which are offered by online educational sites. They offer interactive classes that can be helpful for clearing your conceptual parts and doubts. They also keep you updated on new changes in the syllabus and other updates.
You can also form online study groups with the candidates of same interests. You all can keep testing each other to evaluate your progress and pool your study resources.
Your own strategy
Prepare your own strategy for study. Self-study is the key that will help you score more and will lead to perfection. Categories the syllabus into the parts which demands hands-on training and which is theoretical. Break the big parts into smaller one and then try to learn them. This will enable you to grasp things easily. Always make sure to never skip classes and keep your practice regular. Make notes and keep revising time to time.
Offline resources
You can take classroom lectures if you don’t feel comfortable with online classes. You can also from study groups which will help you boost your self-confidence and practice. This also will make you aware of other’s point of view. So, you can obviously improve the parts in which you lag behind.
Test series and practice papers
Many online sites provide you free test papers and paid test series which will keep evaluating your preparation. Taking the test series will help you identify what parts of the syllabus are strong from your side and on which parts you are required to put more efforts. Many sites provide trustworthy test series which help you gain confidence and will finally help you ace the exam. Since, clearing the exam is not the end, you have to apply the concepts practically to secure your dream job. Take a free practice test now!
Passing the MS 70-762 exam requires a lot of efforts but nothing is impossible. You just need to pick right resources for yourself and you can clear the exam easily. Just make sure to put your 100% efforts. Start preparing for MS 70-762 – Developing SQL Databases Exam.