是否可以向临时表添加索引？ create #t 和 declare @t 有什么区别

Question

我需要做一个非常复杂的查询。在某一时刻，这个查询必须连接到一个无法被索引的视图。这个视图也是一个连接大表的复杂视图。

View 的输出可以简化为：

 PID (int), Kind (int), Date (date), D1,D2..DN

其中 PID 和 Date 和 Kind 字段不是唯一的（可能有不止一行具有相同的 pid、kind、date 组合），但是像这样在 join 中使用的那些

left join ComplexView mkcs on mkcs.PID=q4.PersonID and mkcs.Date=q4.date and mkcs.Kind=1
left join ComplexView mkcl on mkcl.PID=q4.PersonID and mkcl.Date=q4.date and mkcl.Kind=2
left join ComplexView mkco on mkco.PID=q4.PersonID and mkco.Date=q4.date and mkco.Kind=3

现在，如果我只是这样做，查询的执行将花费大量时间，因为我假设复杂视图运行了 3 次，并且在其大量行中只有一些实际使用（例如，在 40000 中只有 2000 是用过的）

我所做的是声明@temptable，然后插入@temptable select * from ComplexView where Date… - 每个查询一次我只从我的 ComplexView 中选择我要使用的行，然后我加入这个@temptable。

这显着减少了执行时间。

但是，我注意到，如果我在我的数据库中创建一个表，并在 PID、Kind、Date（非唯一聚集）上添加一个聚集索引并从该表中获取数据，然后从该表中删除 * 并插入到该表中复杂视图中的表需要几秒钟（3 或 4 秒），然后在我的查询中使用此表（左加入 3 次）将查询时间缩短到一半，从 1 分钟到 30 秒！

所以，我的问题是，首先 - 是否可以在声明的 @temptables 上创建索引。然后 - 我看到人们谈论“创建#temptable”语法。也许这就是我需要的？我在哪里可以了解声明 @temptable 和创建 #temptable 之间的区别？我应该用什么来做像我这样的查询？ （如果重要，此查询适用于 MS Reporting Services 报告）。

原文由 Istrebitel 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

这不是一个完整的答案，但 #table 将创建一个您需要删除的临时表，否则它将保留在您的数据库中。 @table 是一个表变量，不会比您的脚本持续更长时间。

另外，我认为这篇文章将回答您问题的另一部分。

在表变量上创建索引

原文由 Brian Dishaw 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

要扩展 Alex K. 的答案，您 可以 在临时表上创建 PRIMARY KEY

 IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
 DROP TABLE #tempTable

CREATE TABLE #tempTable
(
   Id INT PRIMARY KEY
  ,Value NVARCHAR(128)
)

INSERT INTO #tempTable
VALUES
     (1, 'first value')
    ,(3, 'second value')
    -- will cause Violation of PRIMARY KEY constraint 'PK__#tempTab__3214EC071AE8C88D'. Cannot insert duplicate key in object 'dbo.#tempTable'. The duplicate key value is (1).
    --,(1, 'first value one more time')

SELECT  * FROM #tempTable

原文由 svonidze 发布，翻译遵循 CC BY-SA 4.0 许可协议