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

Question

I have used temp table in my current stored procure and i have face performance issue .so i have used hash table(#).I dont know clearly what is different.can you tell me what is exect it ?

Puneet Kankar · 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 Tiwari · Answer

Refrence: Url: https://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server

Sanwar Ranwa · Answer

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

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

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (3)