Paul White explains how to cache temporary objects:
Table variables and local temporary tables are both capable of being cached. To qualify for caching, a local temporary table or table variable must be created in a module:
- Stored procedure (including a temporary stored procedure)
- Trigger
- Multi-statement table-valued function
- Scalar user-defined function
The return value of a multi-statement table-valued function is a table variable, which may itself be cached. Table-valued parameters (which are also table variables) can be cached when the parameter is sent from a client application, for example in .NET code using
SqlDbType.Structured. When the statement is parameterized, table-valued parameter structures can only be cached on SQL Server 2012 or later.
The first time I heard about this was a SQL Saturday presentation that Eddie Wuerch did. Paul does a great job talking about the requirements (and noting that table variables are eligible as well), making this well worth the time to read.