Temporary Tables vs Table Variables SQL Server 2005: A Practical Guide for Developers

As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables. (This is an overly broad guideline with of course lots of exceptions - see below and following articles.)

Table variables don't participate in transactions and SELECTs are implicitly with NOLOCK. The transaction behaviour can be very helpful, for instance if you want to ROLLBACK midway through a procedure then table variables populated during that transaction will still be populated!

temporary tables vs table variables sql server 2005

Download

Using Table Variables within user-defined functions enables those functions to be used more widely (see CREATE FUNCTION documentation for details). If you're writing a function you should use table variables over temp tables unless there's a compelling need otherwise.

Both table variables and temp tables are stored in tempdb. But table variables (since 2005) default to the collation of the current database versus temp tables which take the default collation of tempdb (ref). This means you should be aware of collation issues if using temp tables and your db collation is different to tempdb's, causing problems if you want to compare data in the temp table with data in your database.

It seems generally untrue that there is any difference in quantity of logging (at least for insert/update/delete operations to the table itself though I have since found that there is some small difference in this respect for cached temporary objects in stored procedures due to additional system table updates).

The table variable version actually has a few extra log entries because it gets an entry added to (and later removed from) the sys.syssingleobjrefs base table but overall had a few less bytes logged purely as the internal name for table variables consumes 236 less bytes than for #temp tables (118 fewer nvarchar characters).

A temp table can have indexes, whereas a table variable can only have a primary index. If speed is an issue Table variables can be faster, but obviously if there are a lot of records, or the need to search the temp table of a clustered index, then a Temp Table would be better.

Schema Modifications Schema modifications are possible on temporary tables but not on table variables. Although schema modifi cations are possible on temporary tables, avoid using them because they cause recompilations of statements that use the tables.

The other main difference is that table variables don't have column statistics, where as temp tables do. This means that the query optimiser doesn't know how many rows are in the table variable (it guesses 1), which can lead to highly non-optimal plans been generated if the table variable actually has a large number of rows.

Consider also that you can often replace both with derived tables which may be faster as well. As with all performance tuning, though, only actual tests against your actual data can tell you the best approach for your particular query.

In SQL the Temporary tables are stored in the TempDB and the local temporary tables are only visible in the current session and it will not be visible in another session. This can be shared between nested stored procedure calls. The Global temporary tables are visible to all other sessions and they are destroyed when the last connection referencing table is closed. For Example,

Table variables are similar to tempTables, a table variable is also created in TempDB. The scope of a table variable is the batch, stored procedure, or statement block in which it is declared. They can be passed as parameters between procedures. The same query can be written using Table variable by

Temporary tables are used by every DB developer, but they're not likely to be too adventurous with their use, or exploit all their advantages. They can improve your code's performance and maintainability, but can be the source of grief to both developer and DBA if things go wrong and a process grinds away inexorably slowly. We asked Phil for advice, thinking that it would be a simple explanation.

Temporary tables come in different flavours including, amongst others, local temporary tables (starting with #), global temporary tables (starting with ##), persistent temporary tables (prefixed by TempDB..), and table variables.(starting with (@)

Now if you can make what goes into the tables unique, you can then use a primary key constraint on these tables. This allowed the optimiser to use a clustered index seek instead of a table scan and the execution time was too rapid to measure

The Table-Valued Parameter (TVP) is a special type of table variable that extends its use. When table variables are passed as parameters, the table is materialized in the TempDB system database as a table variable and passed by reference, a pointer to the table in the TempDB.

Another oddity of the local temporary table (and the local temporary stored procedure) is that it has a different name in the metadata to the one you give it in your routine or batch. If the same routine is executed simultaneously by several processes, the Database Engine needs to be able to distinguish between the identically-named local temporary tables created by the different processes. It does this by adding a numeric string to each local temporary table name left-padded by underscore characters. Although you specify the short name such as #MyTempTable, what is actually stored in TempDB is made up of the table name specified in the CREATE TABLE statement and the suffix. Because of this suffix, local temporary table names must be 116 characters or less.

With Local temporary table (names that begin with #), what goes on under the hood is surprisingly similar to table variables. As with Table Variables, Local Temporary tables are private to the process that created it. They cannot therefore be used in views and you cannot associate triggers with them.

You cannot easily tell which session or procedure has created these tables. This is because, if the same stored procedure is executed simultaneously by several processes, the Database Engine needs to be able to distinguish the same tables created by the different processes. The Database Engine does this by internally appending a left-padded numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sys.objects view in TempDB is made up of the table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, the table name specified for a local temporary name must be less than 116 characters.

You can associate rules, defaults, and indexes with temporary tables, but you cannot create views on temporary tables or associate triggers with them. You can use a user-defined datatype when creating a temporary table only if the datatype exists in TempDB

Stored procedures can reference temporary tables that are created during the current session. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table with the same name.

SQL Server provides the concept of temporary table which helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside tempdb database.

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

The scope of Global temporary table is the same for the entire user for a particular connection. We need to put "##" with the name of Global temporary tables. Below is the syntax for creating a Global Temporary Table:

I'm learning more details in table variable. It says that temp tables are always on disk, and table variables are in memory, that is to say, the performance of table variable is better than temp table because table variable uses less IO operations than temp table.

But I don't know what the "too many records" is. 100,000 records? or 1000,000 records? How can I know if a table variable I'm using is in memory or is on disk? Is there any function or tool in SQL Server 2005 to measure the scale of the table variable or letting me know when the table variable is put on disk from memory?

Regarding the question in the title though as to when to use a table variable vs a local temporary table you don't always have a choice. In functions, for example, it is only possible to use a table variable and if you need to write to the table in a child scope then only a #temp table will do (table-valued parameters allow readonly access).

If you need an index that cannot be created on a table variable then you will of course need a #temporary table. The details of this are version dependant however. For SQL Server 2012 and below the only indexes that could be created on table variables were those implicitly created through a UNIQUE or PRIMARY KEY constraint. SQL Server 2014 introduced inline index syntax for a subset of the options available in CREATE INDEX. This has been extended since to allow filtered index conditions. Indexes with INCLUDE-d columns or columnstore indexes are still not possible to create on table variables however.

If you will be repeatedly adding and deleting large numbers of rows from the table then use a #temporary table. That supports TRUNCATE (which is more efficient than DELETE for large tables) and additionally subsequent inserts following a TRUNCATE can have better performance than those following a DELETE as illustrated here.

Table variables does not have distribution statistics, they will not trigger recompiles. Therefore, in many cases, the optimizer will build a query plan on the assumption that the table variable has no rows. For this reason, you should be cautious about using a table variable if you expect a larger number of rows (greater than 100). Temp tables may be a better solution in this case. 2ff7e9595c