Study material for exam 70-762 Developing SQL Databases

By: Daniel Calbimonte   |   Updated: 2017-02-08   |   Comments (3)   |   Related: More > Professional Development Certifications

Problem

There is a new Microsoft exam for SQL Server 2016 or later, which is exam 70-762 and it is for developing SQL databases. What are some of the materials I can use to study for this exam?

Solution

In this tip, we will give you some links, tips, books, and training exams to pass the 70-762 exam.

What is the 70-762 certification exam?

This is exam is related to creating indexes, views, constraints, columnstore indexes, stored procedures, user defined functions (UDFs), triggers, transactions, handling performance and more.

Is it an exam for database developers?

This exam is for DBAs and database developers.

Is it an exam oriented to SQL Server 2016?

Yes, if you pass this exam and 70-761, you will get the MCSA title (Microsoft Certified Solution Associate) in SQL Sever 2016 Database Development. However, this certification includes some topics like SQL Azure, because SQL Server has a lot of functionality that interacts with SQL Azure.

Which SQL Server 2012/2014 exam is similar to this one?

The 70-464 is similar to this new exam, but there were changes related to Azure and columnstore which is more important in this new exam. The security and CLR topics were removed.

Is this exam difficult?

The 70-761 exam is a piece of cake compared with this exam. This exam, is much harder and requires more effort and knowledge to pass.

Which books would you recommend for this exam?

There is no Training Kit book yet, but old books for older exams may help. Here are some:

1. SQL Queries 2012 Joes 2 Pros. Volume 4: Query Programming Objects for SQL Server 2012 (SQL Exam Prep Series 70-461 Volume 4 of 5 Kindle Edition) is useful for the for implementing programmability objects topic.
2. SQL Server Concurrency: Locking, Blocking and Row Versioning is a great book for the manage database concurrency topic.
3. SQL Server Execution Plans, Second Edition is a good book for the optimize database objects and SQL infrastructure section.
4. Inside the SQL Server Query Optimizer also helps for the optimize database objects and infrastructure.
5. Performance Tuning with SQL Server Dynamic Management Views is also a good book for the dynamic management objects topics.
6. Obviously, the books online in MSDN is the main book in SQL Server.
7. Pro SQL Server Internals 2nd ed. Edition is also a great book that covers 60% of all the topics.

Which courses would you recommend for this exam?

The official Microsoft course is 20762B, Developing SQL Databases.

There will be a specific MVA course in the future, but not yet.

Other courses that could help are:

• Pluralsight has several SQL Server courses with topics related to this exam
• SolidQ, check the data management courses
• The MVA course, Developing Microsoft SQL Server Databases

What links do you recommend for these exam?

Design and implement database objects

• Design and implement a relational database schema
  • Designing Databases
  • Normalization of Database
  • CREATE TABLE (Transact-SQL)
  • Data Types in SQL Server
  • Data Types (Transact-SQL)
• Design and implement indexes
  • SQL Server Indexing Tips
  • Stairway to SQL Server Indexes
  • Indexes
  • CREATE TABLE (Transact-SQL)
• Design and implement views
  • Creating Views - SQL School Video
  • CREATE VIEW (Transact-SQL)
  • SQL CREATE VIEW Statement
  • Views
  • SQL Server Views Tips
• Implement columnstore indexes
  • Columnstore indexes
  • Stairway to columnstore indexes
  • Columnstore Indexes in SQL Server 2012
  • Columnstore Indexes Guide

Implement programmability objects

• Ensure data integrity with constraints
  • Create Foreign Key Relationships
  • Constraints
  • Unique Constraints and Check Constraints
  • Primary and Foreign Key Constraints
• Create stored procedures
  • SQL Server Stored Procedure Tutorial
  • CREATE PROCEDURE (Transact-SQL)
  • Stored Procedures (Database Engine)
  • Using Table Valued Parameters (TVP) in SQL Server 2008
• Create triggers and user-defined functions
  • CREATE TRIGGER (Transact-SQL)
  • DML Triggers
  • SQL Server Triggers Tips
  • SQL Server Functions - User Defined UDF Tips
  • CREATE FUNCTION (Transact-SQL)
  • Deterministic and Nondeterministic Functions

Manage database concurrency

• Implement transactions
  • Transaction Statements (Transact-SQL)
  • Explicit Transactions
  • What is a transaction?
  • Using Indexes to Reduce Blocking in Concurrent Transactions
• Manage isolation levels
  • Questions About T-SQL Transaction Isolation Levels You Were Too Shy to Ask
  • SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
  • Demonstrations of Transaction Isolation Levels in SQL Server
• Optimize concurrency and locking behavior
  • SQL Server Concurrency: Locking, Blocking and Row Versioning
  • Understanding SQL Server Locking
  • The DBA as Detective: Troubleshooting Locking and Blocking
  • Detecting and Ending Deadlocks
  • SQL Server Deadlocks by Example
  • Analyze Deadlocks with SQL Server Profiler
• Implement memory-optimized tables and native stored procedures
  • Memory-Optimized Tables
  • Getting started with SQL Server 2014 In-Memory OLTP
  • Memory optimized tables for SQL 2014 and 2016
  • Natively Compiled Stored Procedures
  • Native Compilation of Tables and Stored Procedures

Optimize database objects and SQL infrastructure

• Optimize statistics and indexes
  • SQL Server Execution Plans, Second Edition
  • Statistics
  • Performance Tuning with SQL Server Dynamic Management Views
  • Index Related Dynamic Management Views and Functions (Transact-SQL)
  • How to get index usage information in SQL Server
  • SQL Server query execution plans  Viewing the plans
• Analyze and troubleshoot query plans
  • SQL Server Execution Plans, Second Edition
  • Capturing Graphical Query Plans with SQL Server Profiler
  • Query Store
  • SQL Server Estimated Vs. Actual Execution Plans
  • SQL Database Performance Insight
  • Extracting ShowPlan XML from SQL Server Extended Events
• Manage performance for database instances
  • Tuning a Workload
  • Scaling out with Azure SQL Database
  • Editions and Supported Features for SQL Server 2016
  • SQL Database options and performance: Understand what's available in each service tier
  • TempDB Performance and Configuration
  • Azure SQL Database and performance for single databases
  • Performance Tuning with SQL Server Dynamic Management Views
• Monitor and trace SQL Server baseline performance metrics
  • SQL Server Performance Monitoring Tools
  • Performance Monitoring and Tuning Tools
  • SQL Server Extended Events Tips
  • SQL Server Counters
  • SQL Server Dynamic Management Views and Functions Tips
  • Performance Tuning with SQL Server Dynamic Management Views
  • Azure SQL Database and performance for single databases
  • SQL SERVER  SQL Profiler vs Extended Events
  • Extended Events
  • SQL Server Extended Events Packages

Next Steps

Here are several links that will be useful:

• Exam 70-762
• Exam policies and FAQ
• Course 20762B: Developing SQL Databases

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips