Views in SQL Server: A Beginner's Guide

Introduction

Getting data from multiple database tables and working with this data is a common need for business applications. Views are one of the ways to do so in a database. We can create a view and get data from multiple tables. Views create a virtual table in the database and a query can be used to work with a view. In this article, we will learn about views in SQL Server. Here we will cover topics such as What is a view in SQL, types of views, create a view in SQL, and alter views with SQL code examples in SQL Server.

Views in SQL Server

Views are virtual tables that hold data from one or more tables. It is stored in the database. A view does not contain any data itself, it is a set of queries that are applied to one or more tables that are stored within the database as an object. Views are used for security purposes in databases. Views restrict the user from viewing certain columns and rows.

In other words, using a view we can apply the restriction on accessing specific rows and columns for a specific user. A view can be created using the tables of the same database or different databases. It is used to implement the security mechanism in the SQL Server.

In the preceding diagram, we have created a view that contains the columns of two tables, Table A and Table B, using a query. A view is created using a select statement. Views are stored in the database as an object so it doesn't require additional storage space. Before starting any discussion about views we should have a basic knowledge of them.

Uses of views in SQL

Views are used to implement the security mechanism in SQL Server. Views are generally used to restrict the user from viewing certain columns and rows. Views display only the data specified in the query, so it shows only the data that is returned by the query defined during the creation of the view. The rest of the data is totally abstract from the end user. 

Types of views in SQL Server

There are the following two types of views.

1. User-Defined Views
2. System-Defined Views

First, we discuss the User-Defined Views.

User Define Views

First, we create two tables. First, create an Employee_Details table for the basic info of an employee.

CREATE TABLE [dbo].[Employee_Details]
(  
    [Emp_Id] [int] IDENTITY(1,1) NOT NULL,  
    [Emp_Name] [nvarchar](50) NOT NULL,  
    [Emp_City] [nvarchar](50) NOT NULL,  
    [Emp_Salary] [int] NOT NULL,  
 CONSTRAINT [PK_Employee_Details] PRIMARY KEY CLUSTERED   
   (  
    [Emp_Id] ASC  
   )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
)
 ON [PRIMARY]  
  
GO  

Now insert some data into the table as in the following.

Insert Into Employee_Details Values('Pankaj','Alwar',25000)  
Insert Into Employee_Details Values('Rahul','Jaipur',26000)  
Insert Into Employee_Details Values('Rajan','Delhi',27000)  
Insert Into Employee_Details Values('Sandeep','Alwar',28000)  
Insert Into Employee_Details Values('Sanjeev','Jaipur',32000)  
Insert Into Employee_Details Values('Narendra','Alwar',34000)  
Insert Into Employee_Details Values('Neeraj','Delhi',29000)  
Insert Into Employee_Details Values('Div','Jaipur',25000)  
Insert Into Employee_Details Values('Tanuj','Alwar',22000)  
Insert Into Employee_Details Values('Nitin','Jaipur',20000)  

Now the table Employee_Detail will look as in the following.

Select * from Employee_Details  

 

We create another table named Employee_Contact.

CREATE TABLE [dbo].[Employee_Contact]
(  
    [Emp_Id] [int] NOT NULL,  
    [MobileNo] [nvarchar](50) NOT NULL  
) ON [PRIMARY]  
  
GO  
  
ALTER TABLE [dbo].[Employee_Contact]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Contact_Employee_Details] FOREIGN KEY([Emp_Id])  
REFERENCES [dbo].[Employee_Details] ([Emp_Id])  
GO  
  
ALTER TABLE [dbo].[Employee_Contact] CHECK CONSTRAINT [FK_Employee_Contact_Employee_Details]  
GO  

Insert some values into the Employee_Contact table as in the following.

Insert Into Employee_Contact Values(1,'9813220191')  
Insert Into Employee_Contact Values(2,'9813220192')  
Insert Into Employee_Contact Values(3,'9813220193')  
Insert Into Employee_Contact Values(4,'9813220194')  
Insert Into Employee_Contact Values(5,'9813220195')  
Insert Into Employee_Contact Values(6,'9813220196')  
Insert Into Employee_Contact Values(7,'9813220197')  
Insert Into Employee_Contact Values(8,'9813220198')  
Insert Into Employee_Contact Values(9,'9813220199')  
Insert Into Employee_Contact Values(10,'9813220135')  

Now the table Employee_Contact will look as in the following.

select * from Employee_Contact   

 

Now we start a detailed discussion of User Defined Views (UDVs).

Create SQL VIEW in SQL Server

1. CREATE VIEW view_name AS  
2. SELECT columns  
3. FROM tables  
4. WHERE conditions;  

Let us create some views.

Method 1, We can select all columns of a table. The following example demonstrates that.

Create View Employee_View1  
as  
select * from Employee_Details  

Method 2, We can select specific columns of a table. The following example demonstrates that.

Create View Employee_View2  
as  
select Emp_Id,Emp_Name,Emp_City from Employee_Details  

Method 3, We can select columns from a table with specific conditions. The following example demonstrates that.

Create View Employee_View3  
as  
select * from Employee_Details where Emp_Id>3  

Method 4, We can create a view that will hold the columns of different tables. The following example demonstrates that.

Create View Employee_View4  
as  
select Employee_Details.Emp_Id,Employee_Details.Emp_Name,Employee_Details.Emp_Salary,Employee_Contact.MobileNo from Employee_Details   
Left Outer Join  
Employee_Contact   
on  
Employee_Details .Emp_Id= Employee_Contact.Emp_Id  
Where Employee_Details.Emp_Id>2 

Retrieve Data From View in SQL Server

This SQL CREATE VIEW example would create a virtual table based on the result set of the select statement. Now we can retrieve data from a view as follows.

Select * from Employee_View4  
  
Select Emp_Id,Emp_Name,Emp_Salary from Employee_View4  

The preceding query shows that we can select all the columns or some specific columns from a view.

Dropping a View in SQL Server

We can use the Drop command to drop a view. For example, to drop the view Employee_View3, we can use the following statement.

Drop View Employee_View1

Renaming the View in SQL Server

We can use the sp_rename system procedure to rename a view. The syntax of the sp_rename command is given below.

Sp_Rename OldViewName , NewViewName  

Example

Sp_Rename Employee_View4 , Employee_ViewNew   

In the preceding example, we rename the view Employee_View1 as Employee_ViewNew.

Getting Information about a view

We can retrieve all the information of a view using the Sp_Helptext system Stored Procedure. Let us see an example.

Sp_Helptext Employee_View4  

Output

 

Alter View in SQL Server 

We can alter the schema or structure of a view. In other words, we can add or remove some columns or change some conditions that are applied in a predefined view. Let us see an example.

Alter View Employee_View4  
as  
select Employee_Details.Emp_Id,Employee_Details.Emp_Name,Employee_Details.Emp_Salary,Employee_Contact.MobileNo from Employee_Details   
Left Outer Join  
Employee_Contact   
on  
Employee_Details .Emp_Id= Employee_Contact.Emp_Id  
Where Employee_Details.Emp_Id>5 and Employee_Details.Emp_City='Alwar'  

Refreshing a View in SQL Server

Let us consider the scenario now by adding a new column to the table Employee_Details and examine the effect. We will first create a view.

Create View Employee_View1  
as  
Select * from Employee_Details  

Now add a column in the Employee_Details table  

Alter Table Employee_Details Add MY_sal nvarchar(50)  

Now retrieve the data from the table and view and you will receive the following output.

Select * from Employee_Details  
Select * from Employee_View1  

Output

 

We don't get the results we expected because the schema of the view is already defined. So when we add a new column to the table it will not change the schema of the view and the view will contain the previous schema. For removing this problem we use the system-defined Stored Procedure sp_refreshview.

sp_refreshview is a system-level Stored Procedure that refreshes the metadata of any view once you edit the schema of the table. Let's execute the following.

Exec sp_refreshview Employee_View1    
Select * from Employee_Details    
Select * from Employee_View1    

Output

 

SchemaBinding a VIEW 

In the previous example, we saw that if we add a new column to the table then we must refresh the view.

In such a way if we change the data type of any column in a table then we should refresh the view. If we want to prevent any type of change in a base table then we can use the concept of SCHEMABINDING. It will lock the tables being referred to by the view and restrict all kinds of changes that may change the table schema (no Alter command).

We can't specify "Select * from tablename" with the query. We need to specify all the column names for reference.

Create View Employee_Details3  
with SCHEMABINDING  
as  
select Emp_Id,Emp_Name,Emp_Salary,Emp_City from DBO.Employee_Details  

In the preceding example, we create a view using Schemabinding. Now we try to change the datatype of Emp_Salary from int to Decimal in the Base Table.

 

We find that we cannot change the data type because we used the SCHEMABINDING that prevents any type of change in the base table.

Encrypt a view in SQL Server

The “WITH ENCRYPTION” option can encrypt any views. That means it will not be visible via SP_HELPTEXT. This option encrypts the definition. This option encrypts the definition of the view. Users will not be able to see the definition of the view after it is created. This is the main advantage of the view where we can make it secure.

Create View Employee_Details4  
with Encryption  
as  
select Emp_Id,Emp_Name,Emp_Salary,Emp_City from DBO.Employee_Details  

Now we try to retrieve the definition of the view.

Exec sp_helptext 'Employee_Details4'  

Output

 

Check Option

The use of the Check Option in a view is to ensure that all the Update and Insert commands must satisfy the condition in the view definition.

Let us see an Example

GO  
  
Create view [dbo].[Employee_Details7]  
as  
select * from Employee_Details  
where Emp_Salary>30000   
  
GO  

In the preceding example, we create a view that contains all the data for which Emp_Salry > 30000 but we can insert the data for a salary less than 30000 as follows.

Insert Into Employee_Details7 values ('ram','mumbai',25000,'Pan')  

For preventing this problem we can use the Check Option property such as.

GO  
  
Create view [dbo].[Employee_Details7]  
as  
select * from Employee_Details  
where Emp_Salary>30000   
with Check Option  
GO  

Now if we try to execute the preceding query then it will throw an error such as.

Insert Into Employee_Details7 values ('ram','mumbai',25000,'Pan')  

Output

Msg 550, Level 16, State 1, Line 1

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

DML Query In View

In a view, we can implement many types of DML queries like insert, update and delete. But for a successful implementation of a DML query we should use some conditions like:

1. View should not contain multiple tables
2. View should not contain a set function.
3. View should not use the Distinct keyword
4. View should not contain Group By, having clauses 
5. View should not contain Sub query
6. View should not use Set Operators 
7. All NOT NULL columns from the base table must be included in the view in order for the INSERT query to function.

If we use the preceding conditions then we can implement a DML Query in the view without any problem. Let us see an example.

select * from Employee_Details7   

Output

 

Now we implement a DML Query as in the following.

Insert Into Employee_Details7 values ('ram','mumbai',35000)  
Update Employee_Details7 set Emp_Name='Raju' where Emp_id=5  
delete from Employee_Details7 where Emp_Id=6  
select * from Employee_Details7   

Output

 

System Define Views

SQL Server also contains various predefined databases like Tempdb, Master, and temp. Each database has its own properties and responsibility. Master data is a template database for all other user-defined databases.

A Master database contains many Predefine_View that work as templates for other databases and tables. Master databases contain nearly 230 predefined views.

 

These predefined views are very useful to us. Mainly we divide system views into the following two parts.

1. Information Schema
2. Catalog View

Information schema

There are nearly 21 Information Schemas in the System. These are used for displaying the most physical information of a database, such as table and columns. An Information Schema starts from INFORMATION_SCHEMA.[View Name].

Let us see an example.

select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE  
where TABLE_NAME='Employee_Details'  

Output

 

This Information_Schema returns the details of all the views used by table Employee_Details.

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS  
where TABLE_NAME='Employee_Details'  

Output

 

This Information_Schema returns the information about the constraints of a table.

Catalog View

Catalog Views have been categorized into various groups also. These are used to show the self-describing information of a database. These start with “sys”.

select * from sys.all_views  

 

This query provides information to all types of views using a database.

select * from sys.databases  

 

This query will provide information about all the databases defined by the system, including user-defined and system-defined databases.

Conclusion

In this article, we learned about views in SQL Server with its different types with code examples in SQL Server.