Introduction to Database Development
This module introduces database development and the key tasks that a database developer
would typically perform. Also the most common tools (Management Studio and SQL Server Data Tools within Visual
Studio) are introduced.
- Introduction to the SQL Server Platform
- Working with SQL Server Tools
- Configuring SQL Server Services
- LAB: Introduction to Database Development
Designing and Implementing Tables
Tables are the basis of every database. This module explains how to design tables (normalization), and how
create and alter tables with both the graphical designers as well as with T-SQL statements. Also it focusses on
working with schemas: You're not going to put everything in the dbo schema, are you?
- Designing Tables
- Working with Schemas
- Creating and Altering Tables
- LAB: Designing and Implementing Tables
Ensuring Data Integrity through Constraints
This module explains how to enforce data integrity, and implement domain integrity
to maintain high quality data. Also it focusses on implementing Entity and Referential
- Enforcing Data Integrity
- Implementing Domain Integrity
- Implementing Entity and Referential Integrity
- LAB: Ensuring Data Integrity through Constraints
Introduction to Indexing
Indexes speed up SQL queries, but they can slow down data insertion. Because of this a compromise needs to be
found. In this module you will learn about the 3 most commonly used data structures to store SQL Server data
(heap, clustered index and non-clustered index) and the consequences they have on performance.
- Core Indexing Concepts
- Single Column and Composite Indexes
- SQL Server Table Structures
- Working with Clustered Indexes
- Working with Non-Clustered Indexes
- LAB: Introduction to Indexing
This module dives a bit deeper in the indexing topic: how does SQL Server decides which indexes to use, and how
can we monitor this.
- Execution Plan Core Concepts
- Common Execution Plan Elements
- Working with Execution Plans
- Designing Effective Non-clustered Indexes
- Performance Monitoring
- LAB: Advanced Indexing
Since SQL Server 2012 a new type of index has been added to SQL Server. This columnstore index is mainly
beneficial for queries which retrieve many rows but only a few columns. In this module you will learn about the
different types of ColumnStore indexes and how they differ from the traditional indexes.
- Columnstore Indexes
- Best Practices for Columnstore Indexes
- LAB: Working with Columnstore Indexes
Designing and Implementing Views
Abstraction layers shield applications from changes in a source system. In the same way you can create an
abstraction layer on top of your tables, such that application developers do not need to worry when you want to
redesign your tables. Views are one of the abstraction mechanisms in SQL Server. This module illustrates how to
create them, their strong points and their limitations.
- Introduction to Views
- Creating and Managing Views
- Performance Considerations for Views
- LAB: Designing and Implementing Views
Designing and Implementing Stored Procedures
This module describes the potential advantages of the use of stored procedures along
with guidelines on creating them.
- Introduction to Stored Procedures
- Working With Stored Procedures
- Implementing Parameterized Stored Procedures
- Controlling Execution Context
- LAB: Designing and Implementing Stored Procedures
Designing and Implementing User-Defined Functions
User-defined functions allow for easy code-reuse amongst different queries. SQL Server distinguishes 3 different
types of user-defined functions, all of which are discussed in this module.
- Overview of Functions
- Designing and Implementing Scalar Functions
- Designing and Implementing Table-Valued Functions
- Implementation Considerations for Functions
- Alternatives to Functions
- LAB: Designing and Implementing User-Defined Functions
Responding to Data Manipulation via Triggers
Triggers allow you to run arbitrary T-SQL code when data is inserted, updated or deleted in your database. This
module shows you how to create both AFTER triggers and INSTEAD OF triggers.
- Designing DML Triggers
- Implementing DML Triggers
- Advanced Trigger Concepts
- LAB: Responding to Data Manipulation via Triggers
Using In-Memory Tables
This module covers the creation of in-memory tables and native stored procedures
and discusses the advantages and disadvantages of using in-memory tables.
- Memory-Optimized Tables
- Native Stored Procedures
- LAB: Using In-Memory Tables
Implementing Managed Code in SQL Server
T-SQL is not an advanced programming language: It lacks a large library of useful functions, the code is not
compiled but interpreted, it has no inheritance or advanced error handling, ... . To overcome these limitations
stored procedures, triggers and functions can be written in Managed .Net code (C# and VB.Net) as well. This
module discusses the advantages and disadvantages of this, as well as how to get started with Managed code. It
does not provide the basics of .Net coding itself.
- Introduction to SQL CLR Integration
- Importing and Configuring Assemblies
- Implementing SQL CLR Integration
- LAB: sImplementing Managed Code in SQL Server
Optional â€“ Storing and Querying XML Data in SQL Server
SQL Server provides a powerful platform for developing rich applications for semi-structured data management.
Support for XML is integrated into all the components in SQL Server
- Introduction to XML and XML Schemas
- Storing XML Data and XML Schemas in SQL Server
- Implementing XML Indexes
- Using the Transact-SQL FOR XML Statement
- Getting Started with XQuery
- Shredding XML
- LAB: Storing and Querying XML Data in SQL Server
Working with Spatial Data in SQL Server
- Introduction to Spatial Data
- Working with Spatial Data Types in SQL Server
- Using Spatial Data in Applications
- LAB: Working with Spatial Data in SQL Server
Incorporating Data Files into Databases
This module describes the storage of data files in a database in Microsoft SQL Server, how to plan for storing this type of data, and the use of full-text indexes to query the data.
- Considerations for Working with Data Files in SQL Server
- Implementing FILESTREAM and FileTables
- Searching Data Files
- LAB: Implementing a Solution for Storing Data Files
This instructor-led course introduces SQL Server 2019 (or earlier) and describes logical
table design, indexing and query plans. It also focusses on the creation of database
objects including views, stored procedures, along with parameters, and functions.
Other common aspects of procedure coding, such as indexes, concurrency, error handling,
and triggers are also covered in this course. Also this course helps you prepare
for the Exam 70-464.
Most skills learned in this course are also applicable when developing Azure SQL databases.