[RESOLVED] Sql views

[motil]

Hello all.

I really have problem understanding SQL VIEWS, i keep reading this view explanations articles but i'm can't REALLY understand when and why to use them. this is what i understand so far

A) VIEW is virtual table to instead storing data it stores the query of one or more table
- OK, no problem.

B) VIEWS only display to the end user columns or rows we would like him/she to see.

- but I do it with regular query by selecting only the desire columns / rows
where is the different ?

can someone please explain it very slow and simple ??

Thanks!

[motil]

Ok, I finally got the idea, just tell me if i'm on the right truck or there's something else i should be aware of:

basically you'll use VIEWS for re-use complex queries such as

SELECT A.id,A.order_ID,B.first_name,B.last_name FROM orders A 
INNER JOIN employees B ON (A.employee_id = B.employee_id)

once i create view for this query i can treat it as a single query, correct ?

[si_the_geek]

Yes, that is one of the benefits.

Another is that because it is stored inside the database system, it can be used by any program/person that works with the database, rather than just from one specific program.

On top of that, many database systems (such as SQL Server and Oracle etc) will actually optimise the efficiency of views a bit, because they are expected to run repeatedly - whereas any queries sent from your program will be assumed to be a one off, and will therefore not get the same treatment.

[motil]

Thanks for the answer si_the_geek

the fact that it stored inside the db not change much for me cause all my queries is already in the form of SPs, but it good to know that the query been optimize do you know by any chance in what way it been optimize ? is the server changing the query syntax? (i guess not) adding indexes maybe? adding statistics ?


one more question: now that i understand the benefits of VIEWS (and it really has) should i start thinking different and use it a lot? is it common to use VIEWS very frequently ?

Thanks again.

[si_the_geek]

The optimisation depends on the database system, but the query itself stays the same. What generally changes is just the execution plan, which may be based on statistics etc from previous runs (and thus improve over time, rather than just after the first run). I don't think indexes would be changed automatically, as that could damage the speed elsewhere - and the database wont know your system well enough to know if it is a good idea.

I like to create a few views (perhaps 5 for a database with about 30 tables), as it makes manual database checks etc much easier. If you have SP's that do the same job tho, you wont get much benefit in that respect.

[motil]

Yea when i just thought about it, SPs almost filling the job of views in most cases.

Thank you very much for the info, was very helpful!

[techgnome]

consider this: I have a database that holds information about all of the tracks (songs) I've imported into a particular system. For simplicity and speed the developers have a single table songlist that has everything. It is extremely denormalized... when I need to get a list of artists, I have to either do a distinct or group by to get the list down... rather than do that each time, I could create a view that does it for me... then I can use that normalized view to join back to another view that gives me all of the albums... the end result being a list of all artists and their albums... that way I don't have to do all that grouping and distinct each time - it's handled by the view.

-tg

[techgnome]

And for me, the views are more importaint in this case since sprocs aren't an option for me.

-tg

[GaryMazzone]

Just to cloudy the waters a little more. There is in Oracle a Materialized View and in SQL Server an Indexed View. These are actuall phyisical structures that are stored in the database and can be used to help speed up the application.

[CVMichael]

I hate views !!!

To me views are a way to mess up a database that is already messed up.

Databases created by me don't have any views, and when working on a database made by someone else, I have a headache tracking down where the data is coming from, because of views using other views and so on, and each one renaming fields (using "AS" keyword).

[motil]

Hi all

Thank you very much for the input!
From now on I'll slowly start use views until I'll fully understand it and its benefits

@CVMichael - I see what you mean luckily for me i'm the owner of the db at work and i almost the only one who touch it so it won't be a problem (for now)

Thanks again.

[techgnome]

that's why my views are prefixed with "vw_" then I know exactly where it's coming from. Personally, I have the opposite opinion of views... it's a great way to cleanup a messed up database... the example I gave above is not contrived in any means... it was a real example... I use it to "normalize" the database to some extent. Plus they can be used to obscure security.. revoke privileges on the table, but grant select to the view...

-tg

[techgnome]

DISCLAIMER - I should note, how ever, I have recently taken control of the database and started denormalizing some of the data when I can, eliminating the need for the original views I had created. However, I still think views are valid and relevant and have their uses... quite often in many cases though, the use of a select sproc is used instead since we're passing around parameters and such.

-tg

[leinad31]

This was your earlier thread http://www.vbforums.com/showthread.p...62#post3579062

Your difficulty with assessing value of view comes from having a bottom-up approach to development. Change your POV to top-down and you will see the benefits.

In an enterprise system, tracking down all queries for rewrite (e.g. modify where clause) is not feasible, error/omission prone, and a documentation+testing/QA headache. You are better off rewriting view that supplies relevant recordset to various applications/systems/web services.

[motil]

I think you've taking it too far leinad31, the thread that you linked in your post was my first attempt to understand view's and when i saw i still have trouble to understand it i just left it until yesterday.
I don't think there's nothing wrong with my point of view since I know and learn by myself much more complicated subjects.
I think the source of my problem to understand view's was number of things:

1) view's is in some degree similar to SP, just SP gives you more as long as your DB and SPs are organized and well built which this is my case.
2) DB is not my main occupation (I do work with DB alot but I never buy book or went to learn DB/SQL, I prefer investing my time learning programing and programing concepts)
3) I just didn't try to hard

the bottom line: I think I will use views in the future but not as much as i use SPs that's for sure.
regards!

[leinad31]

View is an abstraction mechanism for returning data sets.

SP is for ensuring atomicity of transactions but can return data sets, because some data sets requires transactional processing, or procedural logic before they can be returned and that isn't supported by SQL.

Compared to other database systems, MicroSoft makes it easy/straightforward to return data sets using SP to the point that it is abused and misused to return data sets even when there is no transaction processing involved (a simple query within an SP).

View design is more portable and can easily be used elsewhere (just another SQL, minimal rewrite if any). Not all SPs can be ported in a straightforward manner. Also, there is overhead in switching context from SQL to procedural language and vice versa, more database resources are used. Also, since there is no persistence layer such as Hibernate in MS strategy (please correct me if I am wrong) then MS developers will not realize up front that using views with mid-tier is easier especially if you are going to leverage mid-tier query technology. There's also the ER diagramming, table to object mapping (common in web based projects), and related tools that will treat views just like a table. Etc, etc.

Lastly, my statement regarding your POV holds because you are still comparing the two in a limited use case (returning data set). And yes, the earlier posts are concerns at a higher level/abstraction ... Sorry, but your POV really is bottom-up if you can't appreciate what was shared.