Sahitya Bindu

• Sep 9th, 2006

 

#table in SQL Server : 

This # symbol prefixed with the tablename indicates that it is a local temporary table. This table will be existing till the session exists. Once the session expires the table will be dropped automatically. The table that is created with # symbol prefixed is temporary, so we can't give foreign key constraints to that table. Rest all the features are similar to that of the table that is permanent.

create table #Employee(EmpId int,EmpName Varchar(20))

##table in SQL Server:

This ## symbol prefixed with the tablename indicates that it is a Global Temporary table.This particular temporary table can be used by all the connections of SQL server and the temporary table is made available for all the users to access it.

The Temporary table can be mostly used for 'Select into' statements

Ritesh Medhe

• Sep 19th, 2006

 

Hi ,

Regards

#table and ##table are called as temporary tables in SQL Server. One with single # is called as local temp table and one with double # is called as global temp tables.Both are created are run time in tempdb (One of the SQL Server's system database).

When you create any table with single # the scope is limited to the session it cannot be reffered by any other session.

However when you want to share data across the sessions you should go for ## ie., global temperoary table data can be shared across multiple session

Note: As far as possible try avoiding temp tables as it kills your SQL quering techniques.

Thanks and Regards

Ritesh

Rakesh Shrivastav

• Nov 27th, 2006

 

Its worth mentioning @table (Declared table) which is useful when data is limited. I'm not considering performance issues, since they are out-of-scope of current article.

Dipti Bhardwaj

• Feb 20th, 2007

 

Hi Rakesh,

Can you guide me what do u mean by limited data  in case of table variables. I read it so many places regading using table variables when data is limited. But not sure the maximim capacity of data could be handled by table variables.
 
Regards,
 Dipti

Deepek Bhandari

• Mar 30th, 2007

 

#temp table: the table define with single '#' called local temp table, it can be used only a single project of a databse for exemple if we are making a new query in query builder and declare a #temp table then this temp table only can be used for that query. and if we close that query its destroy automatically.

##temp table: this is called global temp table. it can be used in multiple project. but once we close query builder we can not recalled that table. it will automatically destory when we close query builder or server management studio.