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 (http://blog.sqlauthority.com)

About these ads

16 thoughts on “SQL SERVER – What is Interim Table – Simple Definition of Interim Table

  1. Nice Article.
    It was presented really well and was easy to understand.

    Just a quick question, are sub-queries and tmep tables too classified as interim tables?

    Keep up the good work.

    Regards,
    Vishal Gadhia

  2. Hi Pinal,

    This is very very nice article about interim tables, you explained it very nicely.

    May be my question is not valid but, I am just wondering how they are helpful other than they are required to get final result/table?

    Thanks

  3. Is this MS jargon or where did ‘interim table’ come from? From a ‘set’ approach (which is an appropriate way to think about queries), I see that this makes sense, but obviously from an execution perspective maybe it would be misleading to think of these as interim tables–i.e. these joins can and will be re-ordered, and processed in a stream (or spooled)…so the notion of ‘tables’ really has no relevance (with the exception of spools).

    But I liked the succinctness of the post-

    Thanks,
    Steve

  4. I like your blog. I follow it all the time. Interestingly, whenever I go ahead with my problems with SQL Server in Google, there is atleast one Pinal Dave link on the first page all the time.

    My folks at office have also observed the same. Keep up the good work.

    Your explanations and examples are simple to understand. That is the USP of this blog.

  5. Hi Pinal,

    This is very nice and the way of explanation is very good. not only this blug i saw many of u r blugs all blugs are very nice and very use full.. Keep it up and thank you..

  6. Hey Pinal

    This was really very nice post,
    Nice geamatrical approach to comeup with solution.
    You concluded Interim Table dramatically, it really facinate

    Thanks for sharing this all …

    K.K.B.

  7. Hi Pinal

    This is great article regarding interim table. It sound good. i am wondering to learn these type of new things. Thanks. you blog and your information is very helpful and it is very easily understanding

  8. Did you pay people to post mostly positive comments here? Because it’s very ambiguous and leaves me frustrated trying to figure out what interim table means.

    • Poorman,

      Basically Interim tables understanding makes more sense to you when you understand the Architecture of SQL working. when you execute a Query in the SSMS using joins as in above examples internally the table data is joined based on the condition, that set of records which are generated/pulled are called interim tables, why bcoz that is a table which holds that result set until the next set of operations begin (this is not the result you see in SSMS output, there are still some more operations to be done before you see that result) when all the operations mentioned in the SQL Query are executed the result that you see in SSMS is what is known as the Final table.

      for more understanding of the SQL architecture you can read books like PROFESSIONAL SQL Server Internals and Troubleshooting.

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #032 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s