Best way to Use #Temp table and @Table variable

Here I am going to discuss how often we can use #Temptable and @Temp variable in real T-SQL Code

Whenever we are writing T-SQL Code, we need a table in which to store temporarily data, based on this SQL offer four different table options:-

1) Normal tables
2) Local temporary tables
3) Global temporary tables
4) Table Variables

• Normal tables are that exactly physical tables defined in our database.
• Local Temporary tables are temporary table that are available only to the session that created. This tables are automatically distroyed at the termination of the procedure.
• Gloabal Temporary tables are temporary tables that are avaible to all sessions and all users. They are dropeed automatically when last session using temporary table has completed. Both local temporary tables and global temporary tables are physical are physically created in the tempdb database.
• Temp variables are stored within memory but its set as table. Table variables are partially stored on disk and partially stored in memory. The Access time for table variable can be faster than the temproray table.

The difference between accessing tables and variables cause the internal SQL server processes, Temporary tables are actually physical tables, so the SQL Optimizer and locking engine handle the tabels just as any other database tables. Becasue data reads on temproray table are made a READ lock is placed on the table.

If you are applying a locking process takes its time and consume CPU resources. When reading data from table variable, because table variable partially stored in memory and can not accessed by any other user. In very busy database lack of locking can improve system performance.

On Heavy load system that has good to use temporary tables, the disk array in the TEMPDB database will experience a higher than expected load, becuase all the reads and writes to the temporary tables are done withig the tempdb database. Table variable will perform poorly with large record sets. When many users start using table variable we have to concerned because large Amount of RAM are used.

Table variable should hold no more 2 megs or 3 megs of data (depends on user load system momory)