Use Derived Tables instead of Temp Tables in SQL Server

The biggest benefit of using derived tables over using temporary tables is that they require fewer steps, and everything
happens in memory instead of a combination of memory and disk.
The fewer the steps involved, along with less I/O, the faster the performance.

Here are the steps when you use a temporary table:

1) CREATE the temporary table (write activity)
2) SELECT data & INSERT data (read & write activity)
3) SELECT data from temporary table (read activity)
4) DROP TABLE (write activity)

Compare that to the number of steps it takes for a derived table:

1) SELECT data (read activity)

Derived tables are essentially SELECT statements within SELECT statements. Let's look at a very simple example:

Take a look at this simple query where we SELECT data from a table:

USE timeclock

GO

SELECT * FROM users
Inner join roles on roles_users_fk = users_pk
order by roles_role

Now, instead of selecting data from the categories table, let’s select our data from a derived table. For example:

USE timeclock

GO

SELECT distinct * FROM
(
SELECT * FROM users
Inner join roles on roles_users_fk = users_pk
order by roles_role
) dt_users
Order by lastname