One INSERT with UNIONS or multiple INSERTS?

Question

I have a sql function that returns a table. The table is populated via 6 or so reasonably complex statements.

Is it better to UNION together these statement so there is only 1 insert or are these better kept separate?

Or does it make no difference whatsoever?

Answer 1

I'd just UNION ALL them - the key there is not a UNION (which could be less efficient by deduping), but a UNION ALL.

Answer 2

I will suggest separate inserts. I found that performance of insert with Union All is very poor as compared to separate inserts. I tried inserting around 3500 records using both the approaches, separate insert statements were 4 times faster. (It may vary on number of columns in your insert)

Answer 3

Instead to think by yourself what is the best solution i suggest as a tip to use a properly tool.

In SQL server using the Managment Studio you can evaluate the performance by Display Estimated Execution Plan. So in this way you can really see the differences between your 2 cases and select the best one by observing the results.