课程

Improving Query Performance in SQL Server

中级技能水平

更新时间 2023年2月

In this course, students will learn to write queries that are both efficient and easy to read and understand.

免费开始课程

SQLProgramming4 小时16 视频58 练习4,450 经验值24,819成就声明

创建您的免费帐户

或

继续操作即表示您接受我们的《使用条款》和《隐私政策》，并同意您的数据存储在美国。

深受数千家公司学习者的喜爱

培训2人或更多？

试用DataCamp for Business

课程描述

A mission critical assignment is depending on your SQL coding skills. You’ve been given some code to fix. It is giving the results you need but it’s running too slow, and it’s poorly formatted making it hard to read. The deadline is tomorrow. You’ll need to reformat the code and try different methods to improve performance. The pressure is on!!! In this course we’ll be using SQL on real world datasets, from sports and geoscience, to look at good coding practices and different ways how we can can improve the performance of queries to achieve the same outcome.

先决条件

Intermediate SQL Server

1

Introduction, Review and The Order of Things

In this chapter, students will learn how SQL code formatting, commenting, and aliasing is used to make queries easy to read and understand. Students will also be introduced to query processing order in the database versus the order of the SQL syntax in a query.

Introduction

50 经验值

Formatting - player BMI

100 经验值

Commenting - player BMI

100 经验值

Commenting - how many Kiwis in the NBA?

100 经验值

50 经验值

Ambiguous column names

50 经验值

Aliasing - team BMI

100 经验值

Query order

50 经验值

Processing order

50 经验值

Syntax order - New Zealand earthquakes

100 经验值

Syntax order - Japan earthquakes

100 经验值

Syntax order - very large earthquakes

100 经验值

2

Filtering and Data Interrogation

This chapter introduces filtering with WHERE and HAVING and some best practices for how (and how not) to use these keywords. Next, it explains the methods used to interrogate data and the effects these may have on performance. Finally, the chapter goes over the roles of DISTINCT() and UNION in removing duplicates and their potential effects on performance.

Filtering with WHERE

50 经验值

Column does not exist

100 经验值

Functions in WHERE

100 经验值

Test your knowledge of WHERE

50 经验值

Filtering with HAVING

50 经验值

Row filtering with HAVING

100 经验值

Filtering with WHERE and HAVING

100 经验值

Test your knowledge of HAVING

50 经验值

Interrogation after SELECT

50 经验值

SELECT what you need

100 经验值

Limit the rows with TOP

100 经验值

Should I use ORDER BY?

50 经验值

Managing duplicates

50 经验值

Remove duplicates with DISTINCT()

100 经验值

UNION and UNION ALL

100 经验值

UNION or DISTINCT()?

50 经验值

3

Sub-queries and presence or absence

This chapter is an introduction to sub-queries and their potential impacts on query performance. It also examines the different methods used to determine if the data in one table is present, or absent, in a related table.

Sub-queries

50 经验值

Uncorrelated sub-query

100 经验值

Correlated sub-query

100 经验值

Sub-query vs INNER JOIN

100 经验值

Presence and absence

50 经验值

100 经验值

100 经验值

Interrogating with INTERSECT

100 经验值

Alternative methods 1

50 经验值

IN and EXISTS

100 经验值

NOT IN and NOT EXISTS

100 经验值

NOT IN with IS NOT NULL

100 经验值

Alternative methods 2

50 经验值

100 经验值

Exclusive LEFT OUTER JOIN

100 经验值

Test your knowledge

50 经验值

4

Query performance tuning

Students are introduced to how STATISTICS TIME, STATISTICS IO, indexes, and executions plans can be used in SQL Server to help analyze and tune query performance.

Time statistics

50 经验值

STATISTICS TIME in queries

100 经验值

STATISTICS TIME results

50 经验值

Page read statistics

50 经验值

STATISTICS IO: Example 1

100 经验值

STATISTICS IO: Example 2

100 经验值

STATISTICS IO comparison

50 经验值

50 经验值

Test your knowledge of indexes

50 经验值

Clustered index

100 经验值

Execution plans

50 经验值

Sort operator in execution plans

100 经验值

Test your knowledge of execution plans

50 经验值

Query performance tuning: final notes

50 经验值

Improving Query Performance in SQL Server

课程完成

获得成就证明

将此证书添加到你的 LinkedIn 档案、简历或履历中
在社交媒体和绩效评估中分享立即注册

加入超过19百万学习者，今天就开始Improving Query Performance in SQL Server！

创建您的免费帐户

或

继续操作即表示您接受我们的《使用条款》和《隐私政策》，并同意您的数据存储在美国。

通过 DataCamp for Mobile 提升您的数据技能

随时随地通过我们的移动课程和每日 5 分钟编程挑战提升技能。