Views in SQL Server

TrilochanSahoo — Mon, 24 Jan 2022 17:30:12 +0000

The view is a significant part of SQL and interesting to learn. However, it is kind of hard to understand for beginners. The view is the whole module within it. Hence, the attempt to simplify the module as much as possible.

So let's get started.

The View

In general, view means sight or outlook of a particular object or place or something. In SQL, View is a virtual table which is the result set of stored queries. Put differently, a SQL table stores data in the database and takes memory to store the data. When we create a view from an original table or a base table, we add some columns and rows according to our requirements. After the execution of the query, the view shows the only results of the query in a table-like format. It only shows the output of the query rather than storing the data of the base table.

Then arises one question, what happens when we operate on both sides? As an answer, when we perform any operation and change the actual data in the base table, it will also change the value of the view as it is dependent on the base table. But vice versa, it depends upon us. We can put restrictions on performing various operations according to our requirements. If we restrict all DML (Data Manipulation Language) commands on the view then, it acts as a read-only view and does not affect the view as well as the base table.

Advantages

Restricting the access: Views provide an additional level of security by restricting access to a predetermined set of rows and columns of a table.

Example: In a lecture table of a university database, it may contain many columns. But we can create 5-6 columns and give them to the user. As a result, we create an additional level of security on the top of the view.

Simplify Complex queries for users: Using views, we select information from multiple tables without the help of Joins.

Provide Data Independence: If we do any changes in the conceptual level view of the data, then the user-level view of the data would not be affected.

Different Views on the same data: We create different views on the same table and add different restrictions.

Example: In the university for student marks table, we can create different views based on lecture and student. The lecture has the privilege to change or update and add records where the student only read that data.

Managing Views

There are different aspects related to managing views, which are given below.

Creating view:

Views can be created by using the “create view” statement.
Syntax:-

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

Code Snippet:

CREATE VIEW [India Customers] AS
SELECT CustomerID, CustomerName
FROM Customers
WHERE Country = ‘India’;

Modifying View:

By using the “Alter View” command allows us to modify a view.

Syntax:-

ALTER VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;

Code Snippet:

ALTER VIEW [India Customers] 
AS 
SELECT CustomerID, CustomerName, CustomerAddress
FROM Customers
WHERE Country = ‘India’;

Removing View:

View can be removed by using the “Drop view” statement.

Syntax:-

DROP VIEW view_name;

Code Snippet:

DROP VIEW [India Customers];

That's the view in a nutshell.

Thanks for reading the blog. Feel free to give suggestions for any areas of improvement. :)

DEV Community: TrilochanSahoo

Views in SQL Server

The View

Advantages

Managing Views

Creating view:

Modifying View:

Removing View: