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:
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
9.We can retrieve table using SELECT statement like we select a table.
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
Advantages of ViewsSecurity
We can restrict the user to access the confidential view that contains the important data.
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.
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.
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.
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 viewsPerformance
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).
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.