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
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.