By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,939 Members | 1,602 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,939 IT Pros & Developers. It's quick & easy.

Temp Table vs. Union: Which Has Better Performance?

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.