#temp table vs. @table variable

Thought I’d share this article with everyone:

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

I try to avoid temp tables whenever I can, but there are too many cases where there’s no better way to do something.

In the past I typically used #temp tables simply because that is the only thing I really knew how to use and because when I took over some previously written applications there were a lot of cases where they were used.

However, anytime I have to use a temp table now I typically use a table variable directly in the stored procedure.

Today was a great example of why only one of those would work. There was an old stored procedure that uses a #temp table and that same logic is unfortunately copied into several different places. Today I finally got fed up and created a user-defined-function (UDF) to centralize some of the logic. I found out very quickly that you can not use a #temp table in a UDF, which about made me pass out… But then I realized I could simply use a table variable which I prefer anyways.

Another nice thing about table variables is you don’t have to manually clean them up (e.g. drop table #temp).

The article explains different scenarios and recommended practices. Worth looking at if you ever do much with stored procedures or temp tables.