Geeks With Blogs
Gaurav Taneja Great dreams... never even get out of the box. It takes an uncommon amount of guts to put your dreams on the line, to hold them up and say, "How good or how bad am I?" That's where courage comes in.

Using Derived Tables to Simplify the SQL Server Query Process

Problem
Sometimes querying data is not that simple and there may be the need to create temporary tables or views to predefine how the data should look prior to its final output.  Unfortunately there are problems with both of these approaches if you are trying to query data on the fly. 

With the temporary tables approach you need to have multiple steps in your process, first to create the temporary table, then to populate the temporary table, then to select data from the temporary table and lastly cleanup of the temporary table.

With the view approach you need to predefine how this data will look, create the view and then use the view in your query.  Granted if this is something that you would be doing over and over again this might make sense to just create a view, but let's look at a totally different approach.

Solution
With SQL Server you have the ability to create derived tables on the fly and then use these derived tables within your query.  In concept this is similar to creating a temporary table and then using the temporary table in your query, but the approach is much simpler, because it can all be done in one step.

Let's take a look at an example where we query the Northwind database to try to find out how many customers fall into various categories based on sales.  The categories that we have predefined are as follows:

  • Total Sales between 0 and 5,000 = Micro
  • Total Sales between 5,001 and 10,000 = Small
  • Total Sales between 10,001 and 15,000 = Medium
  • Total Sales between 15,001 and 20,000 = Large
  • Total Sales > 20,000 = Very Large

There are several ways that this data can be pulled, but let's look at an approach using a derived table.

The first step is to find out the total sales by each customer, which can be done with the following statement.

SELECT   o.CustomerID,
         
SUM(UnitPrice QuantityAS TotalSales
FROM     [Order Details] AS od
         
INNER JOIN Orders AS o
           
ON od.OrderID o.OrderID
GROUP BY o.CustomerID

This is a partial list of the output:

CustomerID

TotalSales

CUST1 

4596.2000

CUST2

1402.9500

CUST3

7515.3500

...

 

CUSTN

3531.9500

The next step is to classify the TotalSales value into the OrderGroups that were specified above:

SELECT   o.CustomerID,
         
SUM(UnitPrice QuantityAS TotalSales,
         
CASE 
           
WHEN SUM(UnitPrice Quantity
               BETWEEN 
AND 5000 THEN 'Micro'
           
WHEN SUM(UnitPrice Quantity
               BETWEEN 
5001 AND 10000 THEN 'Small'
           
WHEN SUM(UnitPrice Quantity
               BETWEEN 
10001 AND 15000 THEN 'Medium'
           
WHEN SUM(UnitPrice Quantity
               BETWEEN 
15001 AND 20000 THEN 'Large'
           
WHEN SUM(UnitPrice Quantity
               > 
20000 THEN 'Very Large'
         
END AS OrderGroup
FROM     [Order Details] AS od
         
INNER JOIN Orders AS 
          
ON od.OrderID o.OrderID
GROUP BY o.CustomerID

This is a partial list of the output:

CustomerID

TotalSales

OrderGroup

CUST1 

4596.2000

Micro

CUST2

1402.9500

Micro

CUST3

7515.3500

Small

...

 

 

CUSTN

3531.9500

Micro

The next step is to figure out how many customers fit into each of these groups and this is where the derived table comes into play.  Take a look at the following query which uses a derived table called OG.  What we are doing here is using the same query from the step above, but calling this derived table OG. Then we are selecting data from this derived table for our final output just like we would with any other query.  All of the columns that are created in the derived table are now available for our final query.

SELECT   OG.OrderGroup,
         
COUNT(OG.OrderGroupAS OrderGroupCount
FROM     (SELECT   o.CustomerID,
                   
SUM(UnitPrice QuantityAS TotalSales,
                   
CASE 
                     
WHEN SUM(UnitPrice Quantity
                       BETWEEN 
AND 5000 THEN 'Micro'
                     
WHEN SUM(UnitPrice Quantity
                       BETWEEN 
5001 AND 10000 THEN 'Small'
                     
WHEN SUM(UnitPrice Quantity
                       BETWEEN 
10001 AND 15000 THEN 'Medium'
                     
WHEN SUM(UnitPrice Quantity
                       BETWEEN 
15001 AND 20000 THEN 'Large'
                     
WHEN SUM(UnitPrice Quantity
                       > 
20000 THEN 'Very Large'
                   
END AS OrderGroup
          
FROM     [Order Details] AS od
                   
INNER JOIN Orders AS o
                     
ON od.OrderID o.OrderID
          
GROUP BY o.CustomerIDAS OG
GROUP BY OG.OrderGroup

This is the complete list of the output from the above query.

OrderGroup

OrderGroupCount

Large

10

Medium

11

Micro

33

Small

15

Very Large

20

 

Next Steps

  • Next time you run into a challenge of whether to create a temporary table or a view to produce the desired query take a look at using a derived table instead
  • Experiment with using derived tables, views and temporary tables to see what yields better performance results. There are several articles on the internet that have shown that using a derived table is faster then temporary tables, but try it for yourself.

 

Posted on Monday, January 10, 2011 7:15 PM SQL SERVER | Back to top


Comments on this post: Using Derived Tables

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Gaurav Taneja | Powered by: GeeksWithBlogs.net