What is a good reason to use SQL views

Question

I am reading though the sql server 2008 bible and I am covering the views section. But he really dont 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.

Answer 1

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.

Answer 2

(Copied from the first tutorial that came up in a Google search, 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.

Answer 3

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.

Answer 4

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

Answer 5

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)

Answer 6

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 T_NEW, dropping T_OLD 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.

Answer 7

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.

Answer 8

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.