SQL Server 70-762: Developing SQL Databases: Required Knowledge for SQL Server 2012 and 2014

Introduction

Module 1 Design and Implement Database Objects

Lesson 1: Design and Implement a Relational Database Schema
Learning objectives
1.1 Designing Tables and Schemas Based on Business Requirements
1.2 Lab: Building the Design
1.3 Improving the Design of Tables by Using Normalization
1.4 Lab: Implementing Normalization
1.5 Writing Table Create Statements
1.6 Lab: Creating Tables
1.7 Determining the Most Efficient Data Types to Use: Categories, Precedence, Conversion, Considerations, and Keys
1.8 Determining the Most Efficient Data Types to Use: Identity and Sequence
1.9 Determining the Most Efficient Data Types to Use: Computed Columns, Numeric, and Date/Time
1.10 Determining the Most Efficient Data Types to Use: Character, Unicode, Binary String, and Other Data Types
1.11 Lab: Reviewing Data Types and Their Applications
1.12 Extending Security to Data Types by Using Dynamic Data Masking
1.13 Lab: Using DDM Functions
Lesson 1 Summary

Lesson 2: Design and Implement Indexes
Learning objectives
2.1 Designing New Indexes Based on Provided Tables, Queries, or Plans
2.2 Lab: Implementing Indexes
2.3 Distinguishing Between Indexed Columns and Included Columns
2.4 Lab: Distinguishing Between Indexed Columns and Included Columns
2.5 Implementing Clustered Index Columns by Using Best Practices
2.6 Lab: Choosing the Clustered Index Columns Based on Best Practices
2.7 Recommending New Indexes Based on Query Plans
2.8 Lab: Analyzing Query Plans for Indexing Improvements
Lesson 2 Summary

Lesson 3: Design and Implement Views
Learning objectives
3.1 Designing a View Structure to Select Data Based on User or Business Requirements
3.2 Lab: Implementing a View Structure
3.3 Identifying the Steps Necessary to Design an Updatable View
3.4 Lab: Analyzing the Effects of DML Statements Using a VIEW Object
3.5 Implementing Partitioned Views
3.6 Lab: Configuring and Using Partitioned Views
3.7 Implementing Indexed Views
3.8 Lab: Configuring and Using Indexed Views
Lesson 3 Summary
Lesson 4: Implement Columnstore Indexes
Learning objectives
4.1 Determining Use Cases That Support the Use of Columnstore Indexes
4.2 Lab: Understanding the Conceptual Format of Columnstore Indexes
4.3 Identifying Proper Usage of Clustered and Non-clustered Columnstore Indexes
4.4 Lab: Using Clustered Columnstore Indexes in Dimensional Data Warehouse Structures
4.5 Lab: Using Non-clustered Columnstore Indexes in OLTP Tables to Support Analytics
4.6 Designing Standard Non-clustered Indexes in Conjunction with Clustered Columnstore Indexes
4.7 Lab: Adding Non-clustered Indexes to Clustered Columnstore Indexes
4.8 Implementing Columnstore Index Maintenance
4.9 Lab: Using the ALTER INDEX Statement
Lesson 4 Summary

Module 2 Implement Programmability Objects

Lesson 5: Ensure Data Integrity with Constraints
Learning objectives
5.1 Defining Table and Foreign Key Constraints to Enforce Business Rules
5.2 Lab: Using DEFAULT, UNIQUE, CHECK, and FOREIGN KEY Constraints
5.3 Writing Transact-SQL Statements to Add Constraints to Tables
5.4 Lab: Creating and Managing Constraints
5.5 Identifying Results of Data Manipulation Language (DML) Statements Given Existing Tables and Constraints
5.6 Lab: Writing INSERT, UPDATE, and DELETE Statements on Tables with Constraints
5.7 Identifying Proper Usage of PRIMARY KEY Constraints
5.8 Lab: Creating and Managing PRIMARY KEY Constraints
Lesson 5 Summary

Lesson 6: Create Stored Procedures
Learning objectives
6.1 Designing Stored Procedure Components and Structure Based on Business Requirements
6.2 Lab: Creating Stored Procedures
6.3 Implementing Input and Output Parameters
6.4 Lab: Creating and Calling Stored Procedures with Input and Output Parameters
6.5 Implementing Table-valued Parameters
6.6 Lab: Creating and Calling Stored Procedures Using Table-valued Parameters
6.7 Implementing Return Codes
6.8 Lab: Creating and Executing Stored Procedures with RETURN Statements
6.9 Streamlining Existing Stored Procedure Logic
6.10 Lab: Altering Stored Procedures to Streamline Logic
6.11 Implementing Error Handling and Transaction Control Logic Within Stored Procedures
6.12 Lab: Implementing Error Handling in Stored Procedures Using THROW, RAISERROR, and TRY…CATCH Statements
6.13 Lab: Using Transaction Control Logic Within Stored Procedures
Lesson 6 Summary

Lesson 7: Create Triggers and User-Defined Functions
Learning objectives
7.1 Designing Trigger Logic Based on Business Requirements
7.2 Lab: Creating and Implementing Triggers
7.3 Determining When to Use Data Manipulation Language (DML) Triggers, Data Definition Language (DDL) Triggers, or Logon Triggers
7.4 Lab: Implementing DML Triggers
7.5 Lab: Implementing DDL Triggers
7.6 Lab: Implementing Logon Triggers
7.7 Recognizing Results Based on Execution of AFTER Triggers
7.8 Recognizing Results Based on Execution of INSTEAD OF Triggers
7.9 Lab: Implementing AFTER and INSTEAD OF Triggers
7.10 Designing Scalar-valued and Table-valued User-defined Functions Based on Business Requirements
7.11 Lab: Creating and Implementing Scalar-valued User-defined Functions
7.12 Lab: Creating and Implementing Table-valued User-defined Functions
7.13 Identifying Differences Between Deterministic and Non-deterministic Functions
7.14 Lab: Implementing Deterministic and Non-deterministic Functions
Lesson 7 Summary

Module 3 Manage Database Concurrency

Lesson 8: Implement Transactions

Learning objectives
8.1 Identifying DML Statement Results Based on Transaction Behavior
8.2 Lab: Implementing and Exploring Transaction Behavior
8.3 Recognizing Differences Between and Identifying Usage of Explicit and Implicit Transactions
8.4 Lab: Implementing Transaction Control
8.5 Implementing Savepoints Within Transactions
8.6 Lab: Implementing SAVE TRANSACTION and ROLLBACK TRANSACTION
8.7 Determining the Role of Transactions in High-concurrency Databases
8.8 Lab: Implementing and Managing Locks
Lesson 8 Summary
Lesson 9: Manage Isolation Levels
Learning objectives
9.1 Identifying Differences Between Read Uncommitted, Read Committed, Repeatable Read, Serializable, and Snapshot Isolation Levels
9.2 Defining Results of Concurrent Queries Based on Isolation Level
9.3 Lab: Implementing Results of Concurrent Queries Using SET TRANSACTION ISOLATION LEVEL
9.4 Identifying the Resource and Performance Impact of Given Isolation Levels
9.5 Lab: Reviewing Resource and Performance Impact of Isolation Levels
Lesson 9 Summary

Lesson 10: Optimize Concurrency and Locking Behavior
Learning objectives
10.1 Troubleshooting Locking Issues
10.2 Lab: Using Dynamic Management Views (DMVs) to Obtain Lock Information
10.3 Identifying Lock Escalation Behaviors
10.4 Lab: Monitoring Lock Escalation
10.5 Capturing and Analyzing Deadlock Graphs
10.6 Lab: Using SQL Server Profiler and Extended Events Viewer to Capture Deadlock Information
10.7 Identifying Ways to Remediate Deadlocks
Lesson 10 Summary

Lesson 11: Implement Memory-Optimized Tables and Native Stored Procedures
Learning objectives
11.1 Defining Use Cases for Memory-Optimized Tables Versus Traditional Disk-based Tables
11.2 Optimizing Performance of In-Memory Tables by Changing Durability Settings
11.3 Lab: Creating and Configuring Memory-Optimized Tables
11.4 Determining Best Case Usage Scenarios for Natively Compiled Stored Procedures
11.5 Lab: Using Natively Compiled Stored Procedures
11.6 Enabling Collection of Execution Statistics for Natively Compiled Stored Procedures
11.7 Lab: Using System Stored Procedures to Collect Execution Statistics
Lesson 11 Summary

Module 4 Optimize Database Objects and SQL Infrastructure

Lesson 12: Optimize Statistics and Indexes
Learning objectives
12.1 Determining the Accuracy of Statistics and the Associated Impact to Query Plans and Performance
12.2 Lab: Using the DBCC SHOW_STATISTICS Command
12.3 Designing Statistics Maintenance Tasks
12.4 Lab: Using UPDATE STATISTICS
12.5 Using Dynamic Management Objects to Review Current Index Usage and Identify Missing Indexes
12.6 Lab: Implementing DMOs that Evaluate Index Usage
12.7 Consolidating Overlapping Indexes
12.8 Lab: Examining Overlappping Indexes
Lesson 12 Summary

Lesson 13: Analyze and Troubleshoot Query Plans
Learning objectives
13.1 Capturing Query Plans Using Extended Events and Traces
13.2 Lab: Using Extended Events to Capture Query Plans
13.3 Lab: Using SQL Server Profiler to Define a Trace
13.4 Identifying Poorly Performing Query Plan Operators
13.5 Lab: Understanding Query Plans
13.6 Creating Efficient Query Plans Using Query Store
13.7 Lab: Monitoring Performance Using the Query Store
13.8 Comparing Estimated and Actual Query Plans and Related Metadata
13.9 Lab: Comparing an Estimated and Actual Query Plan for a Given Query
13.10 Configuring Azure SQL Database Performance Insight
13.11 Lab: Using Azure SQL Database Performance Insight
Lesson 13 Summary

Lesson 14: Manage Performance for Database Instances
Learning objectives
14.1 Managing Database Workload in SQL Server
14.2 Lab: Using the SQL Server Resource Governor
14.3 Designing and Implementing Elastic Scale for Azure SQL Databases
14.4 Lab: Using Elastic Database Tools
14.5 Selecting an Appropriate Service Tier or Edition
14.6 Optimizing Database File and Tempdb Configuration
14.7 Optimizing Memory Configuration
14.8 Lab: Using Object Explorer Properties and T-SQL to Configure Memory
14.9 Monitoring and Diagnosing Scheduling and Wait Statistics Using Dynamic Management Objects
14.10 Lab: Using Wait Statistics DMVs
14.11 Troubleshooting and Analyzing Storage, IO, and Cache Issues
14.12 Lab: Using Performance Counters to Provide IO Information
14.13 Lab: Using Performance Counters to Monitor Memory
14.14 Lab: Using DMVs to Monitor Memory Usage
14.15 Monitoring Azure SQL Database Query Plans
Lesson 14 Summary

Lesson 15: Monitor and Trace SQL Server Baseline Performance Metrics
Learning objectives
15.1 Monitoring Operating System and SQL Server Performance Metrics
15.2 Lab: Using Performance Monitor (PerfMon)
15.3 Comparing Baseline Metrics to Observed Metrics While Troubleshooting Performance Issues
15.4 Lab: Configuring and Using Management Data Warehouse
15.5 Identifying Differences Between Performance Monitoring and Logging Tools, such as PerfMon and Dynamic Management Objects
15.6 Monitoring Azure SQL Database Performance
15.7 Lab: Using the Add Alert Rule Blade in the Azure Portal
15.8 Determining Best Practice Use Cases for Extended Events
15.9 Distinguishing Between Extended Events Targets
15.10 Comparing the Impact of Extended Events and SQL Trace
15.11 Defining Differences Between Extended Events Packages, Targets, Actions, and Sessions
Lesson 15 Summary