VIEWS IN SQL SERVER

VIEW :-

• A view is a structured query language (SQL) query stored as an object.
• A view can be used to reduce the complexity of database schema.
• Views are used to implement the security mechanism in SQL Server.

Two types of view

• User-Defined View
• System Defined View

USER DEFINED VIEW:-

I have created a table Customer table and a Product table with a foreign key relationship.

CUSTOMER TABLE

PRODUCT TABLE

CREATE VIEW:-

Here we can create and retrieve a view that holds multiple tables.

CREATE VIEW

RENAME VIEW:-

We can rename the view by using Sp_Rename System stored procedure.

Syntax:- Sp_Rename OldViewName , NewViewName

Example:-

RENAME VIEW

If you want to check the rename apply on view then goto Database and refresh Views then expand it.

RENAME SUCCESSFULLY

GETTING INFORMATION ABOUT THE VIEW:-

Here we retrieve all information about view by using Sp_Helptext System stored procedure.

INFORMATION ABOUT VIEW

ALTER VIEW:-

In the Alter View, we can add or remove a column. Here We can those customer detail display whose id is less than six and gender is female.

ALTER VIEW

REFRESH THE VIEW:-

Now we are adding a new column to the table. First, we create a view and then add a new column in the table.

ADD A NEW COLUMN

Here address column added successfully in the table but it doesn’t display in the view because the schema of the view already defined.

ADDED COLUMN IN TABLE

After refreshing the view we can retrieve the data easily. Sp_Refreshview is a system-level stored procedure that refreshes the metadata of view once you edit the schema of the table.

REFRESH VIEW

SCHEMABINDING IN VIEW:-

Schema binding uses when you want to prevent changes in the table. Here we create schema binding.

SCHEMABINDING IN VIEW

Now, If we try to change the datatype in the table then display the warning message on your screen.

WARNING

ENCRYPT VIEW:-

We can encrypt the definition of the view help of WITH Encryption.

ENCRYPTION IN VIEW

DROP VIEW:-

We can use the drop view by using the drop view command.

DROP VIEW

SYSTEN DEFINED VIEW:-

SYSTEM VIEW

System view divided into two parts

• Information Schema
• Catalog View

INFORMATION SCHEMA:

Information schema used to display physical information about the database.

There is nearly 21 information schema in the system.

Here information schema returns detail of all views used by the customer table.

INFORMATION SCHEMA IN VIEW

CATALOG VIEW:-

Catalog view describes information about the database. These start with sys.

CATALOG VIEW

• The First query provides information to all types of views using a database.
• The second query will provide the information about all the databases defined by the system, including user-defined and system-defined database.

If you are a newbie to database learning — SQL Server recommended is the following must-watch video: -