Using with vs declare a temporary table: performan

问题:

I have created a sql function in SQLServer 2008 that declared a temporary table and uses it to compute a moving average on the values inside

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:

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 @table from 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.)

回答1:

The @table syntax creates a table variable (an actual table in tempdb) and materialises the results to it.

The WITH syntax defines a Common Table Expression which is not materialised and is just an inline View.

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.

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 way of forcing this for CTEs (without forcing a plan guide at least)

In that eventuality you (in general) have 3 options. A @tablevariable, #localtemp table and a ##globaltemp table. However only the first of these is permitted for use inside a function.

For further information regarding the differences between table variables and #temp tables see here.

回答2:

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

回答3:

Another difference is the second way (with tableName as ...) result in a ReadOnly temporary table. But in the first way (declare table) you are able to change your table data.