Temporary Tables in SQL Server 2008

In this article I will demonstrate how to use Temporary Tables in SQL.
  • 1678

Introduction

  • 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

  1. Local Temporary Table (represented by #).
  2. Global Temporary Table (represented by ##).
  3. Table Variable.

Use of Temporary Tables

  1. Create clustered and non-clustered indexes.
  2. You can use identity columns.
  3. Perform all DML operations.
  4. 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.

Table-in-sql.jpg

Finding second largest salary from above table.

Finding_second_largest_salary.jpg


© 2013 dotNetheaven. All rights reserved.