Developing SQL Databases

Course Code: 5036

Overview
Schedule Classes
What you'll learn
Outline
Prerequisites
Who should attend

Overview

This five-day course equips participants with the knowledge and skills essential for developing SQL databases. The course discusses how participants can use SQL Server product features and tools related to developing a database. After completing the course, participants will be skilled in implementation of a SQL Server database.

Schedule Classes

Looking for more sessions of this class?

Course Delivery

This course is available in the following formats:

Live Classroom
Duration: 5 days

Live Virtual Classroom
Duration: 5 days

What You'll learn

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

Outline

Module 1: Introduction to database development

Introduction to the SQL Server Platform
SQL Server Database Development Tasks

Module 2: 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

Module 3: Advanced table designs

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

Module 4: 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

Module 5: Introduction to Indexes

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

Module 6: 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

Module 7: Columnstore indexes

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

Module 8: 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

Module 9: Designing and implementing stored procedures

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

Module 10: 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

Module 11: 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

Module 12: 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

Module 13: 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

Module 14: 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

Module 15: 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

Module 16: 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 Columns
- Enabling and Using File Tables
- Using a Full-Text Index

Module 17: SQL Server concurrency

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

Module 18: Performance and monitoring

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

Prerequisites

For this course, participants need to have basic knowledge of Microsoft Windows operating system and its core functionality, as well as working knowledge of Transact-SQL and relational databases.