I try to avoid temp tables when I can, but there are plenty of cases where there is no better way to do something.

For a long time I used #temp tables by default, mostly because it was the only thing I really knew and because the applications I inherited were full of them. These days when I need a temp table I reach for a table variable inside the stored procedure instead.

Today was a good example of why the distinction matters. An old stored procedure used a #temp table, and that same logic had been copied into several other places. I finally got fed up and created a user defined function to centralize it, and found out very quickly that you cannot use a #temp table inside a UDF. That is a hard limitation, not a quirk. A table variable works there, which is what I prefer anyway.

The other nice thing about table variables is that you do not have to clean them up. No DROP TABLE #temp at the end, they simply go out of scope.