I’ve encountered a fair bit of confusion on various forums as to the differences between temporary tables and table variables. As a quick article (I’m knee-deep in some AI stuff at the moment) I thought I’d quickly go over some points on temp tables and table variables.
- Created using the Create table syntax, preceding the table name with a’#’ for a local temp table and ‘##’ for a global temp table
- Allocated storage space within the TempDB database and entered into the TempDB system tables 1
- The table’s actual name is the name is was created with, a large number of underscores and a hash value, to prevent object name collisions if two connections create a temp table with the same name
- Can have a primary key, defaults, constraints and indexes (however the names of these are not hashed, possibly leading to duplicate object errors for constraints and defaults)
- May not have triggers.
- Foreign keys are permitted, but are not enforced
- Have column statistics kept on them. The algorithm for determining when to update is different to permanent tables
- Exist until they are dropped, or the connection closes.
- Are visible in any child procedures called from the one where the table was created. Are not visible to parent procedures
- Are not persisted to disk unless there is memory pressure, or the table is too large to fit in the data cache