What are the downsides of using SqlServer Views?

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 proc开发者_StackOverflowessing?

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.

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.

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.

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.

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).

A downside to views that I've run into is a dive in performance when incorporating them into distributed queries. This SQLMag article discusses - and whilst I use highly artificial data in the demo, I've run into this problem time and time again in the "real world".

Respect your views, and they'll treat you well.

What are Various Limitations of the Views in SQL Server?

Top 11 Limitations of Views

• Views do not support COUNT (); however, it can support COUNT_BIG ()
• ORDER BY clause does not work in View
• Regular queries or Stored Procedures give us flexibility when we need another column; we can add a column to regular queries right away. If we want to do the same with Views, then we will have to modify them first
• Index created on view not used often
• Once the view is created and if the basic table has any column added or removed, it is not usually reflected in the view till it is refreshed
• UNION Operation is not allowed in Indexed View
• We cannot create an Index on a nested View situation means we cannot create index on a view which is built from another view.
• SELF JOIN Not Allowed in Indexed View
• Outer Join Not Allowed in Indexed Views
• Cross Database Queries Not Allowed in Indexed View

Source SQL MVP Pinal Dave

http://blog.sqlauthority.com/2010/10/03/sql-server-the-limitations-of-the-views-eleven-and-more/

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

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 :-)

The following is a SQL hack that allows an order by to be referenced in a view:

create view toto1 as 
select top 99.9999 percent F1
from Db1.dbo.T1 as a 
order by 1

But my preference is to use Row_Number:

create view toto2 as 
select *,  ROW_NUMBER() over (order by [F1]) as RowN from ( 
select f1
from Db1.dbo.T1) as a