In my experience, there are few database objects more poorly understood, misunderstood, misused, and outright abused, than the humble view. Depending on who you’re talking to, they may be the source of all evil, something to be tolerated but not encouraged, or the greatest thing since the invention of the wheel.

What Is A View?

Ask 10 developers this question, you’ll likely get 10 different answers. “It’s like a table, but it’s not a table” is pretty close, except for materialized views, which are more like tables than not (more on that later).

A view at the abstract level is what its name implies: an aspect or perspective. Views reveal their underlying data without revealing their sources. Column names and data types may be different, rows may be filtered, data aggregated, all transparently to the user.

At the functional level, a view is simply a query, much like a subquery or a CTE. They can participate in joins, and views can be built on other views, just as CTE’s or subqueries can be nested (but more on that shortly). But views have superpowers in comparison: they can (under the right circumstances) be inserted, updated, and deleted just like tables, and when materialized, they can have clustered and nonclustered indexes.

The Good

Views have a good and rightful place in the architecture of a database. They are useful for providing access to data without allowing direct access to the underlying tables. They can encapsulate often-used query patterns, disguise extended joins, ease the pain of aggregations. They can unify vertically or horizontally partitioned tables. They can translate codes and bit flags into meaningful descriptions. They can limit what columns and rows are visible to the user. They can provide an alternative to stored procedures for frameworks and front ends. In short, in the right hands, views can help simplify security, hide complexity, and enhance performance.

The Bad

Views can – and often are – used to try and compensate for poor design or improper indexing. As Rocky The Flying Squirrel was wont to say, “That trick never works.” You can’t fix ugly. Materialized, indexed views can help, but they come with their own issues. If queries perform poorly, views will, too. Any view with more than a half-dozen joins is simply asking for trouble, and usually gets it. Worse still, compensatory views lead to the cardinal sin of view-building: nested views.

The Ugly

Few things give DBA’s the twitches like nested views. Trying to unravel them is time-consuming, frustrating, and enervating: one view just leads to another, then another, then another. If the performance of one view is poor, that escalates exponentially, and very soon it takes several minutes to retrieve data that ought to come across in seconds.

That’s because nested views can confuse and ultimately cripple the query optimizer. When simple views are provided with parameters, the optimizer can apply those to the view just as it would any query. Nesting that’s one level deep can usually be accommodated, but beyond that, things get hairy, and eventually the optimizer just mutters, “To hell with it”, and returns every row the view touches, relying on hash joins and bitmapping to try and filter out some of the noise. The more joins in the underlying views, the more likely the optimizer will simply run out of time trying to find a decent plan, and return something that resembles a flowchart that’s been through a blender.

Living In The Material World

Materialized views, also known as schema-bound views, take the table analogy to its logical conclusion, persisting the rows of the view, and allowing clustered and non-clustered indexes to be built on the view. In fact, under the right conditions, the optimizer may choose to use a materialized view as if it were another index. These views can help provide workarounds to improperly clustered tables, or compensate for missing indexes.

Unlike indexes and other views, there are strict rules governing the creation of materialized views. They cannot be built on other views (no nesting allowed); they can only use INNER joins; aggregates must be over non-nullable columns, or must utilize ISNULL; and certain aggregations require the inclusion of a COUNT_BIG column.

Materialized views have other shortcomings, ones they share with indexes. They come at a cost to INSERT, UPDATE, and DELETE activities. Worse, a single materialized view may impact the performance of IUD statements for multiple tables: if a materialized view is composed of, say, three joined tables, then any DML statements that affect the underlying table must affect the view as well – and, just as with indexes, updates to the view will be done serially, not in parallel. Materialized views take up space, as well, and will grow in concert with their sources.

The Panoramic View

Some DBA’s take the “Not In My Database” approach to views, and ban them outright, on the grounds that they are too often poorly understood and poorly thought out, and that developers seem to think of them as “kind of like functions, only more better”. That’s a pretty draconian approach, though, and likely does as much harm as good.

I’ve also seen the “Wild, Wild West” approach, where there are more views than tables, and selecting from views joined to views built on views joined to other views becomes more common than addressing the original data sources directly. This will kill performance in a heartbeat, not to mention causing great mental anguish to anyone unfortunate enough to have to maintain such code in the future.

Views should be approached with caution, and an eye to maintenance and performance, but with the knowledge that the right view in the right place can be a boon to developer and DBA alike. So: what’s the right time and the right place? Like everything else in SQL Server, “It depends.” Experiment, step outside the box from time to time, never say “No” just for the sake of saying “No” (but be willing to say “No” if you have to), and views will reward you with time saved, complexity avoided, and performance boosted.