#temp – local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can’t have foreign key constraints on a temporary table
@temp – similar to temporary tables except with more flexibility and they always stay in memory
##temp – global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it. These are rarely used in SQL Server.
Which to Use
- If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won’t create statistics on table variables.
- If you need to create indexes on it then you must use a temporary table.
- When using temporary tables always create them and create any indexes and then use them. This will help reduce recompilations. The impact of this is reduced starting in SQL Server 2005 but it’s still a good idea.
where are they stored
temporary tables (local and global) are stored in the tempDB database
So, if you want to find schema information for the temporary table named temp_table you can use the following queries:
use tempdb
select * from sysobjects
Explanation: When you declare a temporary table, SQL Sever adds some additional characters on its name in order to provide a unique system name for it and then it stores it in tempDB in the sysobjects table. Even though you can query the temporary table with its logical name, internally is known with the exact name SQL Server has set. To this end, you need to execute the above query for finding the exact name of the temporary table.