Improving Query Performance in SQL Server
Learn to make SQL Server queries easier to read and faster to run, using filtering choices, sub-query rewrites, indexes and execution plans.
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.
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.


There are no reviews yet.