SQL SERVER – What is Interim Table – Simple Definition of Interim Table

Sometimes a simple question like “What is interim table?” can initiate a never-ending discussion between developers. I experienced this recently while I was on phone helping my friends working in Los Angeles. In a conference call, one of the developers kept on talking about “first interim table” and “second interim table” and so forth, while another developer was of the opinion that that there cannot be more than one interim table. Well, as this was not enough a third developer interrupted the debate and said that all the tables are interim tables. The heated discussion seemed never ending.

To put the long story sort, let us today in this blog post try to understand what interim table is and how many possible interim tables are there.

Interim table is a table that is generated by joining two tables and not the final result table. In other words, when two tables are joined they create an interim table as resultset but the resultset is not final yet. It may be possible that more tables are about to join on the interim table, and more operations are still to be applied on that table (e.g. Order By, Having etc). Besides, it may be possible that there is no interim table; sometimes final table is what is generated when query is run.

Let us try to comprehend the above explanation with an example using the following AdventureWorks sample database.

USE AdventureWorks;
GO
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
GO

First Interim Table:

When two tables are joined and there are more pending operations then the table is called first interim table.

Second InterimTable:

When a third table is joined to the first interim table and there are more pending operations then the table is called second interim table.

Final Table:

After joins, all the remaining database operations are performed on the latest interim table which generates the final table.

Please let me know if your doubts about interim tables are clear now with this simple and easy-to-read post. If you like the style of this post, I will post many more like this in near future.

Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)