I have noticed quite a few mentions of the word "materializing" when people are talking about using temporary tables in SQL Server. Can someone expand on what it is that means? I am just trying to get a better understanding of what that means in terms of using temp tables?
Thanks!
S
The term "materializing" is normally used in the context of a view. When you create a clustered index on a view, you materialize the view; this means that the view's data is stored like a table on disk, and updated automatically when the tables which participate in the view get updated.
If a view is not materialized, SQL Server must compute the data in the view by performing the joins in the view definition every time a query is performed (though the results may be cached, or what-have-you).
I'm not sure the term "materializing" properly applies to temporary tables. The word materialized in a database context implies "caching query results in concrete table that may be updated from the original base tables". Perhaps that is custom internal jargon to indicate converting the results that are in a temp table into a permanent table.