SQL Server with clause versus temporary table

Question

I could either do

select * into #randomTenUsers from
(select top 10 * from users)x    

select * from #randomTenUsers

OR

WITH randomTenUsers   as (select top 10 * from users)    

select * from randomTenUsers

From what I understand WITH statement also creates a temporary result set.

Is there a reason why WITH should be preferred over temp tables or vice versa?

Answer 1

From what I understand WITH statement also creates a temporary result set.

No it does not. Using a CTE will not create a "temporary result set". There might be reasons for a query to create work tables but just because you are using a CTE is not one of them.

These two queries have an identical query plan and none of them creates a temporary result like a temp table in tempdb.

---

with randomTenUsers as
(
  select top 10 *
  from users
)    
select * 
from randomTenUsers;

---

select * 
from (
     select top 10 * 
     from users
     ) x;

---

As marc_s said in the comment, what you should use depends on what you want to do. There are situations where creating a temp table makes perfect sense and there are situations where it is completely unnecessary.