Sql Views

Although , there is mass of information available over the internet about the view usage and its positives and negatives …. here are my two cents

1- Views can b updated

UPDATE View_Name 
SET View_Column_Name = 'SomeValue'
WHERE  View_Some_Column_Value = 'FilterValue'

Advantages of Database Views

  • Data protection – create views to protect sensitive data. For example the salary column  hidden in for some users or if non-technical person wills to see information from different tables and security of those tables is a concern.
  • Code re-use – simplifies application development. If some business logic changes for presenting data, change it once in the view and not many times in different places. For example, you have a new field in the database table, and this field is required in  N screens datagrid in your application , so if have u written sql query dynamically binded as datasource for every screen , you need to change it every sql for every screen but if all your screens datagrid are associated with a view you can easily avail that new field as it will automatically will be populated with new field as well.
  • Simplifies access to data for untrained users. Lets say you have a department of data analysts who are experts with Excel but can’t write SQL. Create them a view of the data and have them load that into Excel. They can’t write SQL but they can choose a view from a list of available datasets – everyone is happy.
  • Performance – lets say your data analysts can write SQL but you don’t want them writing run-away queries which bring your database server down. Create them a view instead of allowing them direct access to the data.

Pitfalls of using Database Views

  • Ease of use – you’re probably thinking,, if you only needed access to the data in the “Staff” table to form part of your query, why join to a view which pulls in data from the “Department” table? That is extra IO overhead which when dealing with large volumes of data or frequent batch requests can have negative performance implications. It is better to have access to either the table (if you are allowed to) or another view of the “Staff” table which allows you to view the fields which you require and use that.
  • Nested database views – don’t create views based on other views as this has a negative performance impact. Better to create views from the base tables.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: