Posted by Erland Sommarskog on 08/13/07 22:01

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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: How does one query for %?
• Prev in forum: Re: INNER JOIN - INSERT
• Thread view: Re: Temp Table vs. Union: Which Has Better Performance?

[Reply to this message]