What is CTE and Temp Table.

Question

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?

Jignesh Kumar · 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 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 Singh · Answer

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: The CTE solution is more concise and self-contained The Temp Table solution requires explicit creation and can be reused multiple times Both achieve the same result but serve different purposes depending on your needs

Shubham Sidnale · Answer

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.