This article is half-done without your Comment! *** Please share your thoughts via Comment ***
I have given one assignment to a database developer team for preparing general database development practices.
I have found one point in that document: “Do not create Indexes on Temp Tables”.
The use of Temp Tables is very common in SQL Server. Most of the SQL database developers are storing their data into temp tables.
Let me, explore something about, the best practices of Temp Tables.
In general advice, we should avoid the use of temp tables because it requires more CPU/IO while accessing the TempDB.
When you are storing data into temp tables, it stores in TempDB files means the physical disk operation is involved.
Like any other regular table, temp table also requires disk space to store temporary data and other resources for manipulation.
I have seen that many developers are saving a significant number of records in Temp Tables and they are not thinking even for Indexing on Temp Tables.
If you don’t know, please note: You can create a Clustered and Non-Clustered Indexes on Temp Tables which improves your query performance.
Again, more indexes will create more problems because it needs space and manipulation.
First, you should analyze the volume of data, execution plan and accordingly you should apply indexes on Temp tables.
Personally, I have checked the performance of Temp Tables with Clustered Index, which is much better than Temp Table without an Indexes.
I am rejecting all comments and points which say that ” we should not apply Indexes on Temp Tables. “
SQL Server: Difference between Temporary Table and Table Variable
