SQL Server Performance Forum – Threads Archive

Temp Table and Using non clustered index

I am analyzing a new stored proc which is written by someone, where in he has created temp table using select into statement, after that he has created non clustered index on 4 columns in temp table. My Analyze:
This temp table would contain maximum of 100 rows at any time from another table, Also this data contains non unique data.
Execution plan shows that the table scan is used on this table instead of using non-clustered index. So, I think it is unnecessary to create temp table and creating non clustered index for the same. Instead I think I can go for creating permanent table without creating non clustered index and truncating the values at the end of the stored procedures Any suggestions are welcome. you can also mail me at [email protected] Regards,
Balasundaram

---

If you are sure that you will have up to 100 rows, use table variable without any pk/index.

---

A few more things to know: 1. Never use SELECT INTO to create a temp table. This causes unnecessary contention during the INSERT operation.
2. Generally, you only want to use a temp table (use table variables instead) if there are 10k or more rows.
–You need to test this on a case-by-case basis when you start getting a lot of rows; however, this is a safe metric number.
3. In the case you are mentioning, creating a permanent table would be a real waste of resources. The table variable is perfect for that scenario.
4. Be careful about using indexes on temp tables. When you decide to go down this route, test the entire process both ways. Many times, the creation of the index will outweigh the benefit of it. Sometimes though, it can take a process from minutes of runtime to seconds by creating one. Again, it’s a situation where you need to look at the overall execution plan, consider the costs of the plans, and create the process accordingly. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

]]>