what Difference between Hash(#) and temp(@) table in SQL?

A Temp table is easy to create and back up data. But the table variable involves the effort when you usually create the normal tables.
Temp table result can be used by multiple users. But the table variable can be used by the current user only.
Temp table will be stored in the tempdb. It will make network traffic. When you have large data in the temp table then it has to work across the database. A Performance issue will exist. But a table variable will store in the physical memory for some of the data, then later when the size increases it will be moved to the tempdb.
Temp table can do all the DDL operations. It allows creating the indexes, dropping, altering, etc.., where table variable won't allow doing the DDL operations. But the table variable allows us to create the clustered index only.
Temp table can be used for the current session or global. So that a multiple user session can utilize the results in the table. But the table variable can be used up to that program. (Stored procedure)
Temp variable cannot use the transactions. When we do the DML operations with the temp table then it can be rollback or commit the transactions. But we cannot do it for table variable.
Functions cannot use the temp variable. But the function allows us to use the table variable. More over we cannot do the DML operation in the functions but using the table variable we can do that.
The stored procedure will do the recompilation (can't use same execution plan) when we use the temp variable for every sub sequent calls. Where the table variable won't do like

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Puneet Kankar7y ago
Accepted answer
A Temp table is easy to create and back up data. But the table variable involves the effort when you usually create the normal tables.
Temp table result can be used by multiple users. But the table variable can be used by the current user only.
Temp table will be stored in the tempdb. It will make network traffic. When you have large data in the temp table then it has to work across the database. A Performance issue will exist. But a table variable will store in the physical memory for some of the data, then later when the size increases it will be moved to the tempdb.
Temp table can do all the DDL operations. It allows creating the indexes, dropping, altering, etc.., where table variable won't allow doing the DDL operations. But the table variable allows us to create the clustered index only.
Temp table can be used for the current session or global. So that a multiple user session can utilize the results in the table. But the table variable can be used up to that program. (Stored procedure)
Temp variable cannot use the transactions. When we do the DML operations with the temp table then it can be rollback or commit the transactions. But we cannot do it for table variable.
Functions cannot use the temp variable. But the function allows us to use the table variable. More over we cannot do the DML operation in the functions but using the table variable we can do that.
The stored procedure will do the recompilation (can't use same execution plan) when we use the temp variable for every sub sequent calls. Where the table variable won't do like
Arun Kumar Tiwari6y ago
Refrence:

Url: https://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server
Sanwar Ranwa7y ago
Hello Velmurugan

Please check these Links

http://www.nullskull.com/q/10298657/what-is-difference-between-temp-table-and-hash-table-in-sql--server.aspx

https://stackoverflow.com/questions/21011276/difference-between-temptable-and-temptable

http://sqlserverplanet.com/tsql/yet-another-temp-tables-vs-table-variables-article