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://blog.sqlauthority.com)
16 Comments. Leave new
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
Hi Pinal,
This is really very good information. Please be continue with your posting.
Ritesh
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
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
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.
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..
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.
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
Hi Pinal,
It is the good article to clear that interim table.
With Regard
Rajiv Singh
Hi Pinal,
Great to here about Interim tables. Great explanation. Keep posting good things.
Hi Pinal,
Great explaination. Easy to understand. Thanks for the post. Very useful.
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.
Nice Article
Quite informative, and quite clear just like your all other posts..
Nice explanation! Keep posting valuable information.