SELECT INTO vs WITH AS: Who is faster in the temp table approach?

I have a little experience with these two structures. Is there a performance improvement using one or another to use temp tables?

• I know that SELECT INTO creates a table that persists after the query and is removed after some time. There is some problems when using the same query after a few seconds (SQL Error [2714] [S0001]: There is already an object named #XXXXX in the database). So, you don’t need create the table again if you are making a lot of queries using that temp table at different approaches. It’s a table that can be used by different users while exists, included.
• WITH AS just create the temp table in the current query and is immediately erased after that.

I have no more information.

Answer

You are confusing two concepts.

SELECT INTO creates a new table. That could be a temporary table or a permanent table. But the table is created.

WITH defines a common table expression (CTE) used within a single query. This is not a “table”. It is simply a subquery and it may or may not be materialized as a temporary table (actually, SQL Server does not typically materialize CTEs).

You use SELECT INTO when you want a real table. Some reasons for that are:

• Sharing data among multiple queries.
• Collecting correct statistics to help the query optimizer.
• Adding indexes to improve subsequent query performance.

You use a CTE when you want a named subquery in a query. If you are choosing between the two, you probably want to start with a CTE.