Is it bad practice to use temporary tables in SQL?

Question

That is, using temporary tables with some initial unique data and then populating it one or several fields at a time. Sometimes it makes code seem more readable but it also leads to procedural type thinking. And it's also slower than using derived tables or other methods. Is it discouraged in industry?

Answer 1

It would be a bad practice if all set-based operations were a) implemented and b) efficiently in all engines.

However, for some tasks (like emulating LAG and LEAD in SQL Server, long insert chains on cascading auto-generated id is several tables etc), temp tables or table variables are a nice solution.

You should note that temporary tables are very often created and dropped by the engine itself for the operations involving using temporary in MySQL, spool in SQL Server etc.

So each time you create a temp table you should ask yourself a question:

• Do I create a temp table because I don't know a set-based way, or because I know a set-based way but the server (or optimizer) does not?

If the answer is "I know but the optimizer does not", then create the table. The optimizer would do the same if it could.