I have been writing lots of SQL commands for Crystal Reports recently. One of them required me to link a sub query to itself, repeatedly, to get multiple levels. Fortunately I had recently read about Common Table Expressions (CTEs) which simplify SQL commands in cases like this.
A CTE allows you to write a query, give it a name, and then refer to it throughout your SQL command as if it were a table. You would start your command with something like this:

with trans as (
select Item, Date, Amount, CustName
from Items
inner join customers on Items.CustID = Customers.CustID
Where Items.Type = 3)

You put your subquery in the parens and Trans becomes the name of the results.
After that you start your SELECT and use Trans as if it were a table with those 4 columns.

This is especially helpful when you have to use the same results multiple times, because you don’t have to repeat the SQL like you normally would with a normal subquery. If you have to change that subquery you can make the change in one place, so the SQL is more efficient to write and read.

While CTEs make writing more efficient, they don’t make the SQL processing any more efficient. Each time you reference a CTE in your SQL it has to repeat the CTE query to get results. If the subquery is complex you want to have it processed only once and then be able to refer to the results several times without having the database repeat the query. For that you switch from a CTE to a Temp table.

With a temp table the results of the query are written to an actual table (in a tempDB). Temp tables are deleted when you are done with your DB session. And because temp tables are written to tempDB you don’t even need to have “write” permissions for the main database. You can even add an index to the temp table if it is large enough to need one.

With a temp table your SQL command can read that table as many times as necessary without having to generate the data again. This can make a significant difference in the processing time of a query, especially when you have to refer to those results multiple times. It is a good idea to start the SQL by checking to see if the temp table already exists and deleting it. Otherwise you will get an error when you try to create it.

Below is an example of a command that shows how to do the CTE above as a temp table. First it drops the temp table and then it recreates it:

if Object_ID (N'tempdb..#TempItems') IS NOT NULL
BEGIN
DROP TABLE #TempItems
END;

select Items.Item, Items.Date, Items.Amount, customers.CustName
Into #TempItems
from Items Items
inner join customers customers on Items.CustID = Customers.CustID
Where Items.Type = 3 ;

If you start your command with the SQL above you can follow that with a SELECT that references the table #TempItems (all temp tables start with #). You treat a temp table just like any other table. Note that there are semicolons after each step which we didn’t need with the CTE.

If you think your reports might benefit from these techniques, call to schedule a session.