Creating a view in SQL Server

In this article, we are going to learn about SQL Server views. I am covering the following topics in the article

• Basics of view and its usage.
• How to create, update, and drop the view using T-SQL query.
• How to create the view using SQL Server Management Studio.

For a better explanation, I have created a sample database that contains the data of a hospital. The database contains four tables named [Appointments],[Patients],[MedicalHistory], and [Doctors]
Here is the code to create the tables.

use HospitalManagementSystem
go
CREATE TABLE  Appointments (
    AppointmentID   int  NOT NULL,
    PatientID   int  NULL,
    DoctorID   int  NULL,
    AppointmentDate   datetime  NULL,
    Notes   varchar (max) NULL
)
CREATE TABLE Patients (
     PatientID   int  NOT NULL,
     FirstName   varchar (50) NULL,
     LastName   varchar (50) NULL,
     DateOfBirth   date  NULL,
     Gender   char (1) NULL,
     PhoneNumber   varchar (15) NULL,
     Email   varchar (100) NULL,
     Address   varchar (255) NULL
)
GO
CREATE TABLE MedicalHistory (
     MedicalHistoryID   int  NOT NULL,
     PatientID   int  NULL,
     Diagnosis   varchar (255) NULL,
     Prescription   varchar (max) NULL,
     DateRecorded   datetime  NULL
)
GO
CREATE TABLE Doctors (
     DoctorID   int  NOT NULL,
     FirstName   varchar (50) NULL,
     LastName   varchar (50) NULL,
     Specialization   varchar (50) NULL,
     PhoneNumber   varchar (15) NULL,
     Email   varchar (100) NULL,
     Address   varchar (255) NULL)

I have inserted dummy records in the tables using dbForge Studio for SQL Server which has a feature called Data generator. It helps to create a real-time data set that helps to run various test cases for the application.
Now, let us understand the basics of SQL Server views.

Understanding the Basics of Views

A view is a virtual table that contains the output of the SELECT query. The views are stored as a database object and like a table, it contains the columns. Note that the view contains only those columns that are returned by a query which is used to create a view. For example, if the query which is used to create a view, returns five columns then a view can contain only those five columns.

Unlike tables, the views do not store actual data in a database. Whenever we execute SELECT statement on view, it executes the T-SQL query which is used to create the view and returns the data from underlying tables.

The SELECT query that is used to define a view can have various string and numeric functions. Also, the query has data from one or more than one table, which can exist on the same database or different databases.

The views can be considered as an added security layer because when a user executes a query against a view, it retrieves the specific subset of the data. Moreover, the underlying structure of a table remains hidden. For example, if your database contains the details of the credit card of a customer and you do not want anyone to access that column, you can create a view on top of the base table and grant permissions to access that view only.
There are mainly five types of views.

• Simple views: The simple views contain a simple SELECT statement which populates data from one table. As simple view references to one table, we can perform INSERT, UPDATE , and DELETE operations.
• Complex Views: The complex views contain a SELECT statement that joins multiple tables and may contain aggregate functions, string manipulation functions and other complex business logic. The complex views cannot be updated.
• Updatable Views: The updatable views allows us to perform INSERT, UPDATE , and DELETE operations. When we perform any DML operation on UPDATABLE view, the chages will be applied on the underlying tables. Note that, the updatable view cannot be complex, meaning it cannot contain an aggregate function or group by clause. It must not contain a DISTINCT keyword. The view must reference one table and if the view references multiple tables, then all tables must be joined using INNER JOIN only.
• Indexed views: The indexed views are materialized views. As the name suggests, we can add a unique index on it. The index views have performance benefits when we are using a query that has aggregate functions on various columns. However, if the data of the underlying tables of the materialized views is frequently updated, it is not a good idea to use an indexed view. You can read Create indexed views article to learn more about the indexed views.
• Partitioned views: The partitioned views are like SQL Server table partitions but unlike table partitions, we do not need to create various partition functions or schema. However, the partition views are not as flexible as table partitions but if configured properly, they give good results in terms of performance, data manageability, and archival process. You can read SQL Server Partitioned Views article to learn more about partitioned views.
• System Views: The system views are used to show the SQL Server metadata. When we query a system catalog view, it provides the details of the metadata information of the SQL Server database and its configurations. The simple example is sys.tables view which shows the details of the tables created in a database. You can read System Views article to learn more about the system catalog views.

Now, let us understand how to create and manage SQL Server views.

Creating a View in SQL Server

The syntax to create a view is following:

create view [view_name]
as
sql_query..

In the syntax,

• view_name: Specify the name of the view.
• sql_query: Specify the SELECT query to fetch the data from one or multiple tables.

For example, I want to create a view that populates the details of the patients admitted to a hospital. The details of registered patients are stored on a [Patient] table. Hence, we are going to execute the SELECT statement on the [Patient] table.
Here is the query to create a view.

create view vw_patientDetails
as
select patientID, firstname, lastname, dateofbirth,gender, phonenumber,email,address from [Patients]
where  (FirstName is not null and lastname is not null)
and  (FirstName <>'' and lastname <>'')

When you execute the above query, SQL Server will create a view named [vw_patientDetails]. Now, to fetch the data from the view, you can execute a select statement against the view.
Here is the query to view the data of a patient from view.

Select * from vw_patientDetails

Here is the output.

As I mentioned in an earlier section, you can query a view just like the way you run a query on a SQL Server physical table. You can apply aggregate and string manipulation and other SQL Server built-in functions. Now, let us understand how we can use a query with joins to create a view.

Creating a View with Multiple Tables

This section explains how we can use a query that joins multiple tables in a view. The syntax is same as creating view

create view [view_name]
as
sql_query..

In the syntax,

• view_name: Specify the name of the view.
• sql_query: Specify the SELECT query with Joins to fetch the data from multiple tables.

For example, we want to create a view that shows the list of patients and the name of the doctor who is treating them. For that, we are joining [Patients] and [MedicalHistory] tables.

The join query is the following:

USE HospitalManagementSystem
go
SELECT p.FirstName ,p.LastName,p.Gender,p.PhoneNumber,mh.Diagnosis,mh.Prescription from [Patients] p INNER join
[MedicalHistory] mh ON p.PatientID=mh.PatientID
WHERE
  (FirstName is not null and lastname is not null)
and  (FirstName <>'' and lastname <>'')

Now, to create a view named vw_patients_dignosis, execute the following query.

create view vw_patients_dignosis
as
SELECT p.FirstName ,p.LastName,p.Gender,p.PhoneNumber,mh.Diagnosis,mh.Prescription from [Patients] p INNER join
[MedicalHistory] mh ON p.PatientID=mh.PatientID
WHERE
  (FirstName is not null and lastname is not null)
and  (FirstName <>'' and lastname <>'')

Once a view is created, run the following query to get the data.

USE HospitalManagementSystem
go
select * from vw_patients_dignosis

Query output

Now, let us understand how to update a view.

Updating a View in SQL Server

If you want to modify the SELECT statement in a view meaning altering the view, we can use ALTER VIEW statement. The syntax is following:

ALTER VIEW [your_view_name]
as
updated_SQL_Query

In the syntax

• your_view_name: Specify the name of the view that you want to modify.
• updated_SQL_Query: Specify the modified T-SQL query.

For example, we want to alter the view named vw_patients_dignosis. The view shows the list of patients whose diagnosis is null. To do that, we will add another condition in the WHERE clause of the query:
Here is the updated SQL query.

SELECT p.FirstName ,p.LastName,p.Gender,p.PhoneNumber,mh.Diagnosis,mh.Prescription from [Patients] p INNER join  
[MedicalHistory] mh ON p.PatientID=mh.PatientID 
WHERE  
  (FirstName is not null and lastname is not null) 
and  (FirstName <>'' and lastname <>'') 
and (Diagnosis is not null and Diagnosis<>'')

Execute the code below to ALTER the view.

ALTER view vw_patients_dignosis 
as 
SELECT p.FirstName ,p.LastName,p.Gender,p.PhoneNumber,mh.Diagnosis,mh.Prescription from [Patients] p INNER join  
[MedicalHistory] mh ON p.PatientID=mh.PatientID 
WHERE  
  (FirstName is not null and lastname is not null) 
and  (FirstName <>'' and lastname <>'') 
and (Diagnosis is not null and Diagnosis<>'')

Verify the changes by running the SELECT statement against the vw_patients_dignosis view.

use HospitalManagementSystem
go
select * from vw_patients_dignosis

Query output

As you can see the query has returned a list of patients whose diagnosis is not null which shows that the view has been updated.

Creating a View with Aggregate Functions

This section shows how to create a view using the aggregate function. We can also use a query that has the aggregate functions to create a view. The syntax is the same that we used to create a regular view.

For example, we want to create a view that contains a list of patients and the total number of prescribed medications. For that, we will use count() and group BY() functions. You can read Aggregate Functions (Transact-SQL) - SQL Server article to learn more about the aggregate functions and their usage in SQL Server.
The query to view a list of patients with medication is following:

SELECT p.FirstName ,p.LastName,p.Gender,p.PhoneNumber,Sum(mh.Prescription)TotalMedications from [Patients] p INNER join  
[MedicalHistory] mh ON p.PatientID=mh.PatientID 
WHERE  
  (FirstName is not null and lastname is not null) 
and  (FirstName <>'' and lastname <>'') 
and (Diagnosis is not null and Diagnosis<>'')
group by p.FirstName ,p.LastName,p.Gender,p.PhoneNumber

You can encapsulate this query in vw_patient_medication by running the following statement.

create view vw_patient_medication  
as 
SELECT p.FirstName ,p.LastName,p.Gender,p.PhoneNumber,count(mh.Prescription)TotalMedications from [Patients] p INNER join  
[MedicalHistory] mh ON p.PatientID=mh.PatientID 
WHERE  
  (FirstName is not null and lastname is not null) 
and  (FirstName <>'' and lastname <>'') 
and (Diagnosis is not null and Diagnosis<>'')
group by p.FirstName ,p.LastName,p.Gender,p.PhoneNumber

Run the following query to view the data.

use HospitalManagementSystem
go
select * from vw_patient_medication order by TotalMedications desc

Query output:

Now, let us see, how we can create a view using SQL Server Management Studio.

Creating a View Using SSMS

We can also create a view using SQL Server Management Studio. For example, we want to create view that shows list of patients with their appointment dates with the respective doctors. We will use patient, doctors, and appointment tables.

To create a view, connect to SQL Server instance. → Expand HospitalManagementSystem database → Right-click on Views → Select New View.

A query designer opens which shows the list of tables created in the database. Select the desired tables and click Add.

In a query designer, you can select the desired columns. As shown in below screenshot, the query designer automatically creates the query based on the selected columns.

You can also make changes in auto-generated queries. Once the desired query is ready, click on the Save button which is in the menu bar. The SQL Server management studio prompts to provide the name of the view. Specify vw_patient_appointment as a name of the view.

Once the view is created, Execute following T-SQL query to verify the data.

select * from vw_patient_appointments

Query output

Dropping a View in SQL Server

We can use the DROP VIEW statement to drop any view. When we drop a view from the database, make sure that the same view is not referenced by any other stored procedure or function. If it is being referenced by any other database object, the application code will break. Note that, the ALTER or CONTROL permission is needed on the view that you are dropping. The T-SQL query is following:

Drop view [view_name]

In the above syntax view_name represents the name of the view that you want to drop. If the view exists in a specific schema, you must specify the schema name. The naming will be [Schema_name].[view_name].

Suppose, we want to delete a view named vw_patient_medication from the database. The view exists on the dbo schema, hence we do not need to specify the schema name in DROP VIEW statement. To drop the view, execute following T-SQL query:

drop view vw_patient_medication

Conclusion

In this article, we learned about the basics of SQL Server views and different types of views. The article covers following topics with syntax and various examples.

• Basics of view and its usage.
• How to create, update, and drop the view using T-SQL query.
• How to create the view using SQL Server Management Studio.

Creating a SQL Server view using SQL Server management studio is fairly simple for the developers who has limited knowledge. But if you are a seasoned developer and creating a view that has very complex SQL Queries, you might consider using the dbForge Studio for SQL Server to create views. The dbForge Studio has lot of features that helps programmers to develop the database code very easily. It has a powerful feature named auto-complete. It helps to write complex SQL queries that are used to create views.