Download PDF
of this course

MS20762 - Developing SQL Databases (MS20762)

  • Overview
  • Who Should Attend
  • Certifications
  • Prerequisites
  • Objectives
  • Content
  • Schedule
Course Overview

Duration : 4 Days

This four-day instructor-led course 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.

Who Should Attend

  • This four-day instructor-led course 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 Certifications

This course is part of the following Certifications:

Prerequisites

  • Basic knowledge of the Microsoft Windows operating system and its core functionality.

  • Working knowledge of Transact-SQL.

  • Working knowledge of relational databases.

Course Objectives

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.

 

Course Content

Course Outline 

Module 1 : Introduction to Database Development 

This module is used to introduce the entire SQL platform and its major tools.It will ocver 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

Lab : SQL Server Database Development Tasks

After completing this module , you will be able to :

  • Describe the SQL Server platform
  • Use the SQL Server administration tools

 

Module 2 : Designing and Implementing Tables 

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

Lessons 

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

 

Module 3: Advanced Tables 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 cant be partitioned 
  • Describe how data cant be compressed
  • Desxcribe Temporal tables

Module 4 : Ensuring Data Integrity through Constraints

This module describes the deisgn and implementation of constraints.

Lessons 

  • Enforcing data integrity
  • Implementing 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 grity 
  • 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
  • Single Column and Composite indexes

Lab : Implementing Indexes

After completing this module , you would be able to:

  • Explaing 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 include covering indexes and the INCLUDE clasue , hints , padding / fillfactor , statisctics. It also execution plans and the DTE Lessons.

Lessons

  • Covering Indexes 
  • Managing Indexes
  • Execution Plans
  • Using the DTE

Lab : Designing Optimized Index Strategies 

After completing this module , you will be able to :

  • Choose and appropriate table structure 
  • Implement clustered indexes and heaps

Module 7 : Columnstore Indexes

This module introduces the Columnstore indexes

Lessons

  • Introduction to Columnstore indexes
  • Creating Columnstore indexes
  • Working 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 

Modules 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
  • Performance considerations for views

Module 9 : Designing and Implementation Stored Procedures 

This module describes the design and implementation of stored procedures 

Lessons

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

Lab : Designing and Implementin 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 able-valued. ( Also discusses where they can lead to performance issues).

Lessons

  • Overview of Functions
  • Designing and Implementing Table-Valued Functions
  • Implementation Consideration for 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 via Triggers

After completing this module , you will be able to :

  • Deisgn 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 exmaple the removal of transaction blocking,

Lessons

  • In-Memory Tables
  • Native Stored Procedures

Lab : In Memory OLTP

After completing this module , you will be able to :

  • Implement in-memory tables 
  • Implement native stored procedures

Module 13: Implementing Managed Code in SQL Server

This module describes the implementation of and target use-cases for SQL CLR integration

Lessons

  • Introduction to SQL CLR Integration
  • Importing and Configuring Assemblies
  • Implementing SQL CLR Integration

Lab : Implementing Managed Code in SQL Server 

After completing this module , you will be able to :

  • Describe SQL CLR Integration 
  • Detail appropriate use cases for SQL CLR Integratiobn
  • Implement SQL CLR code

Module 14 : Storing and Querying XML Data in SQL Server

This module covers the XML data type , schema collections , typed and un-typed columns and appropriate  use cases for XML in SQL Server.

Lessons 

  • Introduction to XML and XML Schemas
  • Storing XML Data and Schemas in SQL Server
  • Implementing the XML Data Type
  • Using the T-SQL FOR XML Statement
  • Getting Started with xQuery

Lab: Storing and Querying XML Data in SQL Server 

After completing this module , you will be able to :

  • Explain the purpose and structure of XML and XML schemas
  • Describe how XML data and schemas can be stored in SQL Server 
  • Implement the XML data type

Module 15 : Working with SQL Server Spatial Data

This module describes spatial data and how this data can be implemented within SQL Server.

Lesssons

  • Introduction to Spatial Data 
  • Working with SQL Server Spatial Data Types
  • Using Spatial Data in Applications

Lab : Working with SQL Server Spatial Data

After completing this module , you will be able to :

  • Describe how spatial data can be stored in SQL Server 
  • Use basic methods of the GEOMETRY and GEOGRAPHY data types
  • Query databases containing spatial data

Module 16 : Storing and Querying Blobs and Text Documents in SQL Server 

This module covers full text indexes and queries

Lessons

  • Considerations for BLOB Data
  • Working with FileStream
  • Using Full-Text Search

Lab: Storing and Querying Blobs and Text Documents in SQL Server 

After completing this module , you will be able to :

  • Describe the need for full-text indexing in SQL Server 
  • Implement a full-text index 
  • Perform basic full-text queries

Course ID: MS20762

4 Days Course
SGD 2800.00
 
Singapore

Show Schedule for 1 Month  3 Months  All 
Date Country Location Register
05 Jun 2018 - 08 Jun 2018 Singapore Singapore