Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQL Server Views and Simplification

DZone's Guide to

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.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

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:

Image title

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:

Image title

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.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
ms sql ,views ,join ,plan ,details

Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}