Temp Table vs. Union: Which Has Better Performance?

Right now, a client of mine has a T-SQL statement that does the
following:

1) Create a temp table.
2) Populate temp table with data from one table using an INSERT
statement.
3) Populate temp table with data from another table using an INSERT
statement.
4) SELECT from temp table.

Would it be more efficient to simply SELECT from table1 then UNION
table 2? The simply wants to see the result set and does not need to
re-SELECT from the temp table.

Re: Temp Table vs. Union: Which Has Better Performance?

imani_technology_spam@yahoo.com (imani_technology_spam@yahoo.com) writes:
> Right now, a client of mine has a T-SQL statement that does the
> following:
>
> 1) Create a temp table.
> 2) Populate temp table with data from one table using an INSERT
> statement.
> 3) Populate temp table with data from another table using an INSERT
> statement.
> 4) SELECT from temp table.
>
> Would it be more efficient to simply SELECT from table1 then UNION
> table 2? The simply wants to see the result set and does not need to
> re-SELECT from the temp table.

Probably, provided that you use UNION ALL. By default UNION implies DISTINCT
which could cause a extra sorting step that could be equally expensive
as the temp table.

But there are always a lot of "it depends" when it comes to performance,
so if there are some deviations from the scenario as you described it,
the real answer may be different.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx