Using Virtual Tables to Simplify SQL-based Data Analysis Without Views

By May 10, 2011Tips

Views are wonderful things in SQL Server. They let you simplify your data structures and can enhance performance significantly  In previous articles I covered how to create analytical queries and save them as views. As great as views are, there are often situations where changing the database is not an option. There may be a process involved before changes may be applied or databases may be in many different locations. In some cases, there may be hundreds of individual databases that are too difficult to update.

Regardless of the reason, it is not always easy to update the database to create views. Let’s say we have a sophisticated query that’s needed as a baseline for various ad hoc reports.  Rather than creating a view, we can inject some SQL before our queries to simulate a virtual table. Just like a view, this will simplify and enhance the data schema.  Here’s an example.

WITH VirtualDataSource AS
(SELECT *,
(SELECT COUNT(OrderID) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
) As Orders,
DATEDIFF(mm,
(SELECT Max(OrderDate) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID)
,getdate()
) AS MonthsSinceLastOrder
FROM Customers) 

SELECT * FROM VirtualDataSource

This query sets up some special fields that determine how many orders a customer has placed and how many months since their last order.  We used a WITH statement to simulate having a view.  The nice thing about this approach is that you can simply prefix some text to the query.  Now let’s create a more useful query that tells us something about the top countries that we sell to.

WITH VirtualDataSource AS
(SELECT *,
(SELECT COUNT(OrderID) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
) As Orders,
DATEDIFF(mm,
(SELECT Max(OrderDate) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID)
,getdate()
) AS MonthsSinceLastOrder
FROM Customers) 
SELECT TOP 5 Country, SUM(Orders)
FROM VirtualDataSource
GROUP BY Country
ORDER BY SUM(Orders) DESC

Looks like the US and Germany are tied for the top. We can not easily alter our query to do a further analysis. Again, we simply put our specific query at the bottom of the bottom which greatly simplifies things. The alternative query without this method would be far less modular and much more complex.