20762-1 - Developing SQL Databases

Training for Your Group

Training On Demand

$999

Training for Individuals

$2975

Course Overview

This five-day MOC 20762 – Developing SQL Databases training class provides students with the knowledge and skills to develop a Microsoft SQL Server 2016 database. The course focuses on teaching individuals how to use SQL Server 2016 product features and tools related to developing a database.

Course Length: 5 days

Audience: IT Professionals who want to become skilled on SQL Server 2016 product features and technologies for implementing a database. Developers from other product platforms looking to become skilled in the implementation of a SQL Server 2016 database.

Prerequisites: Basic knowledge of the Microsoft Windows operating system and its core functionality. Working knowledge of Transact-SQL. Working knowledge of relational databases.

What You're Going To Learn

Learn to design and Implement Tables.
Learn to describe advanced table designs.
Learn to ensure Data Integrity through Constraints.
Learn to describe indexes, including Optimized and Columnstore indexes.
Learn to design and Implement Views.
Learn to design and Implement Stored Procedures.
Learn to design and Implement User Defined Functions.
Learn to respond to data manipulation using triggers.
Learn to design and Implement In-Memory Tables.
Learn to implement Managed Code in SQL Server.
Learn to store and Query XML Data.
Learn to work with Spatial Data.
Learn to store and Query Blobs and Text Documents.

Register for an Upcoming Date

Please call for information

This Course is Available On Demand

$ 999

90 Day Subscription

$ 1199

180 Day Subscription

Course Outline

Introduction to Database Development

Introduction to the SQL Server Platform
SQL Server Database Development Tasks

Designing and Implementing Tables

Designing Tables
Data Types
Working with Schemas
Creating and Altering Tables
Lab: Designing and Implementing Tables
Designing Tables
Creating Schemas
Creating Tables

Advanced Table Designs

Partitioning Data
Compressing Data
Temporal Tables
Lab: Using Advanced Table Designs
Partitioning Data
Compressing Data

Ensuring Data Integrity through Constraints

Enforcing Data Integrity
Implementing Data Domain Integrity
Implementing Entity and Referential Integrity
Lab: Using Data Integrity Through Constraints
Add Constraints
Test the Constraints

Introduction to Indexes

Core Indexing Concepts
Data Types and Indexes
Heaps, Clustered, and Nonclustered Indexes
Single Culumn and Composite Indexes
Lab: Implementing Indexes
Creating a Heap
Creating a Clustered Index
Creating a Covered Index

Designing Optimized Index Strategies

Index Strategies
Managing Indexes
Execution Plans
The Database Engine Tuning Advisor
Query Store
Lab: Optimizing Indexes
Using Query Store
Heaps and Clustered Indexes
Creating a Covered Index

Columnstore Indexes

Introduction to Columnstore Indexes
Creating Culumnstore Indexes
Working with Culumnstore Indexes
Lab: Using Culumnstore Indexes
Creating a Culumnstore Index
Create a Memory Optimized Culumnstore Table

Designing and Implementing Views

Introduction to Views
Creating and Managing Views
Performance Considerations for Views
Lab: Designing and Implementing Views
Creating Standard Views
Creating an Updateable view

Designing and Implementing Stored Procedures

Introduction to Stored Procedures
Working with Stored Procedures
Implementing Parameterized Stored Procedures
Contrulling Execution Context
Lab: Designing and Implementing Stored Procedures
Create Stored procedures
Create Parameterized Stored procedures
Changes Stored Procedure Execution Context

Designing and Implementing User-Defined Functions

Overview of Functions
Designing and Implementing Scalar Functions
Designing and Implementing Table-Valued Functions
Considerations for Implementing Functions
Alternatives to Functions
Lab: Designing and Implementing User-Defined Functions
Format Phone numbers
Modify an Existing Function

Responding to Data Manipulation via Triggers

Designing DML Triggers
Implementing DML Triggers
Advanced Trigger Concepts
Lab: Responding to Data Manipulation by Using Triggers
Create and Test the Audit Trigger
Improve the Audit Trigger

Using In-Memory Tables

Memory-Optimized Tables
Natively Compiled Stored Procedures
Lab: Using In-Memory Database Capabilities
Using Memory-Optimized Tables
Using Natively Compiled Stored procedures

Implementing Managed Code in SQL Server

Introduction to CLR Integration in SQL Server
Implementing and Publishing CLR Assemblies
Lab: Implementing Managed Code in SQL Server
Assessing Proposed CLR Code
Creating a Scalar-Valued CLR Function
Creating a Table Valued CLR Function

Storing and Querying XML Data in SQL Server

Introduction to XML and XML Schemas
Storing XML Data and Schemas in SQL Server
Implementing the XML Data Type
Using the Transact-SQL FOR XML Statement
Getting Started with XQuery
Shredding XML
Lab: Storing and Querying XML Data in SQL Server
Determining when to use XML
Testing XML Data Storage in Variables
Using XML Schemas
Using FOR XML Queries
Creating a Stored Procedure to Return XML

Storing and Querying Spatial Data in SQL Server

Introduction to Spatial Data
Working with SQL Server Spatial Data Types
Using Spatial Data in Applications
Lab: Working with SQL Server Spatial Data
Become Familiar with the Geometry Data Type
Add Spatial Data to an Existing Table
Find Nearby Locations

Storing and Querying BLOBs and Text Documents in SQL Server

Considerations for BLOB Data
Working with FILESTREAM
Using Full-Text Search
Lab: Storing and Querying BLOBs and Text Documents in SQL Server
Enabling and Using FILESTREAM Culumns
Enabling and Using File Tables
Using a Full-Text Index

SQL Server Concurrency

Concurrency and Transactions
Locking Internals
Lab: SQL Server Concurrency
Implement Snapshot Isulation
Implement Partition Level Locking

Performance and Monitoring

Extended Events
Working with extended Events
Live Query Statistics
Optimize Database File Configuration
Metrics
Lab: Monitoring, Tracing, and Baselining
Cullecting and Analyzing Data Using Extended Events
Implementing Baseline Methodulogy

What to Expect at LeapFox

Knowledgeable Instructors

Our instructors are certified professionals. They are trained on the latest features and how to get the most out of software programs.

Hands-on Labs

No boring lectures! Our courses are designed to give students lots of time to practice what they are learning with hands-on exercises and projects.

Certificate of Completion

Receive a certificate of completion at the end of every course.

Up-to-date Curriculum

Each course comes with a helpful and up-to-date ebook which will contain instruction and practice exercises.

Time Saving Tips N Tricks

In each course, your instructor will show you tips and tricks that will save you time and make you more efficient.

Friendly and Helpful Staff

Our staff is dedicated to your success. Each team member is trained to provide the absolute best customer service possible.

Satisfaction Guarantee

If you aren't 100% satisfied with your experience at LeapFox, simply let us know, and we will make it right.