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?

Answer 1

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.

Answer 2

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.

Answer 3

When I started I always though views added performance overhead, however experience paints a different story (the view mechanism itself has negligible overhead).

It all depends on what the underlying query is. Check out indexed views here or here , ultimately you should test the performance both ways to obtain a clear performance profile

Answer 4

My biggest 'gripe' is that ORDER BY does not work in a view. While it makes sense, it is a case which can jump up and bite if not expected. Because of this I have had to switch away from using views to SPROCS (which have more than enough problems of their own) in a few cases where I could not specify an ORDER BY later. (I wish there was a construct with "FINAL VIEW" -- e.g. possibly include order by -- semantics).

http://blog.sqlauthority.com/2010/10/03/sql-server-the-limitations-of-the-views-eleven-and-more/ (Limitation #1 is about ORDER BY :-)