Temp Tables vs Table Varibles: The Great Debate

There seems to be a lot of confusion around the differences between  temp tables and table variables (in the classic sense; I'm not talking about in memory table types or anything here). Some people say you should only use table variables. Some people say you should only use temp tables. Most people caution a more nuanced approach. Here I want to cover what I think are the biggest issues in an attempt to shed some light on this surprisingly tricky topic.

Statistics This is usually the most important factor when considering which one to use. It boils down to two facts, and everything else flows from these: Temp Tables maintain statistics, Table Variables do not.

Table Variables Because table variables maintain no statistics, the Query Optimizer always assumes the contain exactly one row. As a result, any joins against a table variable, unless explicitly told to behave otherwise will (probably) alwaysbe a nested loop join.

As long as the number of rows in the table variable are small…

Script Out SQL Row Constructors

A while back I got tired of having to type out the long strings to script out rows from a table, so I built a snippet which does it for you. It’s almost illegible to read, but it determines the columns of the table you choose and builds a string to output each row. I'll admit that this could be further refined to add in some more precise data type handling (perhaps casting date fields to a certain varchar format, or handling more obscure data types), but for most every day purposes, this does just fine. Also, when you copy/paste the results into a query window, you'll have to manually cut out the first "union all" to get it to run. These omissions are just me being lazy, but there's no reason they can't be done, and if you want to make them or wrap this in a procedure, I encourage you to do so.

set nocount on

declare @string nvarchar(max) = 
    --This creates a framework for each column in the chosen table, which then gets serialized and applied to each row of the selected table
    select ''' + '''
        + quotename(column_name) 
        + case when data_type in ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'date', 'time', 'datetime', 'datetime2', 'datetimeoffset')
               then ' = '' + isnull(quotename(cast(' 
                    + quotename(column_name) 
                    + ' as varchar(8000)), ''''''''), ''NULL'') + '', '
               else ' = '' + isnull(cast('
                    + quotename(column_name)
                    + ' as varchar(8000)), ''NULL'') + '', '
    from .information_schema.columns
    where table_name = ''
        and column_name not in ('InsertDate', 'UpdateDate')
    for xml path('')

-- This dynamically selects those columns from the chosen table and with any optional where clause, and outputs the rows you can copy/paste into  query window
select @string = '
        ''union all select ' 
        + left(@string, len(@String) - 5) -- chop of leading ' + '
        + ' from .dbo. '
        + ' '

exec sp_executesql @string


Popular posts from this blog

JOINs Using Playing Cards

Named Constraints on Temp Tables

Master Data Services on Windows 10