Are temp tables stored in-memory?

Where are temp tables stored?

Temporary tables are stored in tempdb. They work like a regular table in that you can perform the operations select, insert and delete as for a regular table. If created inside a stored procedure they are destroyed upon completion of the stored procedure.

Do temp tables write to disk?

Yes most data does normally end up on disk if it is important enough but temporary tables contain temporary data - it may well be wasted effort writing it to disk. That's how I understand it too. Temporary objects start out in memory and are only 'spilled' to disk when there is memory pressure.

How long does a temp table last?

Temporary tables can have a Time Travel retention period of 1 day; however, a temporary table is purged once the session (in which the table was created) ends so the actual retention period is for 24 hours or the remainder of the session, whichever is shorter.

Are temp tables dropped automatically?

If you are wondering why it is not required to drop the temp table at the end of the stored procedure, well, it is because when the stored procedure completes execution, it automatically drops the temp table when the connection/session is dropped which was executing it.