When to use cte and temp table?

Question

i know this a common question, but most frequently people ask about performancy between this two. What I'm asking for is use cases of cte and temp table, for better understanding the usage of them

Answer 1

With a temp table you can use CONSTRAINT's and INDEX's. You can also create a CURSOR on a temp table where a CTE terminates after the end of the query(emphasizing a single query).

I will answer through specific use cases with an application I've had experience with in order to aid with my point.

Common use cases in an example enterprise application I've used is as follows:

---

Temp Tables

Normally, we use temp tables in order to transform data before INSERT or UPDATE in the appropriate tables in time that require more than one query. Gather similar data from multiple tables in order to manipulate and process the data.

There are different types of orders (order_type1, order_type2, order_type3) all of which are on different TABLE's but have similar COLUMN's. We have a STORED PROCEDURE that UNION's all these tables into one #orders temp table and UPDATE's a persons suggested orders depending on existing orders.

CTE's

CTE's are awesome for readability when dealing with single queries. When creating reports that requires analysis using PIVOT's,Aggregates, etc. with tons of lines of code, CTE's provide readability by being able to separate a huge query into logical sections.

---

Sometimes there is a combination of both. When more than one query is required. Its still useful to break down some of those queries with CTE's.

I hope this is of some usefulness, cheers!