When and how to use temporary tables

Question

Hello, I am working on some project data. I have always used the common table, but when I looked at some cases, I saw the # table name. Then I found out that it was a temporary table.

I was wondering what the differences are between temporary tables and ordinary tables, and in what scenarios temporary tables are applied. Does the temporary table occupy memory? Will it be displayed in SSMS?

Thanks for your help.

Answer 1

Hi @Kathy-K

You can refer to the content in these two links, which may help you.

https://www.sqlservertutorial.net/sql-server-basics/sql-server-temporary-tables/

https://www.mssqltips.com/sqlservertip/5109/benefits-of-using-sql-server-temporal-tables-part-1/

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Temporary tables are work areas and they are not seen by other processes. Say that you are analysing some large amounts of data, and as part of the analysis you take our intermediate results which you can store in temp tables, so that you don't have to run the query that produces that result again and again.

However, a temporary table is dropped when you disconnect, and you cannot see it from Object Explorer in SSMS. (Because Object explorer is a different process from the query window.

In terms of memory and space on disk, there is not much difference between temp tables and regular tables.