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

MP4 | Video: AVC 1280x720 | Audio: AAC 44KHz 2ch | Duration: 10 Hours | 18.1 GB

Genre: eLearning | Language: English

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