Learning Objective
In this tutorial, you will learn what is a view in database, how views are created and used in SQL Server, how to list all the views in SQL Server, renaming a view, removing views from database.
SQL Server Views Introduction
SQL Server view is a virtual table and the content of the view is defined by a query. The query can refer one or more tables or views. Like a table, structure of a view is consist of set of named columns and rows of data. However, view does not store any data physically unless it is indexed. The columns and rows of a view come from the underlying table which are mentioned in the view definition.
Below are the few important point regarding views.
- View act as a filter between the data tables and users or applications.
- View does not occupy any storage as it does not store any data physically.
- Views are made from one or more tables or other views in the same or other databases.
- One of the main purpose of the view is to have some security mechanism. Using this, you no need to provide access directly to the underlying table to the user.
- Also column and row level security can be achieve using views.
- Views can also be used when you copy data to and from SQL Server to improve performance and to partition data.
SQL Server Views Example
Whenever we have to retrieve records which require more than one table, we used to write queries using subquery or joins.
For example, consider we have the following two tables in university schema.
| Roll_No | Name | Address |
| 1 | Sagar | Siliguri |
| 2 | Sajal | Kolkata |
| 3 | Shuvadip | Durgapur |
| 4 | Rajat | Delhi |
| 5 | Pratik | Rajkot |
| 6 | Aditya | Bangalore |
| Roll_No | Name | Total_Marks | Age |
| 1 | Sagar | 95 | 18 |
| 2 | Sajal | 65 | 20 |
| 3 | Shuvadip | 78 | 19 |
| 4 | Rajat | 70 | 21 |
| 5 | Pratik | 89 | 22 |
| 6 | Aditya | 59 | 20 |
Managing Views in SQL Server