Developing and optimizing SQL Server databases

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

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

Advanced Table Designs

Designing a physical database implementation includes planning the filegroups, how to use partitioning to manage large tables, and using compression to improve storage and performance. Temporal tables are a new feature in SQL Server and offer a straightforward solution to collecting changes to your data.

• Partitioning Data
• Compressing Data
• Temporal 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 Integrity.

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

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

Advanced 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
• Query Store

Columnstore Indexes

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

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

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

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

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

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

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

SQL Server concurrency

When multiple users are accessing data in parallel, this could lead to data inconsistencies. SQL Server protects against this with transactions.

• Concurrency and Transactions
• Locking Internals

Performance and Monitoring

This module looks at how to measure and monitor the performance of your SQL Server databases.

• Working with extended Events
• Live Query Statistics
• Metrics