Developing SQL Databases (20762) Cardiff Training Course

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

This course includes:

• Training from a Microsoft Certified Trainer (MCT)
• Computer / Laptop to use for the duration of the course
• Microsoft official courseware
• Online labs (if available)
• Refreshments