Developing and Optimizing SQL Server Databases

Introduction to Database Development

This chapter introduces Microsoft SQL Server as a complete enterprise data platform, not just a database engine. It also covers the core tools used to develop SQL Server solutions, including SQL Server Management Studio and Visual Studio (Code).

• Introduction to the SQL Server Platform
• Working with SQL Server Management Studio and Visual Studio
• Configuring SQL Server Services
• LAB: Working with SQL Server Management Studio, SQLCMD and Visual Studio (Code)

Relational Database Design Fundamentals

Understanding how relational databases are structured is essential for anyone who works with data. This module provides the conceptual foundation for designing reliable, consistent, and scalable data models.

• Understanding tables, entities, and data domains
• Operational vs. analytical database models
• The purpose and value of normalization
• First, Second, and Third Normal Form explained
• Surrogate keys vs. business keys
• LAB: Design a relational database for a training center booking system

Designing and Implementing Tables

In a relational database, data is stored in tables made up of rows and columns, where each column has a defined data type. Tables are organized within schemas, which function like folders in an operating system.

• SQL Server Data Types
• Data Types in SQL Server: Characters, Numerical Data, Dates and Times
• Nullability
• Working with Schemas
• Creating and Altering Tables
• Temporary Tables
• Querying Table Metadata
• LAB: Designing and Implementing Tables

Ensuring Data Integrity through Constraints

The quality of data in your database largely determines the usefulness and effectiveness of applications that rely on it. Ensuring data integrity is a critical step in maintaining high-quality data. SQL Server provides a range of features to simplify the job, one of which are constraints.

• Enforcing Data Integrity
• Implementing Default adn Check Constraints
• Primary and Foreign Keys and Unique Constraints
• Cascading Options on Foreign Keys
• Generating Column Values: Identity and Sequences
• LAB: Ensuring Data Integrity Through Constraints

Rowstore Indexes

Indexes are a key mechanism for improving SQL Server performance. This chapter explores the different types of indexes, their benefits and trade-offs, and how they influence data access and storage.

• Core Indexing Concepts
• What is a Heap?
• Forwarding Pointers
• What is a Clustered Index?
• Data Types and Indexes
• Index Fragmentation
• Composite Indexes
• What is a Non-Clustered Index?
• Covering Indexes
• Performance Considerations
• LAB: Performance Impact of Indexes

Designing Optimized Index Strategies

Indexes play an important role in enabling SQL Server to retrieve data from a database quickly and efficiently. However, monitoring your indexes and altering them when necessary is crucial. In this module, we study different tools to monitor the execution performance of your queries and discuss some important considerations.

• Execution Plans
• Performance Monitoring and Tuning
• Designing Effective Nonclustered Indexes
• Filtered Indexes
• LAB: Optimizing Indexes

Advanced Table Designs

As databases grow and data requirements evolve, it becomes increasingly difficult to manage large volumes of data efficiently. This module introduces SQL Server features such as partitioning and temporal tables to improve scalability, performance, and data tracking.

• Partitioning Data
• Temporal Tables
• LAB: Partitioning and Archiving your Data

Columnstore Indexes

Columnstore indexes are often used in data warehouse solutions, in which many rows are consulted, but only a subset of the columns are used in every query. This module explains their benefits on large datasets and the key considerations for using them effectively.

• Introduction to Columnstore Indexes
• Clustered Columnstore Indexes
• Row Groups and Segments
• Non-Clustered Columnstore Indexes
• Rebuilding Columnstore Indexes

Designing and Implementing Views

Views simplify the design of a database by providing a layer of abstraction and hiding the complexity of table joins. They are also a way of securing your data by giving users permissions to use a view, without giving them permissions to the underlying objects.

• Introduction to Views
• Creating and Managing Views
• Ownership Chains and Views
• Advanced Features: Updatable Views, Encryption and Schemabinding
• Performance Considerations for Views
• Indexed Views
• LAB: Designing and Implementing Views

Designing and Implementing Stored Procedures

This module describes the design and implementation of stored procedures. Stored procedures - in contrast to views - allow for parameterization, as well as code that modifies the database.

• Introduction to Stored Procedures
• Working with Stored Procedures
• Implementing Parameterized Stored Procedures
• Parameter Smiffing and Performance
• Controlling Execution Context
• LAB: Designing and Implementing Stored Procedures

Designing and Implementing User-Defined Functions

Functions are routines that you use to encapsulate frequently performed logic without having to repeat the function logic in many places. This makes code more maintainable, and easier to debug.

• Overview of Functions
• Scalar Functions
• Table-Valued Functions: Inline and Multistatement
• Performance Considerations and Execution Context
• Alternatives to Functions
• LAB: Designing and Implementing User-Defined Functions

Responding to Data Manipulation via Triggers

Data Manipulation Language (DML) triggers help enforce data integrity and business logic, enabling more reliable applications. This module introduces what triggers are, the types available, and how to define them in SQL Server.

• Designing DML Triggers
• Implementing DML Triggers
• Nested Triggers
• Considerations and Alternatives
• LAB: Responding to Data Manipulation by Using Triggers

Working with Transactions

Transactions ensure data consistency and reliability in SQL Server by grouping operations into logical units of work. They control when changes are committed or rolled back, helping protect data integrity in multi‑user environments.

• Transactions in SQL Server
• Auto-commit transactions
• Explicit transactions
• Implicit transactions
• Nested Transactions: Savepoints
• Transaction State
• LAB: Working with Transactions

SQL Server Concurrency

Concurrency control ensures data consistency when multiple users access and modify data simultaneously. This module explains how SQL Server manages concurrency through locking and isolation levels, and how these settings can be configured to balance consistency and performance.

• ACID Principle and Concurrency Problems
• Pessimistic and Optimistic Concurrency Control
• Locking Internals: Lock Modes, Lock Escalation, Lock Hints, Deadlocks, ...
• Transaction Isolation Levels
• LAB: SQL Server Concurrency

Performance and Monitoring

As workloads grow and systems evolve, gaining insight into SQL Server behavior becomes increasingly important. Monitoring and tuning activities help you understand how the system operates, diagnose issues, and keep database workloads running smoothly.

• Performance Tuning Overview
• Metrics
• Dynamic Management Views (DMVs)
• Performance Monitor
• Live Query Statistics
• Extended Events
• Optimize Database File Configuration
• Query Store