Views in Microsoft SQL Server (Part 1 – “Joins hell”)

I don’t like views. I like layers of abstractions, dedicated tiers in the system but I don’t like views. It seems kind of strange – regular views are basically the abstraction on the metadata layer. Nothing less, nothing more. It gives you some benefits in terms of security, some benefits of the code abstraction. I just think that views introduce more problems than they solves.

Today I’d focus on one specific aspect of the views – I call it “joins hell”. Remember “dll hell” from the good old days? Maybe not too old, by the way.

Let’s create a couple tables and populate it with the data.

Let’s assume we want to have a view that joins both tables and returns client name in addition to orders column.

Simply enough. Natural enough especially if you come from imperative development world. You have the view as the abstraction and you can use it everywhere where you need to access orders. So let’s select the row including client name for one specific order.

Good. As you can see, it introduces 2 CI seek + join. You cannot do any better than that. Now, let’s say, you need to populate the list of the order numbers. So let’s run it again selecting OrderId and OrderNumber only (I’m still filtering out 1 single row but it hardly matters).

Oops – still join with the clients table even if you don’t need any columns from there. Well, if you think about that, it makes sense. If you use inner join in the view, SQL Server needs to make sure that every order has corresponding client and filter out orders without valid ClientId.

So first thing how to workaround it – use outer join instead.

As you can see, in this example, SQL Server does not care, if order does not have corresponding client. It seeks in Orders table only. On other hand, outer join limits the choices optimizer has. It cannot switch between inner and outer record sets when processes nested loops and so on. So technically this is not the best option.

Another method is to add foreign key constraint (don’t forget to add the index on Orders.ClientID column to avoid performance hit). If you run the original select you see, that SQL eliminates the join. By the way, if you decide to run demo scripts – make sure to use regular db. TempDb does not have joins elimination.


Looks like it solves our problems but unfortunately there are some cases when foreign key constraints cannot be created. And there are some cases when SQL Server cannot eliminate the joins even with constraints. Look here (click to open the plan on the different window):

So we don’t know if SQL will be able to eliminate the joins all the times even with all foreign keys in place. What we know for sure, that SQL does not do that if you have multi-column foreign keys. Let’s see that. First let’s create the same Orders and Clients tables with additional Int1 column.

Now let’s create the view and run the same select and see the plan.

As you can see, SQL still does the join. This is one of “design limitations” SQL Server has – no join elimination with multi-column foreign keys.

And if you think about that, this is very simple case. I saw some systems with the views that includes 20+ joins and at the end every select against them introduces a lot of extra IO you don’t need. So I always ask myself – are those minor benefits views give you worth the efforts?

Source code is available for download.

4 thoughts on “Views in Microsoft SQL Server (Part 1 – “Joins hell”)

  1. Joel Mamedov

    I do not understand your point. Too much technicality but, no clear context. What are you trying to solve?
    In your remark, you have indicated that you do not like views. Well, it would be professional if you list all the legitimate reasons.
    It all depends and what you are trying to solve and many way to solve it.
    In your case, I would for example create a view (not in dbo schema) one to one for each table.
    Joining views usually done for so-called semantic layer and it has its own merits.
    MS Sql by following Oracle’s steps provided (limited way) a concept of schema ownership. Schema concept coupled with view can be a powerful and flexible solution.

    Reply
    1. Dmitri Korotkevitch Post author

      Thank you, Joel, and sorry for the delay with my reply.

      Yes, you are absolutely right – I do not like views. 🙂 And you are right, it always in “it depends” category. There are legitimate cases when views are beneficial (Security, data access layer for self-service reporting/analysis, etc), however more often than not views are used as “code reuse” solution. Developers create views to simplify queries and reuse them when it makes sense or not.

      Problem I see here is potential performance overhead it introduces. I rarely see implementations you described as semantic layer – usually views are used as layer of abstraction for complex queries. And that leads to unnecessary I/O when join eliminations is not working. In this post I described two most common cases – inner joins without or with composite foreign keys. SQL performs joins even when you do not need data from some of the tables.

      And if we bring ORMs to the picture, situation would be even worse.

      Again, I am not arguing that views could be the great and powerful tool. When they used correctly..

      Reply
  2. pradeep gp

    Hi,
    My question is “Why we use views inside the stored procedure only”? we can write views seperately right.
    So, give me the advantage of using views inside the stored procedure.

    Reply
    1. Dmitri Korotkevitch Post author

      Hi Pradeep,

      I have never stated that views should be used only inside the stored procedures. As the opposite, views are often used as “in-database data access tier” as the replacement of the stored procedures. I would personally argue against it due to the various issues, such as one explained in this blog post.

      Views could be the good “security layer” but it is the different topic.

      Sincerely,
      Dmitri

      Reply

Leave a Reply to Dmitri Korotkevitch Cancel reply

Your email address will not be published. Required fields are marked *