What is View in SQL Server?
A View is a logical collection of tables in SQL Server which are stored in the SQL server database. (A view is a virtual table that does not physically exist but is created by the joining of tables in SQL server).
View appears like a table to the user (DB user). These views increase the performance of the sever. Views contain columns and rows just like tables in SQL Server. These definitions of the tables are stored in the database.
Views are used for security purposes in databases, and views restrict the user from viewing certain columns and rows which means by using view we can apply the restriction on accessing the particular rows and columns for a 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 the 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];
Explanation of Syntax
- Here the schema_name is the schema that we want to create(view tables).
- View_name is the name of the view we are going to create.
- Encryption is the security that we can apply on view.
- SCHEMABINDING ensures that the underlying table definitions cannot be modified so as to affect the view.
- VIEW_METADATA will ensure that SQL Server has metadata about the VIEW.
- The columns and calculations that you want to add to view are added using expressions.
- Tables -- here we add the tables that define the View Schema. We must add at least one table here to create a view.
- In the where clause we implement some conditions that should be followed by view. But it is 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;
We can retrieve table using SELECT statement, like we select a table.
SELECT *FROM PRODINVENTION
We can update the view using ALTER statement and can DROP view using DROP statement.
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;
Note
We can DROP the tables that are used by Views in SQL server. If we delete those tables the View remains in the Database.
Advantages of Views in SQL Server
- 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 contain view, the data may change but the view data does not change.
- 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 the view above, to view the program with a database table to be separated.
Disadvantages of views in SQL Server
- 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 to have a better understanding of the views concept in SQL server. If you recommend any changes or modifications to this post please contact me.