Steve Jones - SSC Editor (8/8/2013)
if you need places for data to manipulate it, like staging tables for ETL, why not just create real tables? Don't drop them, and don't clear them out, except for the process that uses them. If you can't keep naming straight, then use a GUID in each process to create/drop the table. If you have process 1 using the same table as process 2 and you aren't sure when these processes will run, you are architecturally making a big mistake.
Steve - This is so true. Use of a permanent staging table has always improved the reliability of any well written ETL process. I used to insert into #temp tables all the time. The difference in performance is amazing. I have heard that you should make these tables heaps, but that has not been the better way for me. What is your opinion on that?