0

Temporary tables and table variables in SQL Server

I will briefly explain the differences between temporary tables and table variables.

Temporary Tables (#)
CREATE TABLE #temp (col1 ...)
Table Variables (@)
DECLARE @temp TABLE (col1 ...)

What do the signs mean?

#

A single number sign represents a local temporary object that is only available to the current connection.

##

A double number sign represents a global temporary object that is available to all connections as long as the current connection is active.

@

A single at sign represents a local temporary variable that is only available to the current connection.

@@

A double at sign represents a global temporary variable that is available to all connections as long as the current connection is active.

Summary

When you create either type of table, it is physically stored in the server’s tempdb as well as in the transaction log. You can perform CRUD (create, read, update and delete) functions on either type. Temporary tables receive statistics while table variables do not. You are restricted with column defaults, types of indices and constraints with table variables. If your server reboots, you will lose both types. If you create a temporary table in a stored procedure or if you end your connection, it will be automatically dropped.

For an in-depth overview of the differences, I recommend reading this StackExchange post.

Norbert Krupa

Technical Consultant

Leave a Reply

Your email address will not be published. Required fields are marked *