Introduction to Database Development
Before beginning to work with Microsoft SQL Server in either a development or an administration role,
it is important to understand the scope of the SQL Server platform. In particular, it is useful to understand
that SQL Server is not just
a database engine, it is a complete platform for managing enterprise data.
SQL Server provides a strong data platform for all sizes of organizations, in addition to a comprehensive set of
tools to make development easier,
and more robust.
In this module you will also learn about the common tools used to develop SQL Server solutions: SQL Server
Management Studio (SSMS), Azure Data Studio and Visual Studio.
- Introduction to the SQL Server Platform
- Working with SQL Server Management Studio, Azure Data Studio and Visual Studio
- Configuring SQL Server Services
- LAB: Working with SQL Server Management Studio, Azure Data Studio, SQLCMD and Visual Studio
Designing and Implementing Tables
In a relational database management system (RDBMS), user and system data are stored in tables. Each table
consists of a set of rows
that describe entities and a set of columns that hold the attributes of an entity. For example, a Customer table
might have columns such as
CustomerName and CreditLimit, and a row for each customer. Each of these columns have a specific data type.
Tables are contained within schemas
that are very similar in concept to folders that contain files in the operating system.
- Using Data Types
- Working with Character Data
- Designing Tables
- Working with Schemas
- Creating and Altering Tables
- 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â€”the success or failure of an organization or a business venture could depend on it. Ensuring data
integrity is a critical step in maintaining high-quality data.
You should enforce data integrity at all levels of an application from first entry or collection through
storage. SQL Server provides a range of features to simplify the job, one of which are constraints, discussed in
- Enforcing Data Integrity
- Implementing default constraints
- Primary keys, unique constraints, and check constraints
- Implementing Referential Integrity with foreign keys
- Cascading options on foreign keys
- Lab: Using Data Integrity Through Constraints
Introduction to Indexes
An index is a collection of pages associated with a table. Indexes are used to improve the performance of
queries or enforce uniqueness. Before learning to implement indexes, it is helpful to understand how they work,
how effective different data types are when used within indexes, and how indexes can be constructed from
multiple columns. This module discusses table structures that do not have indexes, and the different index types
available in SQL Server.
- Core Indexing Concepts
- Data Types and Indexes
- Heaps, Clustered, and Nonclustered Indexes
- Single Column and Composite Indexes
- Lab: Implementing Indexes
Designing Optimized Index Strategies
Indexes play an important role in enabling SQL Server to retrieve data from a database quickly and efficiently.
This module discusses advanced index topics including covering indexes, the INCLUDE clause, query hints, padding
and fill factor, statistics, as well as using DMVs to inspect these indexes.
- Index Strategies
- Managing Indexes
- Execution Plans
- The Database Engine Tuning Advisor
- Designing effective covering indexes
- Lab: Optimizing Indexes
Advanced Table Designs
The physical design of a database can have a significant impact on the ability of the database to meet the
storage and performance
requirements set out by the stakeholders. Designing a physical database implementation includes planning the
file groups, how to use
partitioning to manage large tables, and using compression to improve storage and performance. Temporal tables
offer a straightforward solution to collecting changes to your data.
- Partitioning Data
- Compressing Data
- Temporal Tables
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 highlights the benefits of using these indexes on
large datasets, and the considerations needed to use columnstore indexes
effectively in your solutions.
- Introduction to Columnstore Indexes
- Clustered Columnstore index versus non-clustered Columnstore index
- Row groups and segments
- Creating Columnstore Indexes
- Rebuilding Columnstore indexes
Designing and Implementing Views
This module describes the design and implementation of views. A view is a special type of queryâ€”one that is
stored and can be used in other queries-just like a table. With a view, only the query definition is stored
on in the database, not the result set. The only exception to this is indexed views, when the result set is also stored
in the database, just like a table.
Views simplify the design of a database by providing a layer of abstraction and hiding the complexity of
table joins. Views are also a way of securing your data by giving users permissions to use a view, without
giving them permissions to the underlying objects. This means data can be kept private and can only be
viewed by appropriate users.
- 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 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
- 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. Rather than having to repeat
the function logic in many places, code can call the function. This makes code more maintainable, and easier
In this module, you will learn to design and implement user-defined functions (UDFs) that enforce business
rules or data consistency. You will also learn how to modify and maintain existing 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
Responding to Data Manipulation via Triggers
Data Manipulation Language (DML) triggers are powerful tools that you can use to enforce domain, entity,
referential data integrity and business logic. The enforcement of integrity helps you to build reliable
applications. In this module, you will learn what DML triggers are, how they enforce data integrity, the
different types of triggers that are available to you, and how to define them in your database.
- Designing DML Triggers
- Implementing DML Triggers
- Advanced Trigger Concepts
- Lab: Responding to Data Manipulation by Using Triggers
Using In-Memory Tables
Microsoft SQL Server supports in-memory online transaction processing (OLTP)
functionality features to improve the performance of OLTP workloads. Memory-optimized tables are
primarily stored in memory, which provides the improved performance by reducing hard disk access.
Natively compiled stored procedures further improve performance over traditional interpreted Transact-SQL.
- Memory-Optimized Tables
- Natively Compiled Stored Procedures
SQL Server Concurrency
Concurrency control is a critical feature of multi-user database systems; it allows
data to remain consistent when many users are modifying data at the same time. This module covers the
implementation of concurrency in Microsoft SQL Server. You will learn about how SQL Server implements
concurrency controls, and the different ways you can configure and work with concurrency settings.
- Concurrency and Transactions
- Locking Internals
- Lab: SQL Server Concurrency
Performance and Monitoring
This module looks at how to measure and monitor the performance of your SQL Server
databases. The first two lessons look at SQL Server Extended Events, a flexible, lightweight event-handling
system built into the Microsoft SQL Server Database Engine. These lessons focus on the architectural
concepts, troubleshooting strategies and usage scenarios.
- Extended Events
- Working with extended Events
- Live Query Statistics
- Optimize Database File Configuration