[RESOLVED] Sql views

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