Thank you all for your great feedbacks.
In this article I tried to illuminate things I didn't find in any other article. In order to find common differences between Variable table to Temp table I added in the beginning of this article 2 links:
1. Frequently Asked Questions - SQL Server 2000 - Table Variables
2. Is a Temporary Table Really Necessary?
Is there really any difference or advantage between Temp Tables and Table variables?
When is it advantageous to use a table variable?
While there may be a time where it doesn't matter which one you use, is there ever a time where table variables convey an advantage over temp tables?
Yes. there is a different between those 2 in:
- Table variables result in fewer recompilations of a stored procedure as compared to temporary tables
- Table variables do not maintain statistics like temporary tables can (good for large tables)
- Temp table definition can be altered by alter table
- Select into and Insert Exec can be used only with Temp table
- Table variables require less locking and logging resources
- You can't create non-clustered indexes on table variable
As I said, i tried to mention unknown behaviors.
Adam Machanic mention few differences (as I wrote above) and he's absolutely right.
Anyway, you should pick between them respectively and think of what you need before.
I'd like to get feedbacks if you'd like to have an article about all differences between those 2, and give more details then the paragraph above.