There are two types of temp tables in SQL Server :
Local temp : Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.
--> CREATE TABLE #LocalTempTable( UserID int)
Global temp : Global temp 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.
--> CREATE TABLE ##GlobalTempTable( UserID int)
Hope this will help you.
DNS Gold Member
"Failure is the path of least persistence"
Direction is important than speed
As we make use of temporary variables in programming just to store value for some scope, in database tables also we create temp tables to store few result set.
Later on we can copy those result set from temp table to our main table, then we delete the temp table after making use of it.
If you learn from defeat..
You haven't really lost..
Editor: DNS Forum.
This is a very good question. Even every developer would get this question on Temporary table.
The best thing to consider with temporary table is that the scope of the table will get off when the session is closed. This means that temporary tables will be automatically deleted by the database engine when the connection/Session gets closed.
If you create Table and insert data temporarily, you have to take care of manually deleting the table when the task is completed. As Most of the database developers are missing to drop the tables and are not ready to take this additional overhead, We are using temporary table for this purpose.
When we use temporary table, The size of the database remains the same and will not get increased. Even if it is increased to hold the temporary storage, It will get cleaned up and the spaces will be reclaimed,
Please mark this as Answer, if this helps
<< Database Administrator >>
Jesus saves! The rest of us better make backups...
There are various reasons, we use the temp tables in the Sql Server developments:
1. Temp tables are used for temporary purpose. So if we need some intermediate calculations of the data and finally want that data as per the internal calculations, we can use temp tables.
2. Temp tables have the scope limited to the stored proc, so the memory which will be used in internal calculation will be wiped out once it is out of scope.
3. Temp tables are based on the sessions, so for each user, there will be separate temp table and when for the particular session, it comes out of the stored proc, the memory will be deallocated. This thing will not be for the general tables.
4. If we use temp tables, we are not increasing the size of the database.
Hope it will be helpful to you.
Pawan Awasthi(DNS MVM)
+91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
Do Good... Enjoy your life.....
If we want to perform action once, at that time we can go with temp table after complete that action automatically temp table is deleted. But this is not happen when we go with table, its occupy memory, if we want to delete the table we need to perform some action in to that.
Hope you understood...
Give respect to your work, Instead of trying to impress your boss.
Blog : http://naveens-dotnet.blogspot.in/