Fundamentals of table expressions, Part 9 – Views, compared with derived tables and CTEs

I pointed out that to me this feels like much clearer code due to the lack of nesting. You can see each step in the solution from start to end separately in its own unit, with the solution’s logic flowing clearly from top to bottom. Therefore, I see the CTE option as an improvement over derived tables in this respect.

Now to views. Remember, one of the main benefits of views is reusability. You can also control access permissions. The development of the units involved is a bit more similar to CTEs in the sense that you can focus your attention on one unit at a time from start to end. Moreover, you have the flexibility to decide whether to create a separate view per unit in the solution, or, perhaps just one view based on a query involving statement-scoped named table expressions.

You would go with the former when each of the units needs to be reusable. Here’s the code you would use in such a case, creating three views:

-- Sales.OrderYears
CREATE OR ALTER VIEW Sales.OrderYears
AS 
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders;
GO

-- Sales.YearlyCustCounts
CREATE OR ALTER VIEW Sales.YearlyCustCounts
AS
  SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
  FROM Sales.OrderYears
  GROUP BY orderyear;
GO

-- Sales.YearlyCustCountsMin70
CREATE OR ALTER VIEW Sales.YearlyCustCountsAbove70
AS
  SELECT orderyear, numcusts
  FROM Sales.YearlyCustCounts
  WHERE numcusts > 70;
GO

You can query each of the views independently, but here’s the code you would use to return what the original task was after.

SELECT orderyear, numcusts
FROM Sales.YearlyCustCountsAbove70;

If there’s a reusability requirement only for the outermost part (what the original task required), there’s no real need to develop three different views. You could create one view based on a query involving CTEs or derived tables. Here’s how you would do that with a query involving CTEs:

CREATE OR ALTER VIEW Sales.YearlyCustCountsAbove70
AS
  WITH C1 AS
  (
    SELECT YEAR(orderdate) AS orderyear, custid
    FROM Sales.Orders
  ),
  C2 AS
  (
    SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
    FROM C1
    GROUP BY orderyear
  )
  SELECT orderyear, numcusts
  FROM C2
  WHERE numcusts > 70;
GO

By the way, if it wasn’t obvious, the CTEs that the view’s inner query is based on can be recursive.

Let’s proceed to cases where you need multiple references to the same table expression from the outer query. The task for this example is to compute the yearly order count per year, and compare the count in each year with the preceding year. The easiest way to achieve this is actually to use the LAG window function, but we’ll use a join between two instances of a table expression representing yearly order counts just to compare a multi-reference case among the three tools.

This is the code that we used earlier in the series to handle the task with derived tables:

SELECT CUR.orderyear, CUR.numorders,
  CUR.numorders - PRV.numorders AS diff
FROM ( SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
       FROM Sales.Orders
       GROUP BY YEAR(orderdate) ) AS CUR
  LEFT OUTER JOIN
     ( SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
       FROM Sales.Orders
       GROUP BY YEAR(orderdate) ) AS PRV
    ON CUR.orderyear = PRV.orderyear + 1;

There’s a very clear downside here. You have to repeat the definition of the table expression twice. You are essentially defining two named table expressions based on the same query code.

Here’s the code that handles the same task using CTEs:

WITH OrdCount AS
(
  SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
  FROM Sales.Orders
  GROUP BY YEAR(orderdate)
)
SELECT CUR.orderyear, CUR.numorders,
  CUR.numorders - PRV.numorders AS diff
FROM OrdCount AS CUR
  LEFT OUTER JOIN OrdCount AS PRV
    ON CUR.orderyear = PRV.orderyear + 1;

There’s a clear advantage here; you define only one named table expression based on a single instance of the inner query, and refer to it twice from the outer query.

Views are more similar to CTEs in this sense. You define only one view based on only one copy of the query, like so:

CREATE OR ALTER VIEW Sales.YearlyOrderCounts
AS
  SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
  FROM Sales.Orders
  GROUP BY YEAR(orderdate);
GO

But better than with CTEs, you’re not limited to reusing the named table expression only in the outer statement. You can reuse the view name any number of times that you like, with any number of unrelated queries, so long as you have the right permissions. Here’s the code to achieve the task by using multiple references to the view:

SELECT CUR.orderyear, CUR.numorders,
  CUR.numorders - PRV.numorders AS diff
FROM Sales.YearlyOrderCounts AS CUR
  LEFT OUTER JOIN Sales.YearlyOrderCounts AS PRV
    ON CUR.orderyear = PRV.orderyear + 1;

It seems like views are more similar to CTEs than to derived tables, with the extra functionality of being a more reusable tool, with the ability to control permissions. Or to turn it around, it’s probably appropriate to think of a CTE as a statement-scoped view. Now what could be really wonderful is if we also had a named table expression with a wider scope than that of a CTE, narrower than that of a view. For instance, wouldn’t it have been great if we had a session-level scoped named table expression?

Summary

I love this topic. There’s so much in table expressions that is rooted in relational theory, which in turn is rooted in mathematics. I love knowing what the right terms for things are, and generally making sure that I have the foundations figured out carefully, even if to some it might seem like being picky and over pedantic. Looking back at my learning process over the years, I can see a very clear path between insisting on a good grasp of foundations, using correct terminology, and really knowing your stuff later when it gets to the far more advanced and complex stuff.

So, what are the critical pieces when it comes to views?

• A view is a table.
• It is a table that is derived from a query (a table expression).
• It is given a name that to the user appears like a table name, since it is a table name.
• It is created as a permanent object in the database.
• You can control access permissions against the view.

Views are similar to CTEs in a number of ways. In the sense that you develop your solutions in a modular way, focusing on one unit at a time from start to end. Also in the sense that you can have multiple references to the view name from the outer query. But better than CTEs, views are not limited only to the outer statement’s scope, rather are reusable until dropped from the database.

There’s a lot more to say about views, and I’ll continue the discussion next month. In the meanwhile, I want to leave you with a thought. With derived tables and CTEs you could make a case in favor of SELECT * in an inner query. See the case I made for it in Part 3 in the series for details. Could you make a similar case with views, or is it a bad idea with those?