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?
Within a User Defined Function, I don't see a reason to use one over the other. A UNION has the potential to be more constraining than separate statements...
I'd like to see the details of the function, and it's use, because it's possible it might not be necessary at all.
I'd just UNION ALL them - the key there is not a UNION (which could be less efficient by deduping), but a UNION ALL.
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.
Actually, it does make a difference.
When you have separate INSERTs, you have to take care to do it inside a transaction, so that you have a consistent state at all times. But, since they're separate, the amount of memory required for the server to complete is less because every INSERT has it's own result set - when the query completes the server is free to perform another query etc...
When you have everything inside one big query joined by UNION ALL the server would need more temp tables and more memory to complete. But, this way you have something of a 'bulk' insert, and could result in faster insert.
Personally, I'd go with separate inserts inside a transaction. Sometimes just by having something done simpler is way more beneficial in terms of maintainability and debugging than some minor performance gain (if any, depends on the number of rows actually getting inserted)