Search code examples
sqlsql-server-2008sqlperformance

Is there a performance or memory benefit : Select A, B, C into temp from query VS Select into temp from (Select A,B,C from table) as TTable


In Sql server (2008 version), I have two queries like this :

Select * from sampleTable into #tempTable 

OR

Select * into #tempTable from (Select Query) as someTableName

Is there performance or memory space benefit in any of these queries ? or both are equally good.

This is known that they individually are better than

Insert into Temp 
<Query>

But how about when compared to each other.

Updated Text : Two queries are like this:

 Select A,B,C into #tempTable from TestTable

OR

Select * into #tempTable from (Select A,B,C from TestTable) as someTableName

Solution

  • All of those result in the same query plan.

    SQL Server has a query optimizer, and optimizing away redundant columns is about the easiest and most basic optimization there is.

    The best way to answer such questions for yourself is to look at the query plans and compare them. It is generally quite pointless to memorize the performance behavior of specific queries. It is a better approach to understand how queries are optimized and executed in general.