Sometimes, discussions among developers can become heated when it comes to understanding certain concepts in SQL Server. One such concept is the “interim table.” In this blog post, we will delve into the world of interim tables and shed some light on their nature and purpose.

An interim table is a table that is generated when two tables are joined, but it is not the final result table. It serves as a temporary resultset that may undergo further operations or join with additional tables. The final table is only generated after all the necessary operations have been applied.

Let’s illustrate this concept with an example using the AdventureWorks sample database:

USE AdventureWorks;

SELECT *
FROM Person.Contact C
INNER JOIN Sales.SalesPerson SP ON C.ContactID = SP.SalesPersonID
INNER JOIN Sales.SalesTerritory ST ON ST.TerritoryID = SP.TerritoryID
ORDER BY ST.TerritoryID, C.LastName;

In this example, we are joining the Person.Contact, Sales.SalesPerson, and Sales.SalesTerritory tables. The result of this join operation is the first interim table.

If we were to join another table to the first interim table and perform additional operations, the resulting table would be called the second interim table. This process continues until all the necessary operations have been applied, and the final table is generated.

It’s important to note that not all queries will have interim tables. In some cases, the final table is generated directly without any interim tables.

I hope this explanation has cleared up any confusion you may have had about interim tables. If you found this post helpful, stay tuned for more articles on SQL Server concepts in the future.