SQL Views vs. Database Abstraction (in code)

I just learned about SQL views, which seem nice, but if I abstract table joining to a data 开发者_开发问答access class, wouldn't this accomplish the same thing? What are your thoughts on this? I've never used views before so this is all pretty new to me.

Remember that not all applications that hit your your database may be using the same data access classes. Nor are they used in exports or reporting. The views are a better place to abstract some complex things (such as how to get certain kinds of financial information) if you want consistentcy. However, don't go overboard with abstracting things to views either. Views should not call views (at least in Sql Server but I suspect in other dbs as well) because you have to materialize all the underlying views to get the data in the top layer. This means to get to the three records you want, you might end up materializing millions of records first. With large tables this can create a performance problem of nightmare proportions. Further views that call views that call views become a truly difficult maintenance problem when something needs to be fixed.

The main purpose of a view is to abstract the complexity of creating a specific result set. In large relational databases, you often need to join many tables together to get useful data. By creating views, any client can access it without needing access to your database access layer.

Additionally, almost all RDMSs will optimize a view by caching the parsed execution plan. If you query is complicated enough, you may hit a substantial query planning hit when executing the query. However, with a view, the query plan is created and saved when the view is created or when it is used for the first time.

Views can also be great for maintaining backward compatibility. Say you have a table that needs to change, but it would be difficult to update all the clients at once to use the new table schema. You could create a view with the old table name that provides the backwards compatibility. You can then create a new table with the new schema.

I'd say one of the main purposes of views are to simplify the interface betwen a complex database (whether it be star schema/OLTP) and another layer (user / OLAP cube / reporting interface).

I guess broadly I'd say that if there can be multiple ways that you can access your database (MS Excel/.net app) then you would want to use SQL views as then they are available to all, otherwise if you create a data access class in c# (e.g.), then it wont be usable by the MS Excel people.

Views simply put, reduce the complex look of all the joins put together in a sql query. So instead of executing a join on 30 tables, a view does the 30 table join but then can be reused in another view / sproc to simply say:

SELECT * FROM myView

Rather then:

SELECT...
FROM
...
INNER JOIN
...
INNER JOIN
...
INNER JOIN
...

It basically hides all these details. This article should be a great reference: http://www.craigsmullins.com/cnr_0299b.htm

The point is views are not physical structures, they are simply a relational model or "view" of one or many tables in a database system.

Abstracting joins to a data access class might give you the same data, but it might not give you the same performance.

Also, for most businesses the database is a shared resource. It's sensible to assume that there are applications already hitting the database that cannot or will not go through your data access class. It's also sensible to assume that some future applications cannot or will not go through your data access class. As a trivial example, the command-line interface and the graphical interface to any dbms you use won't be using your data access class.

Views are also the way SQL databases implement logical data independence. Think of them as part of the public interface.

Views can be shared by interactive SQL users, report writers, OLAP tools, and applications written in different languages or by multiple programming teams that don't share classes with eahc other.

As such, it's a good way for database designers to share standardized queries across the whole community of users of the data.