I'm curious as to how much you use #temp tables? Almost all of our SQL is for batch processing at night, written in the 'divide and conquer' format Jeff mentioned. Years ago when we first started writing SQL we created tables in a database called 'db_work'. You had an 'If exists..Drop table..' at the beginning of your SQL and another at the end to clean up these work tables, temp tables really since they were deleted at the end of the session. As more and more SQL was written we started running into issues where the same 'work' table name was being used in two different SQL's. So one would create the table and be running then the other started and the first thing it did was drop that table name, causing the first SQL to fail because the table didn't exists or wrong fields. So after some time of patching these types of issues we started to use the #temp tables more and more since we wouldn't run into this issue with the temp tables, we usually didn't code delete's at the end since they would automatically be dropped when the session ended. Now we have a lot of #temp tables being built and we are getting push back that the tempdb space is getting to large. We are now being told to not write any SQL with #temp tables, as this is not a 'Best Practice'. They are asking that we start using the 'db_work' database more for these type of tables. I'm worried that we will start running into the same issue we had before, where a two SQL's will use the same name and run at the same time causing one to fail. Plus I see that the issue of space will only switch from the tempdb to the db_work database.

How do you find the balance of temp vs work table usage? If you use more work type tables, do you have a naming standard to avoid the issue I mention above?