Can I have index created on temp tables (#temp) which are created dynamically in a stored procedure?

Question

I am creating temp tables (#temp_table) in my stored procedure. It is a huge table with large data. Then I am creating a index in the storeed procedure as it is required for faster query to the temp table. But when I execute the stored procedure, the index is not used. The index is not being created when the stored procedure is executed.

Pseudo code

CREATE PROC abcdefg
AS
...
SELECT col_a, col_b, col_c....    
  INTO #temp_table
  FROM .....
  WHERE ....
...
CREATE INDEX abc_idx ON #temp_table (col_a)
...
SELECT col_a FROM #temp_table WITH (INDEX (abc_idx))
...
GO

When I try to execute the stored proc, it is not recognizing the index. How can I fix this problem?

Answer 1

I tried the code you suggested using Sql Server 2005

ALTER PROCEDURE Test
AS
BEGIN
    CREATE TABLE #Test (
      ID INT,
      Code VARCHAR(20)
    )
    CREATE INDEX test_ind ON #Test (Code)

    INSERT INTO #Test (ID,Code) SELECT ID, Code FROM MyTable

    SELECT Code
    FROM #Test WITH(INDEX(test_ind))

    DROP TABLE #Test
END

When running the

EXEC Test

Command with Include Actual Execution Plan, it does show that the index was used.

Answer 2

This usually happens because the access plans for the temp tables have been generated before the indexes ever existed.

Fortunately, you can use table-level constraints to get around this problem. Since the indexes to support UNIQUE and PRIMARY KEY constraints are defined at the same time as the temp table, the optimizer will always be able to use these indexes.

Reference: Optimizing Performance / Indexes on Temp Tables