What are the downsides of using SqlServer Views?

Question

What are the downsides of using SqlServer Views?

I create views frequently to show my data in a denormalized form.

I find it much easier and therefore faster, less error prone, and more self documenting, to query one of these joins rather than to generate complex queries with complicated joins between many tables. Especially when I am analyzing the same data (many same fields, same table joins) from different angles.

But is there a cost to creating and using these views?

Am I slowing down (or speeding up?) query processing?

Solution 1

When comes to Views there are advantages and disadvantages.

Advantages:

1. They are virtual tables and not stored in the database as a distinct object. All that is stored is the SELECT statement.
2. It can be used as a security measure by restricting what the user can see.
3. It can make commonly used complex queries easier to read by encapsulating them into a view. This is a double edged sword though - see disadvantages #3.

Disadvantages:

1. It does not have an optimized execution plan cached so it will not be as fast as a stored procedure.
2. Since it is basically just an abstraction of a SELECT it is marginally slower than doing a pure SELECT.
3. It can hide complexity and lead to gotchas. (Gotcha: ORDER BY not honored).

My personal opinion is to not use Views but to instead use stored procedures as they provide the security and encapsulation of Views but also come with improved performance.

Solution 2

One possible downside of using views is that you abstract the complexity of the underlying design which can lead to abuse by junior developers and report creators.

For a particularly large and complex project I designed a set of views which were to be used mostly by report designers to populate crystal reports. I found out weeks later that junior devs had started using these views to fetch aggregates and join these already large views simply because they were there and were easy to consume. (There was a strong element of EAV design in the database.) I found out about this after junior devs started asking why seemingly simple reports were taking many minutes to execute.

Solution 3

The efficiency of a view depends in large part on the underlying tables. The view really is a just an organized an consistent way to look at query results. If the query used to form the view is good, and uses proper indexes on the underlying tables, then the view shouldn't negatively impact performance.

In SQL Server you can also create materialized or indexed views (since SQL Server 2000), which increase speed somewhat.

Solution 4

I use views regularly as well. One thing to note, however, is that using lots of views could be hard to maintain if your underlying tables change frequently (especially during development).

EDIT: Having said that, I find the convenience and advantage of being able to simplify and re-use complex queries outweighs the maintenance issue, especially if the views are used responsibly.

Solution 5

Views can be a detriment to performance when the view contains logic, columns, rows, or tables that aren't ultimately used by your final query. I can't tell you how many times I've seen stuff like:

SELECT ... 
FROM (View with complex UNION of ActiveCustomer and InactiveCustomer tables)
WHERE Active = True 

(thus filtering out all rows that were included in the view from the InactiveCustomer table), or

SELECT (one column)
FROM (view that returns 50 columns)

(SQL has to retrieve lots of data that is then discarded at a later step. Its possible those other columns are expensive to retrieve, like through a bookmark lookup), or

SELECT ...
FROM (view with complex filters)
WHERE (entirely different filters)

(its likely that SQL could have used a more appropriate index if the tables were queried directly), or

SELECT (only fields from a single table)
FROM (view that contains crazy complex joins)

(lots of CPU overhead through the join, and unnecessary IO for the table reads that are later discarded), or my favorite:

SELECT ...
FROM (Crazy UNION of 12 tables each containing a month of data)
WHERE OrderDate = @OrderDate

(Reads 12 tables when it only really needs to read 1).

In most cases, SQL is smart enough to "see through the covers" and come up with an effective query plan anyway. But in other cases (especially very complex ones), it can't. In each of the above situations, the answer was to remove the view and query the underlying tables instead.

At the very least (even if you think SQL would be smart enough to optimize it anyway), eliminating the view can sometimes make your own query debugging and optimization easier (a bit more obvious what needs to be done).

View more solutions

Comments

[Paul Sasik]

+1 In my experience there is an incredible amount of ignorance and misinformation about db views. There may be a lot of discussion about what views do and how but i'm not sure if they're framed like this and if the answers are obvious.

[JNK]

@Mr Shoubs - I think people ask questions here, even if the answer is easily googled, because they want the interactivity and followup/Q&A that SO provides, and I don't think we should discourage that.

[Mr Shoubs]

Fair enough, I've re-answered

[Lill Lansey]

@Mr Shoubs. Maybe a lot of info, but also maybe too much info. I don't want to spend 3 days wading thru hundreds of pages of doc. I just want a simple answer.

[Mr Shoubs]

@Lill, unfortuantly, I don't think there is one simple answer :(

[Paul Sasik]

There is a simple answer: You're on the right track. Views are nicely performant and a great abstraction tool. Just take care of the management aspect and watch for abuse.

[Mr Shoubs]

@Paul, agreed - They are easily abused. Test & Compare.

[Mr Shoubs]

They are especially good for reuse

[hyprsleepy]

@Lill: If you are returning results to an application instead of making a View called customers it would be better to have a stored procedure that does the same called GetCustomers(). If the view is mostly for your use you can save scripts and keep commonly used selects in that file that can be re-run if you don't want to have to write them from scratch everytime.

[Mr Shoubs]

ok, ok, talk about being jumped on - I sound a lot worse then intended

[GrowlingDog]

I think the most danger comes from Disadvantage #3. The hidden complexity is potentially dangerous. Often times, the purpose of using views is to "simplify", but it can end up creating more problems in the long run.

[JNK]

As always, the unattributed and uncommented downvotes are appreciated :P

[Lill Lansey]

Not sure what the problem is with: SELECT ... FROM (view with crazy UNION of Active and Inactive Customers) WHERE Active = True . Are you saying there should be an additional two views, one with all active customers, and another with inactive. So if you only need active you query the 'active' view, etc?

[Lill Lansey]

@Mr Shoubs. Maybe a lot of info, but also maybe too much info. I don't want to spend 3 days wading thru hundreds of pages of doc. I just want a simple answer. I guess I could have asked for one good link that gives me an answer to the question. And then rely on the ones with the most votes.

[topski]

@Lill Well, in this (contrived) example, the view combines data from tables that are then filtered out by the final query. So just query the ActiveCustomer table directly, and bypass the view entirely.

[Toby Allen]

could you explain the ORDER BY not Honoured Gotcha and how it manifests itself. I have a question about it if you wish to answer. stackoverflow.com/questions/5901558/…

[daiscog]

Re SELECT (one col) FROM (view with 50 cols) - looking at the execution plan on SQL Server 2008, this is actually optimised away. And the same for SELECT (fields from single table) FROM (view with crazy complex joins) - execution plan removes joins which are not needed for the query.