What is CTE and Temp Table.

Sourabh Dhiman

1y875SQL

Hello everyone, I have faced an issue while working with CTE and Temp Tables in SQL. Could you please provide a simple explanation with an example?"

Would you like an explanation or an example for CTE and Temp Tables?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar1y ago

Accepted answer

Hello Sourabh,

Let's take an example of each CTE (Common Table Expression) and Temp table.

CTE : Common table expression

A CTE is a temporary result set that remains available only for the duration of a single SELECT, INSERT, UPDATE, or DELETE statement.

WITH CTE_Example AS (
    SELECT EmployeeID, FirstName
    FROM Employees
)

SELECT * FROM CTE_Example; This will work As expected

But if you try to execute CTE in another statement like below,

WITH CTE_Example AS (
    SELECT EmployeeID, FirstName
    FROM Employees
)

SELECT * FROM CTE_Example; 

SELECT * FROM CTE_Example; -- This will fail because the CTE no longer exists.

Temp Table

A Temporary Table is a table stored in the tempdb database, and it exists only for the duration of the user session or the batch in which it was created.

-- Creating and populating a temporary table
CREATE TABLE #TempEmployees (
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Salary DECIMAL(10, 2)
);

INSERT INTO #TempEmployees (EmployeeID, FirstName, LastName, Salary)
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

-- Using the temporary table in a query
SELECT *
FROM #TempEmployees
WHERE EmployeeID < 10;

-- Using the temporary table in a query again for second time with different condition
SELECT *
FROM #TempEmployees
WHERE EmployeeID < 5;


-- Temporary table is automatically dropped at the end of the session or can be dropped explicitly
DROP TABLE #TempEmployees;

You can see the difference here: a temporary table can be used multiple times, whereas a CTE cannot be reused, as demonstrated in the example above.

Difference you can find as below,

It store Memory-based	It is stored in Disk-based (in `tempdb`)
CTE result set you can use in a single query/block	A temporary table exists for the session/batch
performance wise better for simple, readable queries	performance it can handle large datasets and is better for multiple references

Lokendra Singh1y ago
A CTE is like creating a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, DELETE, or MERGE statement. It exists only for the duration of that query.
```
-- Create a simple Employee table
CREATE TABLE Employees (
    ID INT,
    Name VARCHAR(50),
    Salary INT
);

-- Insert sample data
INSERT INTO Employees VALUES
(1, 'Loki', 50000),
(2, 'Sunil', 60000),
(3, 'Rakesh', 55000);

-- Using CTE to find employees with above-average salary
WITH high_paid_cte AS (
    SELECT AVG(Salary) as AvgSalary 
    FROM Employees
)
SELECT e.Name, e.Salary
FROM Employees e, high_paid_cte h
WHERE e.Salary > h.AvgSalary;
```
A few things to note:
1. The CTE solution is more concise and self-contained
2. The Temp Table solution requires explicit creation and can be reused multiple times
3. Both achieve the same result but serve different purposes depending on your needs
+2
Shubham Sidnale1y ago
CTE (Common Table Expression):
- Definition: A temporary result set defined within a SQL query.
- Scope: Exists only for the duration of the query in which it is defined.
- Declaration: Declared using the WITH keyword.
- Storage: Does not store data physically in the database; works in-memory.
- Indexes: Cannot have indexes.
- Reusability: Cannot be reused across multiple queries; valid only within the query.
- Use Case: Ideal for simplifying complex queries, recursive queries, and improving code readability.
Temporary Table:
- Definition: A table created temporarily to store intermediate results.
- Scope: Exists for the duration of the database session or until explicitly dropped.
- Declaration: Created using CREATE TABLE #TableName or SELECT INTO #TableName.
- Storage: Stores data physically in the tempdb database.
- Indexes: Can have indexes and constraints.
- Reusability: Can be reused across multiple queries within the session.
- Use Case: Ideal for storing large datasets temporarily, indexing, and performing operations that require multiple query interactions.
+1