我可以在存储过程中动态创建的临时表(#temp)上创建索引吗？

Question

我正在创建临时表(#temp_table)在我的存储过程中。这是一个带有大量数据的巨型表。然后我在存储过程中创建了一个索引，因为它对于更快的查询临时表是必需的。但是当我执行存储过程时，索引没有被使用。索引在存储过程执行时没有被创建。

伪代码

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

当我尝试执行存储过程时，它无法识别索引。我该如何解决这个问题？

- Nirmal Singh Raja Reegan

Answer 1

这通常是由于临时表的访问计划在索引存在之前就已生成造成的。幸运的是，您可以使用表级约束来解决这个问题。由于支持UNIQUE和PRIMARY KEY约束的索引与临时表同时定义，优化器将始终能够使用这些索引。

参考：优化性能/临时表上的索引

- OMG Ponies

---

谢谢OMG。你的建议很有用。 - Nirmal Singh Raja Reegan

@Ponies - 你提到的文章是2004年的，但可以认为里面关于#temp表的建议仍然是“最佳实践”吗？ - mg1075

Answer 2

我尝试了你建议的代码，使用了 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

当运行时，

EXEC Test

使用包含实际执行计划的命令，可以显示索引的使用情况。

- Adriaan Stander

---

感谢Astander的快速回复。我测试了你的解决方案，它运行良好。差异似乎在于存储过程执行"SELECT INTO #temp"和"CREATE TABLE #temp"查询的方式。另一个不太理想的解决方案是使用"with recompile"选项创建存储过程。这也起作用了。再次感谢你的帮助。 - Nirmal Singh Raja Reegan