SQL 使用与 vs 声明一个临时表：性能/差异？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5435319/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using with vs declare a temporary table: performance / difference?
提问by Mike
I have created a sql function in SQLServer 2008that declared a temporary table and uses it to compute a moving average on the values inside
我在SQLServer 2008中创建了一个 sql 函数，它声明了一个临时表并使用它来计算内部值的移动平均值
declare @tempTable table
(
GeogType nvarchar(5),
GeogValue nvarchar(7),
dtAdmission date,
timeInterval int,
fromTime nvarchar(5),
toTime nvarchar(5),
EDSyndromeID tinyint,
nVisits int
)
insert @tempTable select * from aces.dbo.fEDVisitCounts(@geogType, @hospID,DATEADD(DD,-@windowDays + 1,@fromDate),
@toDate,@minAge,@maxAge,@gender,@nIntervalsPerDay, @nSyndromeID)
INSERT @table (dtAdmission,EDSyndromeID, MovingAvg)
SELECT list.dtadmission
, @nSyndromeID
, AVG(data.nVisits) as MovingAvg
from @tempTable as list
inner join @tempTable as data
ON list.dtAdmission between data.dtAdmission and DATEADD(DD,@windowDays - 1,data.dtAdmission)
where list.dtAdmission >= @fromDate
GROUP BY list.dtAdmission
but I also found out that you can declare the tempTable like this:
但我也发现你可以像这样声明 tempTable：
with tempTable as
(
select * from aces.dbo.fEDVisitCounts('ALL', null,DATEADD(DD,-7,'01-09-2010'),
'04-09-2010',0,130,null,1, 0)
)
Question: Is there a major difference in these two approaches? Is one faster than the other or more common / standard?I would think the declare is faster since you define what the columns you are looking for are.. Would it also be even faster if I were to omit the columns that were not used in the calculations of moving average?(not sure about this one since it has to get all of the rows anyways, though selecting less columns makes intuitive sense that it would be faster/less to do)
问题：这两种方法有什么主要区别吗？一个比另一个更快或更常见/标准吗？我认为声明会更快，因为您定义了要查找的列。如果我省略移动平均线计算中未使用的列，它还会更快吗？（不确定这个列）因为无论如何它都必须获取所有行，尽管选择较少的列可以直观地感觉到它会更快/更少做）
I also have found a create temporary table @tablefrom here How to declare Internal table in MySQL?but I don't want the table to persist outside of the function (I am not sure if the create temporary table does this or not.)
我还create temporary table @table从这里找到了如何在 MySQL 中声明内部表？但我不希望该表在函数之外保留（我不确定创建临时表是否执行此操作。）
回答by Martin Smith
The @tablesyntax creates a table variable (an actual table in tempdb) and materialises the results to it.
该@table语法创建一个表变量（中的实际表tempdb）并将结果具体化到它。
The WITHsyntax defines a Common Table Expressionwhich is not materialised and is just an inline View.
该WITH语法定义了一个公共表表达式未兑现而仅仅是一个内嵌视图。
Most of the time you would be better off using the second option. You mention that this is inside a function. If this is a TVF then most of the time you want these to be inline rather than multi statement so they can be expanded out by the optimiser - this would instantly disallow the use of table variables.
大多数情况下，您最好使用第二个选项。你提到这是在一个函数内。如果这是一个 TVF，那么大多数时候您希望它们是内联而不是多语句，以便优化器可以扩展它们 - 这将立即禁止使用表变量。
Sometimes however (say the underlying query is expensive and you want to avoid it being executed multiple times) you might determine that materializing the intermediate results improves performance in some specific cases. There is currently no wayof forcing this for CTEs (without forcing a plan guide at least)
然而，有时（假设底层查询很昂贵并且您希望避免它被多次执行）您可能会确定在某些特定情况下实现中间结果可以提高性能。有目前还没有办法迫使本作的热膨胀系数（不强制至少一个计划指南）
In that eventuality you (in general) have 3 options. A @tablevariable, #localtemptable and a ##globaltemptable. However only the first of these is permitted for use inside a function.
在这种情况下，您（通常）有 3 个选择。一@tablevariable，#localtemp表与##globaltemp表。但是，在函数中只允许使用其中的第一个。
For further information regarding the differences between table variables and #temp tables see here.
有关表变量和#temp 表之间差异的更多信息，请参见此处。
回答by SQLMenace
In addition to what Martin answered
除了马丁的回答
;with tempTable as
(
select * from aces.dbo.fEDVisitCounts('ALL', null,DATEADD(DD,-7,'01-09-2010'),
'04-09-2010',0,130,null,1, 0)
)
SELECT * FROM tempTable
can also be written like this
也可以这样写
SELECT * FROM
(
select * from aces.dbo.fEDVisitCounts('ALL', null,DATEADD(DD,-7,'01-09-2010'),
'04-09-2010',0,130,null,1, 0)
) AS tempTable --now you can join here with other tables
