Database
June 28, 2024
This blog explores the differences between Temp Tables and Common Table Expressions (CTEs).
Temp Tables are temporary tables that are created in the database at runtime and are automatically dropped when the session ends.
They are useful for storing intermediate results that need to be accessed multiple times within a session.
Temp Tables are created using the CREATE TABLE statement and can be indexed for faster access.
They are stored in the tempdb database in SQL Server and as mentioned above, can be accessed by multiple users within the same session.
Also - Temp Tables can be used to store large amounts of data that need to be processed in stages.
Example of creating a Temp Table:
CREATE TABLE #temptable (customerid INT NOT NULL PRIMARY KEY, lastorderdate DATETIME NULL);
INSERT INTO #temptable
SELECT customerid, MAX(orderdate) AS lastorderdate
FROM sales.SalesOrderHeader
GROUP BY customerid;
CTEs are temporary result sets that are defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
They are useful for simplifying complex queries by breaking them down into smaller, more manageable parts.
CTEs are defined using the WITH keyword and can be referenced multiple times within the same query.
They are not stored in the database and are only available for the duration of the query execution.
CTEs can be recursive, allowing them to reference themselves in the query definition.
Example of using a CTE:
WITH t (customerid, lastorderdate) AS
(
SELECT customerid, MAX(orderdate)
FROM sales.SalesOrderHeader
GROUP BY customerid
)
SELECT *
FROM sales.SalesOrderHeader soh
INNER JOIN t ON soh.customerid = t.customerid AND soh.orderdate = t.lastorderdate;
Profiler results show that CTEs slightly outperform both temporary tables and table variables in terms of overall duration.
CTEs use less CPU and perform fewer reads compared to the other options.
However, if you have a very large result set or need to refer to it multiple times, a temporary table is preferred.
Remember that CTEs are not valid beyond the specific query where they are defined.
In summary, choose a temporary table when reusing data or dealing with large result sets, and use a CTE for readability or recursive queries.
Keep in mind the scope and limitations of each approach!
Copyright 2024