Home > SQL Server basics > #temp table or a @table variable

#temp table or a @table variable

In a stored procedure, you often have a need for storing a set of data within the procedure, without necessarily needing that data to persist beyond the scope of the procedure.

1. Local Temporary Tables(# table)

CREATE TABLE #employee
(
empid INT,
empname VARCHAR(100)
)

  • A temporary table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions.
  • The data in this #temp table is visible only to the current scope usually a stored procedure, or a set of nested stored procedures.
  • The table gets cleared up automatically when the current procedure goes out of scope but try to delete before exiting stored procedure.
  • Local temp tables name is limited to 116 characters.

Use DROP TABLE statment like following.

DROP TABLE #people

2.Table Variables(@ table)

DECLARE @employee TABLE
(
empid INT,
empname VARCHAR(100)
)

  • A table variable is created in memory, and so performs slightly better than #temp tables.
  • Table variables are automatically cleared when the procedure or function goes out of scope, so you don’t have to remember to drop or clear the data.
  • You cannot truncate a table variable.
  • Table variables cannot be altered after they have been declared.
  • Unlike a #temp table, you cannot drop a table variable when it is no longer necessary—you just need to let it go out of scope.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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: