469,626 Members | 1,043 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

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.

Aug 13 '07 #1
1 8813
im*******************@yahoo.com (im*******************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 13 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Rebecca Lovelace | last post: by
1 post views Thread by Jim | last post: by
1 post views Thread by Mats Kling | last post: by
5 posts views Thread by wackyphill | last post: by
9 posts views Thread by Veeru71 | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.