Developing and Optimizing SQL Server Databases

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 this module.

• 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

Indexes are used to improve the performance of queries or enforce uniqueness of columns. 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 tables stored as clustered indexes.

• Core Indexing Concepts
• What is a Heap?
• What is a Clustered Index?
• Data Types and Indexes
• Single-Column and Composite Indexes
• Indexes and Statistics
• Index Fragmentation
• LAB: Implementing Heaps and Clustered Indexes

Working with Nonclustered Indexes

On top of a Heap or Clustered Index you can create one or more secondary indexes that are called Nonclustered Indexes. Non-clustered indexes are great for optimizing queries that filter on columns that return only a small number of rows. You could say that they are ideal to find the needles in a haystack.

• What Is a Nonclustered Index?
• Nonclustered Indexes over Heaps
• Nonclustered Indexes over Clustered Indexes
• Creating Nonclustered Indexes
• Covered Nonclustered indexes
• Performance Impact of Lookups in Nonclustered Indexes
• LAB: Implementing Nonclustered 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

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 to debug. 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
• Metrics