Temp Tables vs Table Variables

There are two types of temporary tables that you’ll use: local and global, and these have a specific designation. Local tables are designated with one pound sign and global temporary tables are designated with two pound signs. If you create a table that doesn’t have a pound sign, or two pound signs, in front of it within tempdb, it will not be dropped when your session is dropped. Unless you explicitly drop it, it will remain in tempdb until the SQL Server instance is restarted, or it’s explicitly dropped.

Global tables can be used by other sessions.

Table variables, in general, are best used when you know you’re always going to have a small set of rows, less than a hundred rows. And you want to pass those either to another procedure or through some code. The scope of table variables is also limited to a batch 

Table variables are created like any other variable, using the DECLARE statement. Many believe that table variables exist only in memory, but that is simply not true. They reside in the tempdb database much like local SQL Server temp tables. Also like local SQL temp tables, table variables are accessible only within the session that created them. However, unlike SQL temp tables the table variable is only accessible within the current batch. They are not visible outside of the batch, meaning the concept of session hierarchy can be somewhat ignored.

As far as performance is concerned table variables are useful with small amounts of data (like only a few rows). Otherwise a SQL Server temp table is useful when sifting through large amounts of data. So for most scripts you will most likely see the use of a SQL Server temp table as opposed to a table variable. Not to say that one is more useful than the other, it’s just you have to choose the right tool for the job.

Example:

DECLARE @summaryTable AS TABLE
(
	ItemID			TINYINT IDENTITY(1,1),
	TransactionDate		DATE,
	Description		VARCHAR(50),
	TransactionCount	INT
);

INSERT INTO @summaryTable
(
	TransactionDate,
	Description,
	TransactionCount
)
SELECT	GETDATE(),
	'Test',
	5;

SELECT * FROM @summaryTable;