SQL Server – Best Practices for Using Temp Tables in Stored Procedures

best practicessql server

I recently developed a stored procedure which essentially queries data from several different tables, manipulates it, and then inserts the result in a certain table.

It ended up being one INSERT statement with many subqueries and was around 300 lines. The code was very performant; however, it is not very readable and may have other drawbacks.

Is it within recommended usage to write a query like so:

SELECT ...
INTO #tempfoo
FROM foo;

SELECT ...
INTO #tempbar
FROM bar;

SELECT ...
FROM #tempfoo
     INNER JOIN #tempbar ON #tempfoo.id = #tempbar.id;

Or is it better to write it like this, even though it can become somewhat unreadable:

SELECT ...
FROM
(
    SELECT ...
    FROM foo
) AS foo
INNER JOIN
(
    SELECT ...
    FROM bar
) AS bar ON foo.id = bar.id;

Note: I know this is a simple example, but imagine if you have layers of nested subqueries and four or more table joins.

Best Answer

There are times when the indexes on underlying tables just aren't enough to satisfy a particularly big query with lots of joins/applies/subqueries/CTEs. For those times, temp tables and table variables can be just what you need to improve performance.

By using a temp table to store intermediate results, you can create any indexes or statistics that you need to improve further processing. I run into this now and then when we need to build an occasional-use report on top of a bunch of OLTP data. Table variables can be used to similar ends, but with the restriction that they're not nearly as flexible with indexing (up until 2014, at least, which began to remove much of this restriction). But they are a bit lighter-weight, and more importantly they are scoped to the module rather than session, so you don't have to worry about naming conflicts like you would with temp tables.

There is overhead associated with creating and writing to temp tables, of course, to say nothing of the impact on code maintainability, so if the query can be written cleanly as a single query making use of set-based operations without sacrificing performance, then stick with that approach.

Related Question