//
you're reading...
SQL Server

What are temporary tables in Sql Server

Download/Read PDF version of article here

 

What are temporary tables in Sql Server?

Introduction: Temporary tables are a useful tool in SQL Server provided to allow for short term use of data .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.

Types of temporary table:

1) Local temp table: Local temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed. they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

2) Global temp table: Global temporary tables are available to any connection once created, and are dropped when the last connection using it is closed. 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.

Before we start creating temp table, we need to keep below points in mind

  • Temporary table created on tempdb of SQL Server. This is a separate database. So, this is an additional overhead and can causes performance issues.
  • Number of rows and columns need to be as minimum as needed.
  • Tables need to be deleted when they are done with their work.

 

Create Local temp table

1. Using CREATE

 CREATE TABLE #LocalTempTable(

UserID int,

UserName varchar(50),

UserAddress varchar(150))

2. Using SELECT...INTO

SELECT age as DummField1

Lastname as DummyField2

INTO #myTempTable

FROM DummyTable

 

Now let us see how to insert into temp table;

insert into #LocalTempTable values ( 1, 'Vishal','India');

 

How to select from temp table;

select * from #LocalTempTable

After execution of all these statements, if you close the query window and again execute "Insert" or "Select" Command, it will throw the following error:

But if we choose to close the query window after executing above commands and again execute insert or select command, it will throw an error saying invalid object name #LocalTempTable.

Reason: This is because the scope of Local Temporary table is only bounded with the current connection of current user.

Create Global temp table:

1. Using CREATE

CREATE TABLE ##NewGlobalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

 

2. Using SELECT...INTO
SELECT age as DummyField1,
         Lastname as DummyField2
INTO ##myTempTable
FROM DummyTable

The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:

 

Now let us see how to insert into temp table;

insert into ##NewGlobalTempTable values ( 1, 'Vishal','India');

 

How to select from temp table;

select * from ##NewGlobalTempTable

 

Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.

When to use Temp tables;

1) When we are doing large number of row manipulation in stored procedure

2) When we have complex join operation.

3) This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.

 

Deciding between Local and Global
When deciding which type of table to use asks you two questions.

1) “Do I need this data to persist when I am done using it?” If so, I need a standard table, not a temporary table.

2) Do I need the data to be accessed outside of my single process?” This question can sometimes be a little tougher to figure out, so I have a simple suggestion. Make it a local temporary table for now, and if you find out you need a larger scope, change it later.

Hope you enjoyed reading

Cheers

Download/Read PDF version of article here

 

Advertisements

About Vishal

Vishal Nayan is a seasoned professional with hand on Experience on Mircrosoft Technologies. He always look for challenging IT position that allows him to learn new Microsoft Technologies while utilizing experience of Project Development and Software Engineering Ethics. A MCP in WCF ,and looking forward for more.

Discussion

3 thoughts on “What are temporary tables in Sql Server

  1. can we create temp table in ms access.

    Posted by akrama | 2012/01/15, 4:30 AM
  2. Hello, its pleasant post concerning media print,
    we all be familiar with media is a great source of data.

    Posted by Rvtl Anti Aging Solution | 2013/05/09, 11:51 PM

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

Categories

Follow Vishalnayan on WordPress.com

Blog Stats

  • 215,572 hits
%d bloggers like this: