Indexing, Performance Optimization & Functions in SQL Server

This course is part of Microsoft SQL Server Professional Certificate

Instructor: Microsoft

Access provided by Camara de Comercio Exterior

3,628 already enrolled

4 modules

Gain insight into a topic and learn the fundamentals.

15 reviews

Beginner level

Recommended experience

2 weeks to complete

at 10 hours a week

Flexible schedule

Learn at your own pace

4 modules

Gain insight into a topic and learn the fundamentals.

15 reviews

Beginner level

Recommended experience

2 weeks to complete

at 10 hours a week

Flexible schedule

Learn at your own pace

Skills you'll gain

Tools you'll learn

Details to know

Shareable certificate

Add to your LinkedIn profile

Assessments

9 assignments

Taught in English

See how employees at top companies are mastering in-demand skills

Learn more about Coursera for Business

logos of Petrobras, TATA, Danone, Capgemini, P&G and L'Oreal

Build your Data Analysis expertise

This course is part of the Microsoft SQL Server Professional Certificate

When you enroll in this course, you'll also be enrolled in this Professional Certificate.

Learn new concepts from industry experts
Gain a foundational understanding of a subject or tool
Develop job-relevant skills with hands-on projects
Earn a shareable career certificate from Microsoft

There are 4 modules in this course

Learn the techniques that make SQL Server databases perform at their best. This course focuses on database performance tuning through proper indexing strategies, query optimization techniques, and execution plan analysis. You'll learn to identify bottlenecks using SQL Server's performance tools, implement appropriate index types, and rewrite inefficient queries. The course also covers stored procedures, user-defined functions, and views for encapsulating complex logic and improving maintainability. Through hands-on exercises, you'll analyze real-world performance scenarios and implement solutions that dramatically improve query response times. The integration of AI-assisted optimization techniques shows how modern tools can complement traditional performance tuning methods. By course completion, you'll be able to transform slow-running queries into high-performance database operations.

In this module, you will develop a strong foundation in SQL Server indexing and its role in database performance. You will review the significance of indexing, design and create different types of indexes for various data structures and queries, and analyze how indexing affects database performance, including fragmentation, maintenance, and query efficiency.

What's included

4 videos9 readings2 assignments

4 videosTotal 18 minutes

Welcome to Indexing, Performance, Optimization, and Functions in Microsoft SQL Server2 minutes
Insights on Indexing3 minutes
Syntax for Index Creation6 minutes
Creating and Managing Indexes in SQL Server7 minutes

9 readingsTotal 240 minutes

Course Structure and Learning Outcomes5 minutes
Course Accessibility Information10 minutes
Setting up SQL Server and SSMS10 minutes
Setting up the TechHealth Database10 minutes
Clustered vs. Non-Clustered Indexes10 minutes
DiscountTech Data Code Files5 minutes
Exercise on Creating Indexes for TechHealth Analytics' Tables90 minutes
Designing Indexes for Optimal Query Speed10 minutes
Optimize Indexing for TechHealth's Customer Retention Data90 minutes

2 assignmentsTotal 45 minutes

Indexing Fundamentals15 minutes
Effective Index Foundations30 minutes

In this module, you will learn how to improve SQL Server performance by interpreting and optimizing query execution plans. You will identify performance bottlenecks and inefficiencies, apply advanced optimization techniques using execution plan analysis, and enhance query performance through efficient query design and execution strategies for large datasets.

What's included

3 videos4 readings2 assignments

3 videosTotal 13 minutes

The Role of Query Execution Plans in a Fast-Moving Company2 minutes
Exploring Execution Plan Variations6 minutes
Resolving Execution Plan Inefficiencies5 minutes

4 readingsTotal 155 minutes

Navigating SQL Server Execution Plans10 minutes
Quick Assessment of TechHealth Execution Plans45 minutes
Recognizing and Solving Execution Plan Patterns10 minutes
Troubleshooting TechHealth Data with Execution Plans90 minutes

2 assignmentsTotal 60 minutes

Understanding Execution Plans30 minutes
Execution Plan Problem Patterns30 minutes

In this module, you will learn how to automate and optimize database operations using reusable SQL Server components. You will create and manage stored procedures, design and implement user-defined functions (UDFs), assess their impact on database performance, and construct indexed views and triggers to improve data retrieval and system efficiency.

What's included

4 videos6 readings3 assignments

4 videosTotal 22 minutes

Automating Workflows with Stored Procedures3 minutes
Hands-On with Stored Procedures in SQL Server7 minutes
Building and Testing User-Defined Functions6 minutes
Implementing Triggers and Indexed Views for Faster Access7 minutes

6 readingsTotal 225 minutes

Creating and Invoking Stored Procedures10 minutes
Develop a Stored Procedure for TechHealth Analytics' Data Sync45 minutes
Understanding User-Defined Functions in SQL Server10 minutes
Function Creation for TechHealth Analytics' Wellness Score60 minutes
Working with Triggers and Indexed Views10 minutes
Create an Indexed View for TechHealth Analytics' Multinational Sales Data90 minutes

3 assignmentsTotal 90 minutes

Stored Procedures Essentials30 minutes
Function Implementation Strategies30 minutes
Advanced Data Manipulation30 minutes

In this module, you will explore techniques for monitoring and optimizing SQL Server performance using Dynamic Management Views (DMVs) and AI-assisted tools. You will utilize DMVs to monitor system performance, identify and address resource-intensive queries, and implement AI-driven solutions using GitHub Copilot to support continuous performance optimization and system-wide improvements.