temp table allocation in SQL 2005
Temp table is no different from regular tables. We may choose either page or extent allocation to optimize performance and space utilization.
The kinds of operation affect how we choose:
- select into
- bulk insert
- create index
- insert
How full the tables are also affects how we choose.
For most applications, SQL Server’s dynamic algorithm works pretty well. We recommend all applications relying on our dynamic algorithm with the following exception:
For SQL 2005, we still recommend TF1118 for people on really high end machines with specific workload that cannot take advantage of the temp table caching feature. This should be a very limited scenario.
