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

Is it possible to add index to a temp table? And what's the difference between create #t and declare @t

提问by Istrebitel

I need to do a very complex query. At one point, this query must have a join to a view that cannot be indexed unfortunately. This view is also a complex view joining big tables.

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

View's output can be simplified as this:

视图的输出可以简化为：

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

where PID and Date and Kind fields are not unique (there may be more than one row having same combination of pid,kind,date), but are those that will be used in join like this

其中 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

Now, if I just do it like this, execution of the query takes significant time because the complex view is ran three times I assume, and out of its huge amount of rows only some are actually used (like, out of 40000 only 2000 are used)

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

What i did is declare @temptable, and insert into @temptable select * from ComplexView where Date... - one time per query I select only the rows I am going to use from my ComplexView, and then I am joining this @temptable.

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

This reduced execution time significantly.

这大大减少了执行时间。

However, I noticed, that if I make a table in my database, and add a clustered index on PID,Kind,Date (non-unique clustered) and take data from this table, then doing delete * from this table and insert into this table from complex view takes some seconds (3 or 4), and then using this table in my query (left joining it three times) take down query time to half, from 1 minute to 30 seconds!

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

So, my question is, first of all - is it possible to create indexes on declared @temptables. And then - I've seen people talk about "create #temptable" syntax. Maybe this is what i need? Where can I read about what's the difference between declare @temptable and create #temptable? What shall I use for a query like mine? (this query is for MS Reporting Services report, if it matters).

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

采纳答案by Brian Dishaw

It's not a complete answer but #table will create a temporary table that you need to drop or it will persist in your database. @table is a table variable that will not persist longer than your script.

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

Also, I think this post will answer the other part of your question.

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

Creating an index on a table variable

在表变量上创建索引

回答by Alex K.

#tablenameis a physical table, stored in tempdbthat the server will drop automatically when the connection that created it is closed, @tablenameis a table stored in memory & lives for the lifetime of the batch/procedure that created it, just like a local variable.

#tablename是一个物理表，存储在tempdb创建它的连接关闭时服务器将自动删除，@tablename是一个存储在内存中的表，并且在创建它的批处理/过程的生命周期内一直存在，就像局部变量一样。

You can only add a (non PK) index to a #temptable.

您只能向#temp表添加（非 PK）索引。

create table #blah (fld int)
create nonclustered index idx on #blah (fld)

回答by JeffO

Yes, you can create indexes on temp tables or table variables. http://sqlserverplanet.com/sql/create-index-on-table-variable/

是的，您可以在临时表或表变量上创建索引。http://sqlserverplanet.com/sql/create-index-on-table-variable/

回答by svonidze

To extend the Alex K.'s answer, you cancreate the PRIMARY KEYon a temp table

要扩展 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

回答by binki

The @tableNamesyntax is a table variable. They are rather limited. The syntax is described in the documentation for DECLARE @local_variable. You can kind of have indexes on table variables, but only indirectly by specifying PRIMARY KEYand UNIQUEconstraints on columns. So, if your data in the columns that you need an index on happens to be unique, you can do this. See this answer. This may be “enough” for many use cases, but only for small numbers of rows. If you don't have indexes on your table variable, the optimizer will generally treat table variables as if they contain one row (regardless of how many rows there actually are) which can result in terrible query plans if you have hundreds or thousands of rows in them instead.

该@tableName语法是表变量。它们相当有限。语法中描述为文档DECLARE @local_variable。您可以在表变量上建立索引，但只能通过在列上指定PRIMARY KEY和UNIQUE约束来间接进行。因此，如果需要索引的列中的数据恰好是唯一的，则可以执行此操作。看到这个答案。对于许多用例来说，这可能“足够”，但仅适用于少量行。如果您的表变量没有索引，优化器通常会将表变量视为包含一行（无论实际有多少行），如果您有数百或数千行，这可能会导致糟糕的查询计划在他们中。

The #tableNamesyntax is a locally-scoped temporary table. You can create these either using SELECT…INTO #tableNameor CREATE TABLE #tableNamesyntax. The scope of these tables is a little bit more complex than that of variables. If you have CREATE TABLE #tableNamein a stored procedure, all references to #tableNamein that stored procedure will refer to that table. If you simply reference #tableNamein the stored procedure (without creating it), it will look into the caller's scope. So you can create #tableNamein one procedure, call another procedure, and in that other procedure read/update #tableName. However, once the procedure that created #tableNameruns to completion, that table will be automatically unreferenced and cleaned up by SQL Server. So, there is no reason to manually clean up these tables unless if you have a procedure which is meant to loop/run indefinitely or for long periods of time.

该#tableName语法是一个局部范围的临时表。您可以使用SELECT…INTO #tableName或CREATE TABLE #tableName语法创建这些。这些表的范围比变量的范围要复杂一些。如果您有CREATE TABLE #tableName一个存储过程，则该存储过程中的所有引用#tableName都将引用该表。如果您只是#tableName在存储过程中引用（而不创建它），它将查看调用者的作用域。所以你可以#tableName在一个过程中创建，调用另一个过程，然后在另一个过程中 read/update #tableName。但是，一旦创建的过程#tableName运行完成，该表将被 SQL Server 自动取消引用和清理。因此，没有理由手动清理这些表，除非您有一个旨在无限期或长时间循环/运行的过程。

You can define complex indexes on temporary tables, just as if they are permanent tables, for the most part. So if you need to index columns but have duplicate values which prevents you from using UNIQUE, this is the way to go. You do not even have to worry about name collisions on indexes. If you run something like CREATE INDEX my_index ON #tableName(MyColumn)in multiple sessions which have each created their own table called #tableName, SQL Server will do some magicso that the reuse of the global-looking identifier my_indexdoes not explode.

大多数情况下，您可以在临时表上定义复杂索引，就像它们是永久表一样。因此，如果您需要为列编制索引但有重复的值阻止您使用UNIQUE，这就是要走的路。您甚至不必担心索引上的名称冲突。如果你CREATE INDEX my_index ON #tableName(MyColumn)在多个会话中运行类似的东西，每个会话都创建了自己的表，称为#tableName，SQL Server 会做一些魔术，这样全局标识符的重用my_index不会爆炸。

Additionally, temporary tables will automatically build statistics, etc., like normal tables. The query optimizer will recognize that temporary tables can have more than just 1 row in them, which can in itself result in great performance gains over table variables. Of course, this also is a tiny amount of overhead. Though this overhead is likely worth it and not noticeable if your query's runtime is longer than one second.

此外，临时表会像普通表一样自动构建统计信息等。查询优化器将识别出临时表中可以有不止 1 行，这本身可以导致比表变量更大的性能提升。当然，这也是很小的开销。尽管如果您的查询的运行时间超过一秒，这种开销可能是值得的，并且不会引起注意。