title: Indexing a single-use temporary table
body: <p>A colleague works in a business which uses Microsoft SQL Server. Their team creates stored procedures that are executed daily to create data extracts. The underlying tables are huge (some have billions of rows), so most stored procedures are designed such that first they extract only the relevant rows of these huge tables into temporary tables, and then the temp tables are joined with each other and with other smaller tables to create a final extract. Something similar to this:</p>
  
  <pre><code>SELECT COL1, COL2, COL3
  INTO #TABLE1
  FROM HUGETABLE1
  WHERE COL4 IN ('foo', 'bar');
  
  SELECT COL1, COL102, COL103
  INTO #TABLE2
  FROM HUGETABLE2
  WHERE COL14 = 'blah';
  
  SELECT COL1, COL103, COL306
  FROM #TABLE1 AS T1
  JOIN #TABLE2 AS T2
  ON T1.COL1 = T2.COL1
  LEFT JOIN SMALLTABLE AS ST
  ON T1.COL3 = ST.COL3
  ORDER BY T1.COL1;
  </code></pre>
  
  <p>Generally, the temporary tables are not modified after their creation (so no subsequent ALTER, UPDATE or INSERT operations). For the purpose of this discussion, let's assume the temporary tables are only used once later on (so only one SELECT query would rely on them).</p>
  
  <p>Here is the question: is it a good idea to index these temporary tables after they are created and before they are used in the subsequent query?</p>
  
  <p>My colleague believes that creating an index will make the join and the sort operations faster. I believe, however, that the total time will be larger, because index creation takes time. In other words, I assume that except for edge cases (like a temporary table which itself is extremely large, or the final SELECT query is very complex), SQL Server will use the statistics it has on the temporary tables to optimize the final query, and in doing so it will effectively index the temp tables as it sees fit.</p>
  
  <p>In other words, I am used to think that creating an index is only useful if you know that table is used often; a single-use temporary table that is dropped once the stored procedure is complete is not worth indexing.</p>
  
  <p>Neither of us knows enough about SQL Server optimizer to know in what ways we are right or wrong. Can you please help us better understand which of our assumptions are closer to truth?</p>