Exam Ref 70-762 Developing SQL Databases

Louis Davidson, Microsoft MVP (Data Platform), Senior Data Architect for CBN. Over 20 years of experience with SQL Server as an architect, developer, and writer. He tweets as @drsql.   Stacia Varga, Microsoft MVP (Data Platform) is a consultant, educator, mentor, and author specializing in data solutions since 1999. She provides consulting and custom education services through her company, Data Inspirations, and writes about her experiences with data at blog.datainspirations.com, and tweets as @_StaciaV_.

Chapter 1 Design and implement database objects

Skill 1.1: Design and implement a relational database schema

Designing tables and schemas based on business requirements

Improving the design of tables by using normalization

Writing table create statements

Determining the most efficient data types to use

Skill 1.2: Design and implement indexes

Design new indexes based on provided tables, queries, or plans

Distinguish between indexed columns and included columns

Implement clustered index columns by using best practices

Recommend new indexes based on query plans

Skill 1.3: Design and implement views

Design a view structure to select data based on user or business requirements

Identify the steps necessary to design an updateable view

Implement partitioned views

Implement indexed views

Skill 1.4: Implement columnstore indexes

Determine use cases that support the use of columnstore indexes

Identify proper usage of clustered and non-clustered columnstore indexes

Design standard non-clustered indexes in conjunction with clustered columnstore indexes

Implement columnstore index maintenance

 

Chapter 2 Implement programmability objects

Skill 2.1 Ensure data integrity with constraints

Define table and foreign-key constraints to enforce business rules

Write Transact-SQL statements to add constraints to tables

Identify results of Data Manipulation Language (DML) statements given existing tables and constraints

Identify proper usage of PRIMARY KEY constraints

Skill 2.2 Create stored procedures

Design stored procedure components and structure based on business requirements

Implement input and output parameters

Implement table-valued parameters

Implement return codes

Streamline existing stored procedure logic

Implement error handling and transaction control logic within stored procedures

Skill 2.3 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

Recognize results based on execution of AFTER or INSTEAD OF triggers

Design scalar-valued and table-valued user-defined functions based on business requirements

Identify differences between deterministic and non-deterministic functions

 

Chapter 3 Manage database concurrency

Skill 3.1: Implement transactions

Identify DML statement results based on transaction behavior

Recognize differences between and identify usage of explicit and implicit transactions

Implement savepoints within transactions

Determine the role of transactions in high-concurrency databases

Skill 3.2: Manage isolation levels

Identify differences between isolation levels

Define results of concurrent queries based on isolation level

Identify the resource and performance impact of given isolation levels

Skill 3.3: Optimize concurrency and locking behavior

Troubleshoot locking issues

Identify lock escalation behaviors

Capture and analyze deadlock graphs

Identify ways to remediate deadlocks

 Skill 3.4: Implement memory-optimized tables and native stored procedures

Define use cases for memory-optimized tables 242

Optimize performance of in-memory tables

Determine best case usage scenarios for natively compiled stored procedures

Enable collection of execution statistics for natively compiled stored procedures

 

Chapter 4 Optimize database objects and SQL infrastructure

Skill 4.1: Optimize statistics and indexes

Determine the accuracy of statistics and the associated impact to query plans and performance

Design statistics maintenance tasks

Use dynamic management objects to review current index usage and identify missing indexes

Consolidate overlapping indexes

Skill 4.2: Analyze and troubleshoot query plans

Capture query plans using extended events and traces

Identify poorly performing query plan operators

Compare estimated and actual query plans and related metadata

Configure Azure SQL Database Performance Insight

Skill 4.3: Manage performance for database instances

Manage database workload in SQL Server

Design and implement Elastic Scale for Azure SQL Database

Select an appropriate service tier or edition

Optimize database file and tempdb configuration

Optimize memory configuration

Monitor and diagnose schedule and wait statistics using dynamic management objects

Troubleshoot and analyze storage, IO, and cache issues

Monitor Azure SQL Database query plans

Skill 4.4: Monitor and trace SQL Server baseline performance metrics

Monitor operating system and SQL Server performance metrics

Compare baseline metrics to observed metrics while troubleshooting performance issues

Identify differences between performance monitoring and logging tools

Monitor Azure SQL Database performance

Determine best practice use cases for extended events

Distinguish between Extended Events targets

Compare the impact of Extended Events and SQL Trace

Define differences between Extended Events Packages, Targets, Actions, and Sessions