0

When should I prefer table variable over hash table and hash table over table variable ones. As I see both are almost same. Is there a performance difference?

Igor Cova
  • 3,126
  • 4
  • 31
  • 57
ammad khan
  • 1,022
  • 1
  • 10
  • 14
  • # table is a physical memory allotted for the temporary purpose in that particular session and where as in @ table it is logical memory. so it will be your requirement what type of table space you want.. – Ajay Feb 22 '18 at 06:23
  • Actually working on table valued function where i am maintaining and calculating original data on temporary basis. without inflicting any changes to original rows i am looking for temp changes. – ammad khan Feb 22 '18 at 06:32
  • for the temporary changes if you are using loops and u want to make the changes with in the loop the go for @ table if not then go for #table as it will be through out the session. – Ajay Feb 22 '18 at 06:35
  • 2
    Possible duplicate of [What's the difference between a temp table and table variable in SQL Server?](https://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server) – Prabhat G Feb 22 '18 at 06:36
  • What do you mean by saying "Hash table"? Until 2014 you could NOT create hash tables directly, they are created by server. Starting with 2014, inmemory OLTP was appeared, inmemory tables ARE hash tables, but they are very different by their structure. Did you mean these inmemory tables? – sepupic Feb 22 '18 at 07:17

1 Answers1

1

As per your question When should i prefer Variable table over hash table and Hash table over variable ,I guess this has been already answered in When should I use a table variable vs temporary table in sql server?

Note : For your Information don't believe that Table Variables are stored in memory and Temp Tables are stored in TempDB . Both where created in TempDB and not in memory. This is proved by Pinal Dave in Temp table and Table Variable storage place

Mahesh.K
  • 901
  • 6
  • 15