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.

What is CTE and Temp Table.

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (3)