Temporary tables versus dervied tables

Most of the times programmers go for creation of temporary tables in T Sql Programming for performance gains. It is true that temporary tables provide performance gains but the question is whether it is better to use derived tables in place of temporary tables. Which one is better?


Well most of you by now should know how to create a temporary table creation and derived table creation.

Derived tables are essentially SELECT statements within SELECT statements

For Example:

Select * from
(
Select col1, col2 from table1
)
A


Creation of temporary table follows the following sequence:

1. Creation of table in tempdb
2. Select the data and insert into the table
3. Select the data from temporary table and parent table
4. Drop the temporary table

Creation of derived table uses the following steps

1. Select the data
2. Select the parent data with the derived table data.

Hence the number of steps required while creating a temporary table are more and its an over head to drop the temporary table and release the resources. But with derived table the overhead is less and rewriting the temporary tables replacing them with derived tables would also avoid recompiling the stored procedures.

Let’s see an example as to how to avoid temporary tables

There Employee and Department table. Employee table has the structure
EmployeeID,EmployeeName,DepartmentID

Department table has the structure
DepartmentID,Department Name

The objective of the query is to give a count of employees in each department along with the employee name. So the output should look something like


Employee Department Count
Emp1 Dep1 3
Emp2 Dep1 3
Emp3 Dep1 3
Emp4 Dep2 2
Emp5 Dep2 2


Create table #t1
(
DepartmentID ,Count
)

Insert into #t1 (select departmentid,count(*) from employee group by departmentid)


Select EmployeeName, department.departmentname,#t1.count
from employee join department on employee.departmentid = department.departmentid
join #t1 on department.departmentid = #t1.departmentid


drop table #t1



Using derived tables the query can be rewritten as

Select EmployeeName, department.departmentname,a.cn
from employee join department on employee.departmentid = department.departmentid
join
( select departmentid,count(*) as cn from employee group by departmentid) a
on department.departmentid = a.departmentid