Indexing, Performance Optimization & Functions in SQL Server
Ends soon! Keep adding new skills with 10,000+ programs for $239 (usually $399). Save now.
Indexing, Performance Optimization & Functions in SQL Server
This course is part of Microsoft SQL Server Professional Certificate
Instructor: Microsoft
3,199 already enrolled
Included with
Skills you'll gain
Tools you'll learn
Details to know
9 assignments
See how employees at top companies are mastering in-demand skills
Build your Data Analysis expertise
- 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.
This module provides you with a comprehensive understanding of the significance and function of indexing in SQL Server. You will be enabled to design and create various types of indexes tailored to different data structures and queries, ensuring optimal database performance. You will explore topics such as the differences between clustered and non-clustered indexes, as well as advanced indexing techniques like composite, covering, and filtered indexes.
What's included
4 videos9 readings2 assignments
4 videos•Total 18 minutes
- Welcome to Indexing, Performance, Optimization, and Functions in Microsoft SQL Server•2 minutes
- Insights on Indexing•3 minutes
- Syntax for Index Creation•6 minutes
- Creating and Managing Indexes in SQL Server•7 minutes
9 readings•Total 250 minutes
- Course Structure and Learning Outcomes•10 minutes
- Course Accessibility Information•10 minutes
- Setting up SQL Server and SSMS•10 minutes
- Setting up the TechHealth Database•10 minutes
- Clustered vs. Non-Clustered Indexes•10 minutes
- DiscountTech Data Code Files•10 minutes
- Exercise on Creating Indexes for TechHealth Analytics' Tables•90 minutes
- Designing Indexes for Optimal Query Speed•10 minutes
- Optimize Indexing for TechHealth's Customer Retention Data•90 minutes
2 assignments•Total 60 minutes
- Effective Index Foundations•30 minutes
- Indexing Fundamentals•30 minutes
This module empowers you with the skills to elevate database performance by mastering the art of interpreting and optimizing query execution plans. You will learn to identify performance bottlenecks and inefficiencies through detailed analysis of execution plan components, such as cost percentages and cardinality estimation errors. By applying advanced optimization techniques, you will enhance your ability to design intelligent queries and execution strategies, particularly when dealing with large datasets.
What's included
3 videos4 readings2 assignments
3 videos•Total 13 minutes
- The Role of Query Execution Plans in a Fast-Moving Company•2 minutes
- Exploring Execution Plan Variations•6 minutes
- Resolving Execution Plan Inefficiencies•5 minutes
4 readings•Total 155 minutes
- Navigating SQL Server Execution Plans•10 minutes
- Quick Assessment of TechHealth Execution Plans•45 minutes
- Recognizing and Solving Execution Plan Patterns•10 minutes
- Troubleshooting TechHealth Data with Execution Plans•90 minutes
2 assignments•Total 60 minutes
- Execution Plan Problem Patterns•30 minutes
- Understanding Execution Plans•30 minutes
This module equips you with the skills needed to create and manage stored procedures for optimizing database operations. You will learn to design and implement user-defined functions (UDFs) to create modular and reusable code, enhancing the efficiency of your SQL Server processes. By the end of this module, you will be able to assess the performance impact of both stored procedures and UDFs, ensuring they contribute positively to your system’s performance. Additionally, you will gain the ability to construct and manage indexed views and triggers to further enhance data manipulation and retrieval speeds within your databases.
What's included
4 videos6 readings3 assignments
4 videos•Total 22 minutes
- Automating Workflows with Stored Procedures•3 minutes
- Hands-On with Stored Procedures in SQL Server•7 minutes
- Building and Testing User-Defined Functions•6 minutes
- Implementing Triggers and Indexed Views for Faster Access•7 minutes
6 readings•Total 225 minutes
- Creating and Invoking Stored Procedures•10 minutes
- Develop a Stored Procedure for TechHealth Analytics' Data Sync•45 minutes
- Understanding User-Defined Functions in SQL Server•10 minutes
- Function Creation for TechHealth Analytics' Wellness Score•60 minutes
- Working with Triggers and Indexed Views•10 minutes
- Create an Indexed View for TechHealth Analytics' Multinational Sales Data•90 minutes
3 assignments•Total 90 minutes
- Advanced Data Manipulation•30 minutes
- Stored Procedures Essentials•30 minutes
- Function Implementation Strategies•30 minutes
In this module, you will master the use of Dynamic Management Views (DMVs) to monitor and optimize SQL Server performance effectively. You will be empowered to enhance query performance by pinpointing and addressing high resource-consuming areas and integrate AI-driven solutions for continuous optimization using CoPilot. Additionally, by leveraging AI tools integrated into VS Code, you'll learn to automate and improve performance optimization, ensuring you have the skills to maintain optimal system-wide enhancements.
What's included
3 videos5 readings2 assignments1 ungraded lab
3 videos•Total 14 minutes
- Leveraging Dynamic Management Views for Superior Operational Insight•2 minutes
- Efficient Monitoring with DMVs at DiscountTech•5 minutes
- CoPilot for SQL Server Optimization•7 minutes
5 readings•Total 130 minutes
- Advanced Filtering Techniques with Dynamic Management Views (DMVs)•10 minutes
- Apply DMV Techniques for Query Improvement at TechHealth•90 minutes
- Harnessing AI in SQL Performance Optimization•10 minutes
- Accessing GitHub for Copilot•10 minutes
- Wrapping Up and Looking Ahead•10 minutes
2 assignments•Total 60 minutes
- AI Tools for SQL Server Performance•30 minutes
- Advanced DMV Applications•30 minutes
1 ungraded lab•Total 60 minutes
- CoPilot-Assisted Performance Enhancement at TechHealth•60 minutes
Earn a career certificate
Add this credential to your LinkedIn profile, resume, or CV. Share it on social media and in your performance review.
Instructor
Explore more from Data Analysis
- Status: Free Trial
Course
- Status: Free Trial
Course
Guided Project
- Status: Preview
Course
Why people choose Coursera for their career
Frequently asked questions
To access the course materials, assignments and to earn a Certificate, you will need to purchase the Certificate experience when you enroll in a course. You can try a Free Trial instead, or apply for Financial Aid. The course may offer 'Full Course, No Certificate' instead. This option lets you see all course materials, submit required assessments, and get a final grade. This also means that you will not be able to purchase a Certificate experience.
When you enroll in the course, you get access to all of the courses in the Certificate, and you earn a certificate when you complete the work. Your electronic Certificate will be added to your Accomplishments page - from there, you can print your Certificate or add it to your LinkedIn profile.
More questions
Financial aid available,