MS SQL – Table Variables & Temporary Tables (Transact-SQL)

Temporary Tables

  • visible only in current session of SQL Server;
  • automatically droped on session close;
  • can’t have foreign key constraints;
  • are created in temporary database of SQL Server tempdb;
  • should manually drop to keep cached only in memory.
CREATE TABLE #temp (id int);

INSERT INTO #temp
   SELECT id FROM Table1 WHERE ;

-- Use #temp data here.

DROP TABLE #temp;

Table Variables

  • if you are using SQL Server 2000 or higher;
  • always stay in memory;
  • don’t need to be dropped;
  • more flexibility.
DECLARE @temp table (id int);

INSERT INTO @temp
   SELECT id FROM Table1 WHERE ;

-- Use #temp data here.

Conclusion

If you have SQL Server 2000 or higher always should use table variables.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: