Temporary tables in sql server?

Question

hi,

I have a doubt Why Should we use temporary table is there any special thing in temporary table and where should we use the temporary tables. Can you please explain me or any reference thank you.

Answer 1

There are many uses for temporary tables. They can be very useful in handling data in complex queries. Your question is vague, and does not really have an answer, but I am linking to some temporary table documentation.

They have most of the same capabilities of table, including constraints and indexing. They can be global or limited to current scope. They can also be inefficient, so be cautious as always.

http://www.sqlservercentral.com/articles/T-SQL/temptablesinsqlserver/1279/
http://msdn.microsoft.com/en-us/library/aa258255%28SQL.80%29.aspx

Answer 2

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. Each of the four table options has its own purpose and use, and each has its benefits and issues:

* Normal tables are exactly that, physical tables defined in your database.

* Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them.

* Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. Both local temporary tables and global temporary tables are physical tables created within the tempdb database.

* Table variables are stored within memory but are laid out like a table. Table variables are partially stored on disk and partially stored in memory. It's a common misconception that table variables are stored only in memory. Because they are partially stored in memory, the access time for a table variable can be faster than the time it takes to access a temporary table.

Which one to use:

* If you have less than 100 rows generally use a table variable.  Otherwise use  a temporary table.  This is because SQL Server won't create statistics on table variables.
* If you need to create indexes on it then you must use a temporary table.
* When using temporary tables always create them and create any indexes and then use them.  This will help reduce recompilations.  The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.

Please refer this page http://www.sqlteam.com/article/temporary-tables