What is a good reason to use SQL views?_问答_开发者

I am reading through the SQL Server 2008 bible and I am covering the views section. But the author really doesn't 开发者_JAVA技巧explain the purpose of views. What is a good use for views? Should I use them in my website and what are the benefits of them?

Another use that none of the previous answers seem to have mentioned is easier deployment of table structure changes.

Say, you wish to retire a table (T_OLD) containing data for active users, and instead use a new table with similar data (named T_NEW) but one that has data for both active and inactive users, with one extra column active.

If your system(s) have gazillion queries that do SELECT whatever FROM T_OLD WHERE whatever, you have two choices for the roll-out:

1) Cold Turkey - Change the DB, and at the same time, change, test and release numerous pieces of code which contained said query. VERY hard to do (or even coordinate), very risky. Bad.

2) Gradual - change the DB by creating the T_NEW table, dropping the T_OLD table and instead creating a VIEW called T_OLD that mimics the T_OLD table 100% (e.g the view query is SELECT all_fields_except_active FROM T_NEW WHERE active=1).

That would allow you to avoid releasing ANY code that currently selects from T_OLD, and do the changes to migrate code from T_OLD to T_NEW at leisure.

This is a simple example, there are others a lot more involved.

P.S. On the other hand, you probably should have had a stored procedure API instead of direct queries from T_OLD, but that's not always the case.

(Copied from the first tutorial that came up in a Google search (link now dead), but it has all of the benefits I would have typed manually myself.)

Views have the following benefits:

Security - Views can be made accessible to users while the underlying tables are not directly accessible. This allows the DBA to give users only the data they need, while protecting other data in the same table.

Simplicity - Views can be used to hide and reuse complex queries.

Column Name Simplication or Clarification - Views can be used to provide aliases on column names to make them more memorable and/or meaningful.

Stepping Stone - Views can provide a stepping stone in a "multi-level" query. For example, you could create a view of a query that counted the number of sales each salesperson had made. You could then query that view to group the sales people by the number of sales they had made.

Some reasons from Wikipedia:

Views can provide advantages over tables:

Views can represent a subset of the data contained in a table
Views can join and simplify multiple tables into a single virtual table
Views can act as aggregated tables, where the database engine aggregates data (sum, average etc) and presents the calculated results as part of the data
Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents
Depending on the SQL engine used, views can provide extra security
Views can limit the degree of exposure of a table or tables to the outer world

VIEWS can be used as reusable sections of SELECT/CODE, that can be included in other selects/queries to be joined on, and use various different filters, without having to recreate the entire SELECT every time.

This also places logic in a single location, so that you do not have to change it all over the code base.

Have a look at

Choice Between Stored Procedures, Functions, Views, Triggers, Inline SQL

The main beauty of a view is that it can be used like a table in most situations, but unlike a table, it can encapsulate very complex calculations and commonly used joins. It can also use pretty much any object in the db except for stored procedures. Views are most useful when you always need to join the same set of tables say an Order with an Order Detail to get summary calculation fields etc.

A view is an abstraction layer, and it does what any good abstraction layer does, including encapsulating the database schema and protecting you from the consequences of changing internal implementation details.

It's an interface.

Here are some of many reasons of using view rather than table directly

Simplicity - Views can be used to hide complex queries.
Security - View can hide some important information from end user by creating view on some selected columns
Security - Secure table to change the structure of it by using VIEW.
Redundancy - Reduce redundant code in every procedures/query by using a common view.
Calculation - All the calculations can be done once in view query.
Meaningful Name - Table may have name for id like tbl_org_emp_id which can alias like [Employee No] or some meaningful name.

From imexploring.com

Here is one very common use of using views to constrain an entity by some criteria.

Table: USERS contains all users

View: ACTIVE_USERS contains all users excluding those who are suspended, banned, waiting to be activated and not meeting any criteria you may choose to define in the future as part of the active requirements. This makes it unnecessary to delete any rows from your USERS table should you choose not to because ACTIVE_USERS can always hide the unwanted rows.

This way, you can use the table in your user management pages but the rest of the application can use ACTIVE_USERS as they may be the only users that should be able to execute processes and access/modify data.

Views can allow you to combine data from several different tables and format it (combine fields, give more meaningful field names, etc.) so that it's easier for end users. They are an abstraction of the database model. They can also be used to give users access to the data in the table without giving them direct access to the table itself.

A small list of common reasons/uses:

use them to change the format or 'look' of data (i.e. you might join a first and last name together)

perform calculations or other lookups on data

denormalize data (extract data from several tables in to one spot)

The Views are evil! Avoid them if possible and use only for the reason mentioned by DVK - temporary data migration.

You should understand that in a database with a 100 tables it is hard to remember the purpose of every table. Now, if you add here another 300 views it will become a complete mess. Than the 'View lovers' tend to use nested views and then use the nested views in stored procedures. I personnaly work now with a database where there are Views nested in depth 4 times! So to understand a simpliest logic of a stored procedure I have to go through all the views first.