Temporary Tables in SQL Server 2008
In this article I will demonstrate how to use Temporary Tables in SQL.
- Temporary tables are tables that exists
only to the session that created them. When that session time is completed,
table is automatically destroyed at the
termination of the session.
- Using temporary tables improves the
codes performance and it is more developer friendly.
- Temporary tables are created under
the tempdb database.
- Temporary tables are created at runtime.
- Temporary tables are exactly like general
tables except the temporary table name starts with a '#' pound sign while
creating. It provides same functionality as general table provides.
Types of Temporary Tables
- Local Temporary Table (represented by #).
- Global Temporary Table (represented by
- Table Variable.
Use of Temporary Tables
- Create clustered and non-clustered
- You can use identity columns.
- Perform all DML operations.
- Reduce the number of rows for join.
Removing the largest salary from the table
using a Temporary Table, DISTINCT and TOP keyword
Suppose we have table named Employee having
columns (ID, F_Name, L_Name, Salary, Region, City) as shown in image.
Finding second largest salary from above table.