SQL Server 2016: Concevoir des bases de données

À qui s'adresse la formation?

The primary audience for this course is IT Professionals who want to become skilled on SQL Server 2016 product features and technologies for implementing a database.

The secondary audiences for this course are individuals who are developers from other product platforms looking to become skilled in the implementation of a SQL Server 2016 database.

Durée

5,00 jour(s)

Langues(s) de prestation

Prochaine session

Contacter l'organisme

Le contenu de cette page n'est pas disponible en français

Prérequis

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

Objectifs

After completing this course, students will be able to:

Design and Implement Tables
Describe advanced table designs
Ensure Data Integrity through Constraints
Describe indexes, including Optimized and Columnstore indexes
Design and Implement Views
Design and Implement Stored Procedures
Design and Implement User Defined Functions
Respond to data manipulation using triggers
Design and Implement In-Memory Tables
Implement Managed Code in SQL Server
Store and Query XML Data
Work with Spatial Data
Store and Query Blobs and Text Documents

Contenu

Module 1: Introduction to Database Development

This module is used to introduce the entire SQL Server platform and its major tools. It will cover editions, versions, basics of network listeners, and concepts of services and service accounts.

Lessons

Introduction to the SQL Server Platform
SQL Server Database Development Tasks

After completing this module, you will be able to:

Describe the SQL Server platform
Use SQL Server administration tools

Module 2: Designing and Implementing Tables

This modules describes the design and implementation of tables. (Note: partitioned tables are not covered).

Lessons

Designing Tables
Data Types
Working with Schemas
Creating and Altering Tables

Lab: Designing and Implementing Tables

After completing this module, you will be able to:

Design Tables
Describe the various types of data
Be able to work with schemas
Be able to create and amend tables

Module 3: Advanced Table Designs

This module describes more advanced table designs.

Lessons

Partitioning Data
Compressing Data
Temporal Tables

Lab: Using Advanced Table Designs

After completing this module, you will be able to:

Describe how data can be partitioned
Describe how data can be compressed
Describe Temporal tables

Module 4: Ensuring Data Integrity through Constraints

This module describes the design and implementation of constraints.

Lessons

Enforcing Data Integrity
Implementing Data Domain Integrity
Implementing Entity and Referential Integrity

Lab: Ensuring Data Integrity through Constraints

After completing this module, you will be able to:

Describe how to enforce data integrity
Describe how to implement domain integrity
Describe how to implement entity and referential integrity

Module 5: Introduction to Indexes

This module describes the concept of an index and discusses selectivity, density and statistics. It covers appropriate data type choices and choices around composite index structures.

Lessons

Core Indexing Concepts
Data Types and Indexes
Heaps, Clustered, and Nonclustered Indexes
Single Column and Composite Indexes

Lab: Implementing Indexes

After completing this module, you will be able to:

Explain the need for indexes and describe the core concepts of index design
Choose appropriate data types for indexes
Design single column and composite indexes

Module 6: Designing Optimized Index Strategies

This module includes covering indexes and the INCLUDE clause, hints, padding / fillfactor, statistics. It also execution plans and the DTE Lessons.

Lessons

Index Strategies
Managing Indexes
Execution Plans
The Database Engine Tuning Advisor
Query Store

Lab: Optimizing Indexes

After completing this module, you will be able to:

Choose an appropriate table structure
Implement clustered indexes and heaps

Module 7: Columnstore Indexes

This module introduces Columnstore indexes.

Lessons

Introduction to Columnstore Indexes
Creating Columnstore Indexes
Working with Columnstore Indexes

Lab: Using Columnstore Indexes

After completing this module, you will be able to:

Describe suitable scenarios for Columnstore indexes
Create clustered and non-clustered Columnstore indexes
Describe considerations for updating non-clustered Columnstore indexed tables

Module 8: Designing and Implementing Views

This module describes the design and implementation of views.

Lessons

Introduction to Views
Creating and Managing Views
Performance Considerations for Views

Lab: Designing and Implementing Views

After completing this module, you will be able to:

Explain the concept of views
Design, Create and Alter Views
Create Partitioned Views

Module 9: Designing and Implementing Stored Procedures

This module describes the design and implementation of stored procedures.

Lessons

Introduction to Stored Procedures
Working with Stored Procedures
Implementing Parameterized Stored Procedures
Controlling Execution Context

Lab: Designing and Implementing Stored Procedures

After completing this module, you will be able to:

Design, Create and Alter Stored Procedures
Control the Execution Context of Stored Procedures
Implement Stored Procedures that use Parameters

Module 10: Designing and Implementing User-Defined Functions

This module describes the design and implementation of functions, both scalar and table-valued. (Also discusses where they can lead to performance issues).

Lessons

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

After completing this module, you will be able to:

Design, create and alter scalar functions
Design, create and alter table-valued functions
Describe the performance impacts of functions

Module 11: Responding to Data Manipulation via Triggers

This module describes the design and implementation of triggers.

Lessons

Designing DML Triggers
Implementing DML Triggers
Advanced Trigger Concepts

Lab: Responding to Data Manipulation by Using Triggers

After completing this module, you will be able to:

Design DML triggers
Implement DML triggers
Work with Nested and Recursive Triggers

Module 12: Using In-Memory Tables

This module covers the creation of in-memory tables and native stored procedures. Furthermore, advantages of in-memory tables are discussed, for example the removal of transaction blocking.

Lessons