Temporary tables in sql server?

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 tab开发者_开发技巧les. Can you please explain me or any reference thank you.

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

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

Temporary 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.

SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:

•Local Temp Table

•Global Temp Table

Local Temp Table 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. Global Temp Table 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.

When to Use Temporary Tables?

•When we are doing large number of row manipulation in stored procedures. •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. •When we are having a complex join operation.

Points to Remember Before Using Temporary Tables -

•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.

Alternative Approach: Table Variable-

Alternative of Temporary table is the Table variable which can do all kinds of operations that we can perform in Temp table. Below is the syntax for using Table variable.

When to Use Table Variable Over Temp Table -

Tablevariable is always useful for less data. If the result set returns a large number of records, we need to go for temp table.