Why do you create a View in a database?

Question

When and Why does some one decide that they need to create a View in their database? Why not just run a normal stored procedure or select?

Answer 1

I think first one .To hide the complexity of Query. Its very appropriate for views .How when we normalize Database tables increases.Now to fetch data is very difficult when number of tables increases.So best way to handle is follow views.If i am wrong correct me.

Answer 2

There is more than one reason to do this. Sometimes makes common join queries easy as one can just query a table name instead of doing all the joins.

Another reason is to limit the data to different users. So for instance:

Table1: Colums - USER_ID;USERNAME;SSN

Admin users can have privs on the actual table, but users that you don't want to have access to say the SSN, you create a view as

CREATE VIEW USERNAMES AS SELECT user_id, username FROM Table1;

Then give them privs to access the view and not the table.

Answer 3

Here are two common reasons:

You can use it for security. Grant no permissions on the main table and create views that limits column or row access and grant permissions to users to see the view.

You can use use it for convenience. Join together some tables that you use together all the time in the view. This can make queries consistent and easier.

Answer 4

I like to use views over stored procedures when I am only running a query. Views can also simplify security, can be used to ease inserts/updates to multiple tables, and can be used to snapshot/materialize data (run a long-running query, and keep the results cached).

I've used materialized views for run longing queries that are not required to be kept accurate in real time.

Answer 5

A view is an encapsulation of a query. Queries that are turned into views tend to be complicated and as such saving them as a view for reuse can be advantageous.

Answer 6

Generally i go with views to make life easier, get extended details from some entity that's stored over multiple tables (eliminate lots of joins in code to enhance readability) and sometimes to share data over multiple databases or even to make inserts easier to read.