MS SQL - Table Variables and #Temp Tables - Key Performance Factors

Having worked in ERP and Healthcare Information Systems, the bulk of the Business Logic is observed to be predominantly written in the Stored Procedures. This puts a heavy load in SQL Server (my work has been only with MS-SQL and anyway, it doesn't matter in this context). 

The scenario becomes complicated in Web Based programming where results are expected like Windows application. The Quick & Dirty mechanism employed by Programmers (across seniority) and even Designers to fetch mixed data contents is to use Temp tables.

Listing below the key performance factors to take into account, while designing the logic involving Temp Tables or Table variables. 

1. Temp tables must NOT be used for large records. A record size of 10 to 500 or even 1000 can be a decent number. More than the count, it also depends on the number of columns, data type as well.
2. Not ideal for concurrent scenario or real-time sites. Temp tables have to be created for each connection / session. This is going to be heavy additional load for SQL
3. Large Temp tables / Table variables can perform poorly in spite of Index availability. Temp tables allows to define more than one Index but more index does not help the performance.
4. Though the Temp tables / Table variables are internally cleaned, it is very unclear on when the memory gets actually released and used for other purpose. There is no such thing as GC that runs a clean-up job. SQL does it as part of its engine and its an additional load.
5. TempDB size growth is also a matter of concern. Its advisable to set "Max Degree of Parallelism" for heavy usage Enterprise scale application and create as many Temp DB Secondary files for the number of Max Degree.
6. Temp DB be preferably kept in SSD Drive or a separate drive, so that the I/O is better 
7. Allocate a large chunk in the auto-growth instead of smaller percentage or MB size, if there is going to be a load of Temp DB
8. Remember that, not only Temp Tables & Table variables are going to use Temp DB, but also the other regular query operations like Sorting, Hashing and a poorly written query is going to make more use of the TempDB.
9. If multiple databases are maintained in an server instance, and if they have logic written using Temp tables / Table Variables, remember that there is only TempDB to serve.
10. So, when they can be useful? - typically MIS reports or dynamic reports where data is aggregated from many tables and values are manipulated before sending out.
11. So, what to do for other cases? - one approach is to create Physical tables with a column to identity the SessionID, so that each Session's data can be maintained and then later deleted. Index on Physical tables will also help in query processing. Need to make sure, not to abuse this Table by populating 10000 records for each session and then trying to Delete. This will backfire.

SQL 2014 and its successor may be bringing in features like Inline Memory Processing, but then we have to remember that, most of the customers would prefer 'Standard' edition and these features are most likely going to be made available only in Enterprise Edition.