View in SQL Server

A view is a database object that has no values. It is a virtual table, which is created according to the result set of an SQL query. However, it looks similar to an actual table containing rows and columns. Therefore, we can say that its contents are based on the base table. It is operated similarly to the base table but does not contain any data of its own. Its name is always unique, like tables. The views differ from tables as they are definitions that are created on top of other tables (or views). If any changes occur in the underlying table, the same changes reflected in the views also.

This diagram illustrates the concept of a view that included columns from more than one table. Here we have two tables named 'Table A' and 'Table B,' and by using a SQL statement, a view is created containing data from both tables. Views are a database object; that's why it does not store physically. This feature makes views excellent for abstracting or hiding complex queries.

Uses of views

The primary use of view in SQL Server is to implement the security mechanism. It prevents users from seeing specific columns and rows from tables. It only shows the data returned by the query that was declared when the view was created. The rest of the information is completely hidden from the end-user.

Types of views

The SQL Server categories the views into two types:

1. User-Defined Views

Users define these views to meet their specific requirements. It can also divide into two types one is the simple view, and another is the complex view. The simple view is based on the single base table without using any complex queries. The complex view is based on more than one table along with group by clause, order by clause, and join conditions.

2. System-Defined Views

System-defined views are predefined and existing views stored in SQL Server, such as Tempdb, Master, and temp. Each system views has its own properties and functions. They can automatically attach to the user-defined databases. We can divide the System-defined views in SQL Server into three types: Information Schema, Catalog View, and Dynamic Management View.

SQL Server allows us to create a view in mainly two ways:

Using T-SQL Query
Using SQL Server Management Studio

Let us explain both ways in detail.

Using T-SQL Query

We can create a new view by using the CREATE VIEW and SELECT statement. SELECT statements are used to take data from the source table to make a VIEW.

Syntax

The following syntax is used to create a view in SQL Server:

CREATE VIEW view_name AS  
SELECT column1, column2, ...  
FROM table_name  
WHERE condition;   

In this syntax, the view_name indicates the name of a view. It should be unique. The SELECT statement chooses columns from the source table. The WHERE is an optional clause specifying the conditions that must be met for the records to be included in the VIEW.

Example

Let us understand it with the help of an example. Suppose our database has a table named Student and Fee that contains the following data:

Now, we will create a view based on these tables. Thus, the below example will create a view name "course" that creates a virtual table made by taking data from both tables.

CREATE VIEW course_enrolled
AS 
SELECT first_name, last_name, course, amount_paid 
FROM Student AS S 
INNER JOIN Fee AS F
ON S.admission_no = F.admission_no;

We can verify the view data using the SELECT statement as below:

This query will display the below output:

Rename views in SQL Server

We can also change the name of a view in SQL Server. We can do this by using the built-in stored procedure named sp_rename or the SQL Server management studio. Here we will see both ways:

Using sp_rename

The following syntax is used to rename a view:

Suppose we want to change the name of the above-created view course_enrolled to course. The following query explains this concept:

Using SQL Server Management Studio

To change the name of a view in SSMS, we need to navigate to the Object Explorer -> Databases -> Views. Here you will see all available views. Select one that you want to modify, right-click on it and select the Rename option. For example, we are going to rename a view name course as follows:

Once we click the Rename option, we are able to change its name:

Update views in SQL Server

We can also update a view in SQL Server. We can do this by using the ALTER VIEW command or the management studio. Here we will see both ways:

ALTER VIEW Statement

Suppose we want to add one more column named 'city' in the above-created view course_enrolled. To do this, we need to use the statements as follows:

ALTER VIEW course_enrolled
AS 
SELECT first_name, last_name, course, city, amount_paid 
FROM Student AS S 
INNER JOIN Fee AS F
ON S.admission_no = F.admission_no;

We can verify this modification by using the SELECT statement. It will display the below output where we can see that the city column is added successfully.

Using SQL Server Management Studio

To alter an existing view using SSMS, we need to navigate to the Views. Then, select your desired view name that you want to modify, right-click on it and select the Design option. For example, we are going to modify a view name course_enrolled as follows:

This option will display a new design query window with existing tables and their relationship. Here we can make any changes to our views.

How to get views definition in SQL Server?

SQL Server provides the sp_helptext stored procedure that allows us to get the information of any views. We can use the below syntax to see the definition of a view:

Suppose you want to see the definition of a course_enrolled view. You can do this as follows:

It will display the following output:

List views in SQL Server

We can use the sys.views or sys.objects catalog view to list or display all views available in a SQL Server Database. Here is an example:

SELECT OBJECT_SCHEMA_NAME(v.object_id) schema_name, v.name
FROM sys.views as v;

Drop Views in SQL Server

We can also remove the existing view from the SQL Server. We can do this by using the SQL query or the management studio. Here we will see both ways:

Using SQL Query

SQL Server provides a DROP VIEW command to remove the view from a database. If the view does not exist, we will get an error. We can use the below syntax to remove a view:

Suppose you want to delete a view course_enrolled, we can do this as follows:

It will delete the view successfully. If we execute this command again, we will get the below error message:

Using SQL Server Management Studio

To remove an existing view using SSMS, we need to navigate to the Views. Select your desired view name that you want to delete, right-click on it and select the Delete option. For example, we are going to drop a view name course as follows:

Once we click on the Delete option, we will get a new window to confirm the deletion process. Click on Yes to complete the deletion. We will make sure that it will delete all permission of a view also.

Next TopicSQL Server MIN() Function

← prev next →