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