Properly storing intermediate results

There are times when a query can become very complex, either because of a complicated database schema or because of complex business logic in the query, or both. In these cases, it may be easier to write the query in parts and store intermediate query results so that they can be used in a later query. This can make the query more readable, but it can also help the SQL Database Engine create a better query execution plan. There are different ways to store intermediate query results – this section will look at a few different options along with some of the considerations for when and where to use them.

Using table variables and temporary tables

Table variables and temporary tables serve the same basic principle: to store an intermediate resultset to be used by a subsequent query. Database developers use these to break down complex joined queries that typically are not very efficient.

Tip

We have mentioned before about how the way...