Common Table Expressions (CTEs) let you to create a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. The main points:

• Definition: A CTE is a named temporary result set that you define using a WITH clause. It can be referenced within the main query just like a regular table
• Usage: CTEs are particularly useful for breaking down complex queries into simpler parts, improving readability and maintainability. They can also be used for recursive queries

Example:

WITH avg_students AS (

    SELECT district_id, AVG(students) AS average_students
    FROM schools
    GROUP BY district_id
)

SELECT s.school_name, s.district_id, avg.average_students
FROM schools s
JOIN avg_students avg ON s.district_id = avg.district_id;

This example demonstrates how a CTE can be used to calculate the average number of students per district and then join this result with the schools table to display the required information

• Avoid Recursive CTEs for Large Datasets:
  • Recursive CTEs can be slow when dealing with large datasets. Consider alternative approaches like using iterative logic or temporary tables
• Minimise the Use of CTEs in Complex Queries:
  • For very complex queries, breaking down the logic into multiple steps using temporary tables or table variables can sometimes be more efficient than using nested CTEs
• Optimise Predicates and Joins:
  • Make sure the predicates and joins within the CTE are optimised. This includes using appropriate indexes and avoiding unnecessary columns in the SELECT statement
• Consider Query Hints:
  • In some cases, using query hints can help the SQL optimiser choose a better execution plan. However, this should be done with caution and thorough testing

Using CTEs

Let’s say you want to calculate the average number of students per district and then join this result with the schools table to display the required information. Using a CTE, the query would look like this:

WITH avg_students AS (
    SELECT district_id, AVG(students) AS average_students
    FROM schools
    GROUP BY district_id
)
SELECT s.school_name, s.district_id, avg.average_students
FROM schools s
JOIN avg_students avg ON s.district_id = avg.district_id;

Using Temporary Tables

To achieve the same result using temporary tables, you can follow these steps:

• Create a temporary table to store the average number of students per district.
• Insert the calculated averages into the temporary table.
• Join the temporary table with the schools table to get the desired result.

Here is how you can do it:

-- Step 1: Create a temporary table
CREATE TABLE #avg_students (
    district_id INT,
    average_students DECIMAL(10, 2)
);

-- Step 2: Insert the calculated averages into the temporary table
INSERT INTO #avg_students (district_id, average_students)
SELECT district_id, AVG(students) AS average_students
FROM schools
GROUP BY district_id;

-- Step 3: Join the temporary table with the schools table
SELECT s.school_name, s.district_id, avg.average_students
FROM schools s

Explanation

• Step 1: A temporary table #avg_students is created to store the average number of students per district.
• Step 2: The INSERT INTO statement is used to populate the temporary table with the calculated averages.
• Step 3: The temporary table is then joined with the schools table to get the desired result.
• Step 4: The temporary table is dropped to clean up resources.Using temporary tables can sometimes provide better performance, especially when dealing with large datasets or complex queries that need to be broken down into simpler steps.

From my SQL Server DBA handbook.