Saturday, 25 June 2016

Concept of Views in Sql server

Leave a Comment

What is View:

A View is a logical collection of tables in SQL server which are stored SQL server database.(A view is a virtual table that is not physically exists but it is created by joining of tables in sql server).
View appears like a table to the user (DB user). These views increases the performance of the sever.Views contains columns,rows just like tables in sql sever.These definitions of the tables are stored in Data base.
Views are used for security purpose in databases, views  restricts the user from viewing certain column and rows means by using view we can apply the restriction on accessing the particular rows and columns for specific user. Views display only those data which are mentioned in the query, so it shows only data which is returned by the query that is defined at the time of creation of the View.

syntax of View:
CREATE VIEW [schema_name.]view_name AS
  [ WITH { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }
  SELECT expressions
  FROM tables
  [WHERE conditions];

1.Here the schema_name is the schema that we want to create(view tables).
2.View_name is the name of the view we are going to create.
3.Encryption is the security that we can apply on view.
4.SCHEMABINDING  ensures that the underlying table definitions can not be modified so as to affect the view.
5.VIEW_METADATA will ensure that SQL Server has metadata about the VIEW.
6.The columns and calculations that you want to add view are added using expressions.
7.tables here we add the tables that defines the View Schema.We must add at least one table here to create a view.
8.In where clause we implement some conditions that should be followed by view.But it is an optional.

A sample example View
CREATE VIEW PRODINVENTION AS
  SELECT products.product_id, products.product_name, inventory.quantity
  FROM products
  INNER JOIN inventory
  ON products.product_id = inventory.product_id
  WHERE products.product_id >= 1000;
9.We can retrieve table using SELECT statement like we select a table.
SELECT * 
FROM PRODINVENTION 

10.We can update the view using ALTER statement and can DROP view using DROP statement.
Note : we can DROP the tables that are used by Views in SQL server.If we delete the those tables the View remains exist in the Database.
Syntax for Update & Drop
---for updation of view
ALTER VIEW [schema_name.]view_name AS
  [ WITH { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }
  SELECT expressions
  FROM tables
  WHERE conditions;
--to drop view
DROP VIEW view_name;

Advantages of Views

Security
We can restrict the user to access the confidential view that contains the important data.

Query Simplicity
A view can draw data from several different tables and present it as a single table, turning multi-table queries into single-table queries against the view.so instead of writing all statements we can retrieve using simple SELECT statements with views.

Structural simplicity
Views can give a user a "personalized" view of the database structure, presenting the database as a set of virtual tables that make sense for that user.

Consistency
A view can present a consistent, unchanged image of the structure of the database, even if the underlying source tables are split, restructured, or renamed.For example if we change the tables that contains view data may changed the view data does not changed.

Data Integrity
If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints.

Logical data independence.

 View can make the application and database tables to a certain extent independent. If there is no view, the application must be based on a table. With the view, the program can be established in view of above, to view the program with a database table to be separated.

Disadvantages of views

Performance
Views create the appearance of a table, but the DBMS must still translate queries against the view into queries against the underlying source tables. If the view is defined by a complex, multi-table query then simple queries on the views may take considerable time(more time).

Update restrictions
When a user tries to update rows of a view, the DBMS must translate the request into an update on rows of the underlying source tables. This is possible for simple views, but more complex views are often restricted to read-only.

Conclusion

This post may help you for better understanding of Views concept in SQL server.If you recommend any changes or modifications to this post please contact me.


0 comments:

Post a Comment