Using Views in SQL

Question

Hi, I have a view that is created from a base table.This view is basically the exact copy of table without any filter conditons and it has all the columns and records of the table. Is there any advantage in using view (which is a direct copy of table) instead of the table direclty in my application or stored procedures.

Answer 1

in this case there is no difference between view and table. view is not a copy of table but something like stored select statement.

Answer 2

There may be serious disadvantages to this approach, from a performance aspect.

http://www.sql-server-performance.com/tips/views_general_p1.aspx

I would suggest avoiding the use of views if possible.

Answer 3

Nope.

With the exception of indexed views, views are only really useful for making the SQL cleaner to read - executing a query containing a view is essentially the same as executing a query with the view definition copied and pasted into the query.

I would discourage the use of complex views in this way as although it makes the query look cleaner, it makes the diagnosis process more of a pain (as you need to look up all of the views to understand what the original query is doing)

Answer 4

But you need to be aware that the way views work is a bit different, as you end up having some overkill on the DB side.

The way a view works, is by doing a Select *, and then filtering itself for the columns you are adding to it.

I'd be really weary to use views for this, unless there were some serious security concerns.

The way to go is to create a Stored Procedure that grabs the data directly from the table. That way, you can take the maximum of index and all that.

Cheers

Answer 5

There may not be an immediate purpose or benefit, but it does provide you with a point of abstraction which can provide an architectural or security based benefit at a later point in time.

The view could be in essence considered a data contract, which in the future allows the underlying structure to flex up to a point, without the outside world realising it.

On the security side, at a point in the future a where clause might be inserted, which starts to give you row level filtering / security, where as direct access to the table prevents any such future move.

Answer 6

One advantage (or disadvantage, depending on your viewpoint) is that Views allow you to store business logic within the SQL server, instead of within your code. If you need to change the business easily without recompiling your code, modifying the view is a quick and easy way of doing so.

I personally prefer having the business logic of the application defined within the code however :)

Answer 7

Views can be thought of as a logical layer atop the physical layer (table) In your case at the moment that layer is so thin it's value can be questioned. However over time, such may not be the case. Using a view to access the table, costs you virtually nothing, yet insulates your code from possible changes to the physical model.