Improving Query Performance in SQL Server

Start Free Preview

Buying via our links supports this free directory · Affiliate disclosure.
Course facts
  • Payment: Free Preview (Paid Upgrade)
  • Learning style: Self-paced, start anytime
  • Format: Text & Interactive
  • Video access: Streaming Only
  • Before you start: Intermediate SQL Server
DataCamp details
  • Chapters: 4
Created byDataCamp
Available onDataCamp

Course Details
Learning Style

Start Anytime

Level

Intermediate

Study Mode

100% Online

Duration

Short (< 10 Hours)

Languages
Audio Language

English

Outcomes
Certificate

Certificate of Completion

Price & Terms
Pricing Model

Free Preview (Paid Upgrade)

Technical
Content Medium

Text & Interactive

Video Access

Streaming Only

Specification
Access

Subscription

SKU: improving-query-performance-sql-server Category: Tags: , , Brand:

Learn to make SQL Server queries easier to read and faster to run, using filtering choices, sub-query rewrites, indexes and execution plans.

At a glance
Duration4 hrs
LevelIntermediate
CertificateCertificate of Completion
LanguageEnglish
Taught byDean Smith
AccessSubscription
Add your review
Add to wishlistAdded to wishlistRemoved from wishlist 0
Add to compare

A SQL query can return the right answer and still run too slowly, or be laid out so badly that nobody else can follow it. Working in SQL Server on sports and geoscience data, you take queries like that apart. You tidy their layout and weigh different ways of filtering rows, removing duplicates and checking whether data exists in a related table. Then you measure each version with the server’s own timing and page-read statistics.

What You Will Achieve

  • Format, comment and alias a SQL query so colleagues can read and maintain it
  • Decide whether a filter belongs in WHERE or in HAVING, and avoid the common mistakes with each
  • Compare a sub-query with an INNER JOIN, and find rows missing from another table with NOT EXISTS or an exclusive LEFT OUTER JOIN
  • Compare two versions of a query using STATISTICS TIME, STATISTICS IO and its execution plan


Program Overview

Code Formatting and Processing Order

Starts with the habits that make a query easy to read and the order in which the database processes its clauses.

  • Formatting, commenting and aliasing
  • Ambiguous column names
  • Processing order versus syntax order

Filtering, Duplicates and Sub-queries

Looks at how filtering, data interrogation, duplicate removal and sub-queries affect speed, and compares ways of testing for presence or absence in a related table.

  • WHERE versus HAVING and functions in WHERE
  • Selecting only needed columns, TOP and ORDER BY
  • DISTINCT(), UNION and UNION ALL
  • Correlated and uncorrelated sub-queries, INTERSECT, EXCEPT, IN, EXISTS and joins

Measuring Query Performance in SQL Server

Introduces the SQL Server tools used to analyze a query and tune it.

  • STATISTICS TIME and STATISTICS IO
  • Indexes and clustered indexes
  • Execution plans and the sort operator

Certificate Awarded (Paid)


Job Outlook

These skills are used wherever SQL Server databases sit behind reports and applications, by people who write, review or maintain queries and need them to run faster and read clearly.


Our take

Good for: SQL Server users who already write working queries and want them to run faster and be easier for colleagues to read.

Keep in mind: The timing and page-read statistics and execution plans are taught as SQL Server tools, while the formatting, filtering and sub-query practices of the earlier chapters apply to other SQL databases too.

Bottom line: A focused rework of slow, messy SQL Server queries that pairs each rewrite with the statistics needed to check whether it helped.

Frequently Asked Questions

What data do the practice queries run on?

The exercises draw on sports and geoscience data, such as NBA player measurements used to calculate BMI and records of earthquakes in New Zealand and Japan, so each technique is practiced on real tables.

Which SQL Server tools measure query performance?

The last chapter uses STATISTICS TIME for timing and STATISTICS IO for page reads, then turns to indexes, including the clustered index, and to execution plans and their sort operator to analyze and tune a query.

How does the course handle finding rows missing from another table?

The sub-query chapter sets several methods side by side: INTERSECT and EXCEPT, IN and EXISTS with their NOT forms, NOT IN combined with IS NOT NULL, an INNER JOIN, and an exclusive LEFT OUTER JOIN, with attention to how each may affect performance.

User Reviews

0.0 out of 5
★★★★★
0
★★★★★
0
★★★★★
0
★★★★★
0
★★★★★
0

No reviews yet. Be the first to review this course.

There are no reviews yet.

Write your review

Reviews can only be posted by registered learners. Log in, then you will return here to post.

×

    Report this page
    Improving Query Performance in SQL Server
    Improving Query Performance in SQL Server
    LiveTalent
    Logo
    Compare items
    • Total (0)
    Compare
    0