Temporary tables are makeshift tables that you can create and store data in for temporary reference. One you are done with querying and you close your ssms, the temp table gets deleted.
You use the # symbol as a prefix to create one.
There are 2 types: local and global. If you create a local one, it is accessible only to you and if you create a global one, it is accessible to all active sessions on ssms.
AI generated analogy:
For local temporary tables (#table_name)
A personal scratchpad: Think of a local temp table as a private notepad you create for yourself to use during a single work session.
The session is the key: The "session" starts when you connect to the database and ends when you disconnect or close the query window.
It vanishes with the session: When you close the SSMS window, your connection to the database ends, and SQL Server automatically erases your temporary table.
For global temporary tables (##table_name)
A shared whiteboard: A global temp table is a bit different. It's like a whiteboard that everyone in the office can see and write on.
It outlives your session (sometimes): The global temp table stays around as long as at least one person is still using it. If you close your SSMS window but someone else is still running a query against that same table, it will not be deleted.
The last person to leave turns out the light: It is only automatically deleted once the very last connection that has been referencing that table closes.
CTE's, (Common Table Expressions) on the other hand, only holds that data with a nickname in the SQL internals/data pages (the behind the scenes storage of SQL) for the duration of the query and then deletes it.
Table variables are the middle ground between temp tables and CTEs. In temp tables, once you create the table and run your SQL code, SQL server destroys the table after your session ends whereas in case of table variables, SQL server destroys the table after the chunk of SQL code referencing the table variable is done executing.
Top comments (1)
Hey friend, nice post! 👋
You might want to double-check your formatting in this post, it looks like some things didn't come out as you intended. Here's a formatting guide in case you need some help troubleshooting. Best of luck and thanks again for sharing this post!