Temporary tables are used most often to provide workspace for the intermediate results when processing data within a batch or procedure.
They are also used to pass a table from a table-valued function, to pass table-based data between stored procedures or, more recently in the form of Table-valued parameters, to send whole read-only tables from applications to SQL Server routines, or pass read-only temporary tables as parameters. Once finished with their use, they are discarded automatically.
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.
A quick summary:
• #temp tables can be indexed, can have UNIQUE indexes/constraints, can be references more than one time in the same query, can be referenced (FROM or JOIN) by more than one query. Derived tables can be referenced (FROM or JOIN) once in one query.
• Performance-wise, pull out Profiler for SQL:BatchCompleted and RPC:Completed, watch the Read, Write, CPU, and Duration columns, and see what a few runs of derived tables vs. #temp tables vs. indexed #temp tables does for each particular query.
• In general - if you're going to use it more than once, #temp table wins. If you're joining a lot of tables, #temp table probably wins. If you're joining only a few tables, derived table has a reasonable chance of winning. Benchmark it!
Temp tables are used to save table format data with temporary purpose, it occupy memory until we destroy it or after complete to execute the stored procedure.
Temp table occupy more memory as compared to table variable and CTE.
You may please visit below link to understand better in temptables Vs table variables Vs CTE.
Give respect to your work, Instead of trying to impress your boss.
Blog : http://naveens-dotnet.blogspot.in/
Local temporary tables are the tables stored in tempdb. 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. They are specified with the prefix #, for example #table_name and these temp tables can be created with the same name in multiple windows
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]
<a href="http://crackyourinterview.com/Ads-why-to-use-temporary-in-sql-or-advantage-of-temporary-table.aspx">Click here for above answer</a>