You Probably Shouldn’t Index Your Temp Tables.

When you’ve got a process that uses temp tables, and you want to speed it up, it can be tempting to index the temp table to help work get done more quickly. However, in most cases – not all, but most – that’s a bad idea.

To explain why, I’m going to take a large Stack Overflow database and write a stored procedure:

CREATE OR ALTER PROC dbo.usp_TempTable_Heap @DisplayName NVARCHAR(40) AS BEGIN CREATE TABLE #MyUsers (Id INT, DisplayName NVARCHAR(40)); INSERT INTO #MyUsers (Id, DisplayName) SELECT Id, DisplayName FROM dbo.Users; SELECT Id, DisplayName FROM #MyUsers WHERE DisplayName = @DisplayName; END GO