SQL Server Views and Simplification
Odds are you've worked with views before, but what is a view, really? Grant Fritchey gives us some technical details into what views really are and how they can be used effectively.
Join the DZone community and get the full member experience.Join For Free
I’ve been getting lots of questions on views lately. Must be something in the water.
Because SQL Server allows you to treat a view as if it was a table, lots of people assume that it is a table since they get to treat it that way. The thing is, a view is not a table. It’s a query. Let’s explore this just a little bit. Here’s a relatively straight-forward view:
CREATE VIEW dbo.PersonInfo AS SELECT a.AddressLine1, a.City, a.PostalCode, a.SpatialLocation, p.FirstName, p.LastName, be.BusinessEntityID, bea.AddressID, bea.AddressTypeID FROM Person.Address AS a JOIN Person.BusinessEntityAddress AS bea ON a.AddressID = bea.AddressID JOIN Person.BusinessEntity AS be ON bea.BusinessEntityID = be.BusinessEntityID JOIN Person.Person AS p ON be.BusinessEntityID = p.BusinessEntityID; GO
I can query this view like this:
SELECT * FROM dbo.PersonInfo AS pni WHERE pni.LastName LIKE 'Ran%';
The resulting execution plan looks like this:
You don’t even need to expand it for what I’m about to show. If we modify the query against our view as follows:
Again, you can expand these, but you don’t need to. Notice, the first plan had four tables being referenced, which represents the four tables from the view. The second query only has two tables. This is because the optimizer looked at the query that the view represents, not simply the query that I used to call the view. It then recognized that simplification could be used to eliminate unnecessary JOIN operations from the execution plan and still get the same data because of foreign key constraints on the tables.
The important point to note is that the optimizer is absolutely not treating the view like a table. The optimizer is treating the view like a query, which is all it is. This has both positive and negative impacts when it comes to query performance tuning and this view. You could spend all sorts of time “tuning” the view, only to find all that tuning you’ve done tossed out the window when the query doesn’t reference a column in the view and that causes the optimizer to rearrange the plan. I don’t want to convey that this is an issue. It’s not. I’m just trying to emphasize the point that a view is just a query.
Now, when we get into treating a view exactly like a table in JOINs or calling a view from a view (known as nesting), then we’re talking about issues. I’ll put up another post on a JOIN and views.
For lots more information on query tuning, I’m presenting an all day pre-con at SQL Day in Wroclaw Poland on May 16.
Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.