“there’s nothing so permanent as temporary” can apply to many things, for instance Kitchen and software development (there technical debt is very applicable), the financial top gap measures (which are real debt) of fanfiction. You can apply it to SQL Server as well. The TempDBhas been there since before SQL Server 7, which means it has established a permanent feature for quite some time now.
Your DBA (which might be you) needs to watch the temdb size or space on the separate volume where temdb is stored, or someday the TemDB access patterns will cause havoc.
The most used feature (there are more) in TempDB is temporary tables (often abbreviated to “temp tables”), which – since TemDB got there – has come in three flavours:
- Local/Global Temporary Table (prefixed with #/##)
- Table Variables (prefied with @)
The table variables are created and released implicitly. The temporary tables (one of the Special Table Types) can be created either explicitly using a CREATE TABLE, or implicitly using SELECT … INTO. You’d think that temporary tables are indeed temporary. But they are not:
Temporary tables are semi-temporary. Not actually permanent, but not fully temporary either.
All flavours of temporary tables are not being fully deleted when they go out of scope. When they go out ot scope, they will get an implicit/automatic truncate to empty them (so there is no manual TRUNCATE TABLE or DROP TABLE needed). But the table itself lives on including any cached plan information. They can, and often will be reused. And that’s where you should start reading these links:
- Paul White: Page Free Space : Temporary Tables in Stored Procedures. A big article with a summary at UPDATE STATISTICS Does Not Cause Recompilation for a Cached Temporary Table | Microsoft Connect: When a temporary table in a stored procedure is cached, any statistics created by AutoStats are also cached. This leads to odd effects when a query in the procedure recompiles with statistics cached from a previous execution. In general, the contents of a temporary table change dramatically between executions, so this behaviour is unexpected.
- Paul White: Page Free Space : Temporary Table Caching Explained. A follow up on the first link explaining the caching in more detail.
- An interesting find about Temp tables in SQL Server – Microsoft SQL Server Tips & Tricks – Site Home – MSDN Blogs. Summary: when re-creating a temporary table, the old definition of it might have been cached and worked itself into the query engine.
One more thing: as of SQL Server 2012, the OBJECT_ID associated with temporary tables is negative.
–jeroen
via:
- “there’s nothing so permanent as temporary” – Google Search.
- There’s nothing so permanent as temporary « niksilver.com.
- Technical Debt « The Wiert Corner – irregular stream of stuff.
- There’s Nothing So Permanent as Temporary.
- Turncoat Chapter 1, a harry potter fanfic | FanFiction.
- tempdb Database.
- What To Do When tempdb Is Full – SQLTeam.com.
- Kalen Delaney: tag “temp+tables”.
- Temporary Tables in SQL Server.
- Special Table Types.
- Creating and Modifying Table Basics.
- table (Transact-SQL).
The Wiert Corner - irregular stream of stuff 