View in SQL Server

Introduction

In this article, I describe Views in SQL Server. This is a simple topic. I hope this article will help you like my Windows Store articles. Please give me your valuable suggestions and feedback to improve my articles.

What is a View

Views are database objects like virtual tables with no physical stores and containing data from one table or multiple tables. A View has no physical storage, so they do not contain any data. When we update, insert, or apply any operation over the view, these operations are applied to the table(s) on which the view was created.

Types Of View

System View
User Define View

User Defined Views are essential, so I describe only User Defined Views. They are two types:

Simple View
Complex view

1. Simple View

When a View is created on a single Table, it is called a Simple View. We can apply all operations on a Simple View that we can apply on a table.

First, we create a table by using the below SQL CREATE TABLE statement, on which we create a view.

CREATE TABLE emp (
    empId INT,
    empName VARCHAR(15),
    empAdd VARCHAR(15)
);

Now insert data by the following SQL INSERT statement.

INSERT INTO emp 
SELECT 1, 'deepak', 'UA' UNION ALL
SELECT 2, 'Middha', 'Punjab' UNION ALL
SELECT 3, 'd', 'Delhi' UNION ALL
SELECT 4, 'gourav', 'Noida' UNION ALL
SELECT 5, 'deepakia', 'Laksar' UNION ALL
SELECT 6, 'Deep', 'Haridwar';

Table

Creation of a simple view

To create view, use the below SQL CREATE VIEW statement.

CREATE VIEW v1 
AS
SELECT * FROM emp;

Operations on view

To see all the data of the view, use the below SQL SELECT statement.

SELECT * FROM v1;

See the specific data of the view using SQL SELECT statement with WHERE condition

SELECT * FROM v1 WHERE empId = 4;

Insert the data by using view that was created few steps back.

INSERT INTO v1 VALUES (7, 'raj', 'canada');

Update the data by using SQL UPDATE statement

UPDATE v1 SET empAdd = 'usa' WHERE empId = 7;

Delete the data using SQL DELETE statement with WHERE condition

DELETE FROM v1 WHERE empId = 7;

Rename the view name by using sp_rename procedure that is predefined.

EXEC sp_rename 'v1', 'v11';

Execute sp_helptext to view SQL statement of view v1 that was created in earlier steps

EXEC sp_helptext 'v1';

Drop the view

If you want to drop the view, then use the below SQL DROP statement to drop the existing view.

DROP VIEW v1;

Encrypted View

If you would like to create the view with encryption, then use the below SQL statement

CREATE VIEW v1
WITH ENCRYPTION
AS
SELECT * FROM emp;

2. Complex view

Views created on more than one table are called Complex Views. We cannot perform all operations of a table on a Complex View.

First, we create a table and insert some data.

CREATE TABLE empStatus (empId INT, empStatus VARCHAR(10));

INSERT INTO empStatus
SELECT 1, 'active' UNION ALL
SELECT 2, 'inactive' UNION ALL
SELECT 4, 'active' UNION ALL
SELECT 5, 'inactive' UNION ALL
SELECT 6, 'active';

Table

SELECT * FROM empStatus;

Creation of complex view

You can use the below SQL CREATE VIEW statement to create complex views, including joins and where conditions if required.

CREATE VIEW VComplex AS 
SELECT e.empId, e.empName, e1.empStatus 
FROM emp e 
INNER JOIN empStatus e1 
ON e.empId = e1.empId;

See all the records

To retrieve the records by using your created view, use the below-mentioned SQL SELECT statement

SELECT * FROM VComplex;

See specific record

Use WHERE condition in SQL SELECT statement to retrieve the particular record.

SELECT * FROM VComplex WHERE empId = 4

If we try to insert, update or delete in a complex view, it shows an error as in the following.

INSERT INTO VComplex VALUES (11, 'd', 'inactive')

Encryption of the Complex View

Use the below SQL CREATE statement to create complex view including joins and where condition as required.

CREATE VIEW VComplex
WITH ENCRYPTION 
AS 
SELECT 
    e.empId,
    e.empName,
    e1.empStatus
FROM 
    emp e
    INNER JOIN empStatus e1 ON e.empId = e1.empId

Summary

In this article, I described Views in SQL Server. I hope this article has helped you to understand this topic. Your feedback and constructive contributions are welcome. Please share if you know more about this.