T-SQL Table Variable vs. Temporary Table

Most of the time you don’t gain very much performance from obsessing about details but instead from focusing on finding flaws in the general design.. Once in a while it’s still fun to obsess about the details though, and this post is about just that, details.

I find myself using both temporary tables and table variables in MSSQL without any preference for either or, so I decided to dive a little deeper into if there is a difference, and more importantly when I should be using what.

The biggest difference between the two is that temporary tables are actually created on the disk like a regular table. This also in turn means that temporary tables are pretty much like any other table and support all the things you are used to. Table variables are instead stored in memory and suffer from limitations with the usage of constraints, default values on columns, as well as no indexes.

When reading the above, temporary tables seemed like a clear winner in most any situation, but as with all things first impressions can be deceiving. The “truth” is that temporary tables are not the best option when dealing with smaller sets of data as there is a good chance that the sql server will decide to update statistics and force a recompile. So in the case of smaller datasets table variables should actually be faster.

To get some real numbers on what google was telling me I created the simple test case below, and as predicted the table variable was faster when doing a insert and select with 100k rows (small data set).
But as with all things results will probably vary so try the code out for yourself, and let me know if you come to a completly different conclusion.

 -- Test settings
 DECLARE @loops AS INT = 100000
 DECLARE @topSize AS INT = 10

 -- Test variables
 DECLARE @temp AS NVARCHAR(MAX)
 DECLARE @resultstable TABLE
    (
      id SMALLINT PRIMARY KEY CLUSTERED ,
      method NVARCHAR(50) ,
      starttime DATETIME2 ,
      endtime DATETIME2 ,
      timeused_ms AS ( DATEDIFF(ms, starttime, endtime) ) PERSISTED
    )

 -- Clear out cache for true results
 DBCC freeproccache
 DBCC DROPCLEANBUFFERS

 -- Temp Table
 INSERT INTO @resultstable
        ( id, method, starttime )
 VALUES ( 1, 'Temporary Table', GETDATE() )

 CREATE TABLE #temptable ( c1 INT, c2 VARCHAR(MAX) )
 DECLARE @temptablecounter AS INT = 0 ,
    @innerresult1 AS NVARCHAR(MAX)
 WHILE @temptablecounter < @loops
    BEGIN
        SET @temptablecounter = @temptablecounter + 1
        INSERT  INTO #temptable
                ( c1, c2 )
        VALUES  ( RAND(123), NEWID() )
        -- select the last top 10 rows into a local variable..
        SET @temp = ( SELECT TOP ( @topSize )
                                ISNULL(c2 + CAST(c1 AS NVARCHAR(MAX)) + ' ,',
                                       '')
                      FROM      #temptable
                    FOR
                      XML PATH('')
                    )
    END
 DROP TABLE #temptable

 UPDATE r
 SET    endtime = GETDATE()
 FROM   @resultstable r
 WHERE  r.id = 1

 -- Table Variable
 INSERT INTO @resultstable
        ( id, method, starttime )
 VALUES ( 2, 'Table Variable', GETDATE() )

 DECLARE @variabletable TABLE ( c1 INT, c2 VARCHAR(MAX) )
 DECLARE @tempvariablecounter AS INT = 0 ,
    @innerresult2 AS NVARCHAR(MAX)
 WHILE @tempvariablecounter < @loops
    BEGIN
        SET @tempvariablecounter = @tempvariablecounter + 1
        INSERT  INTO @variabletable
                ( c1, c2 )
        VALUES  ( RAND(123), NEWID() )
        -- select the last top 10 rows into a local variable..
        SET @temp = ( SELECT TOP ( @topSize )
                                ISNULL(c2 + CAST(c1 AS NVARCHAR(MAX)) + ' ,',
                                       '')
                      FROM      @variabletable
                    FOR
                      XML PATH('')
                    )
    END

 UPDATE r
 SET    endtime = GETDATE()
 FROM   @resultstable r
 WHERE  r.id = 2

 -- Display results
 SELECT rt.id ,
        rt.method ,
        rt.starttime ,
        rt.endtime ,
        rt.timeused_ms
 FROM   @resultstable rt