T-Shirt Series: Derived tables

I have been asked what is the difference between Temp Tables, Table Variables, CTEs, and Derived tables.

So I thought I would take this opportunity to do a short blog post on each and then summarize the differences.

I like to think of a derived table as a temporary view. I typically use them when I want to aggregate a dataset before joining to it or I want to perform some filtering over a query I used windowing functions on.

Creation

To create a derived table you really just create a query within a query. Here are a couple common patterns.

Derived table in the FROM clause
SELECT DT.* FROM ( SELECT Column1, Column2 FROM dbo.Table WHERE Column3 = Filter ) as DT

Derived table in a JOIN
SELECT O.Customer, DT.OrderPrice
FROM dbo.Orders as O
JOIN ( SELECT
          OrderId,
          SUM(UnitQty * ( UnitCost + Markup)) as Price
       FROM dbo.OrderDetail
       GROUP BY OrderId ) as DT ON DT.OrderId = O.OrderId

Here is an example where I actually used 3 derived tables in order to perform an Anesthesia Type translation.

Assume you wanted to return the top 1 item for a bunch of records by using the windowed row number.

Let’s start if we wanted to do it for a single row. Let us assume you have a customer table and an orders table and you want to return the salesperson for highest order they have had

A single customer is easy.

SELECT TOP 1 Salesperson FROM dbo.Orders WHERE CustomerId = 4 ORDER BY OrderPrice DESC

But what if you want it for multiple or every customer? There are actually quite a few solutions, but let me give you two common ones.

You could do a correlated sub-select. This is not great for performance since the sub-select query is called for every row in returned in your statement. This is also known as RBAR (Row By Agonizing Row as coined by local SQL MVP Jeff Moden).

SELECT
    C.CustomerId,
    ( SELECT TOP 1 O.Salesperson FROM dbo.Orders O WHERE O.CustomerId = C.CustomerId ORDER BY OrderPrice DESC ) as Salesperson
FROM dbo.Customers C

Databases are meant to deal with sets of data, so let’s use a derived table to sequence our customer orders with a windowing function and then limit to just the top one.

SELECT
    C.CustomerId,
    O.SalesPerson
FROM dbo.Customers C
JOIN ( SELECT
         CustomerId,
         SalesPerson,
         ROW_NUMBER() OVER ( PARTITION BY CustomerId --start renumbering for each customer
                             ORDER BY OrderPrice DESC ) as CustomerOrderSequenceByPrice
       FROM dbo.Orders
     ) O ON O.CustomerId = C.CustomerId
WHERE O.CustomerOrderSequenceByPrice = 1 --we just want the top 1

Storage Location

Derived tables are not stored physically in tempDB. They are compiled in-line with the rest of the query and should be stored in memory, unless your server is memory starved.

Scope

A derived table is only available within the query it is written. If you need to reference the same derived query in multiple statements then you should consider a Temp Table, table variable, view, or function.

Transactions

Derived tables are not physical entities and as such are bound by the transaction of the statement it is part of.

Cardinality

Derived tables are optimized in-line with the rest of the query. Statistics on the tables referenced in the derived query may be used.

Indexes

Derived tables are optimized in-line with the rest of the query. Indexes on the tables referenced in the derived query may be used.

Logging

There is no logging as having directly used a derived query. However, if it is used as part of an Insert, Update, or Delete then the data modification is logged. Remember, a derived table is not itself a physical entity.

So if I was a derived table and was out looking for a t-shirt that described how special I was it would say something like …

Do you have any questions, or tips and tricks for using derived tables?

Let’s discuss in the comments below.

Recurring Theme