Temporary Object Caching Explained
- by Paul White
SQL Server 2005 onward caches temporary tables and table variables referenced in stored procedures for reuse, reducing contention on tempdb allocation structures and catalogue tables. A number of things can prevent this caching (none of which are allowed when working with table variables): Named constraints (bad idea anyway, since concurrent executions can cause a name collision) DDL after creation (though what is considered DDL is interesting) Creation using dynamic SQL Table created in a...(read more)