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.
CREATE VIEW:-
Here we can create and retrieve a view that holds multiple tables.
RENAME VIEW:-
We can rename the view by using Sp_Rename System stored procedure.
Syntax:- Sp_Rename OldViewName , NewViewName
Example:-
If you want to check the rename apply on view then goto Database and refresh Views then expand it.
GETTING INFORMATION ABOUT THE VIEW:-
Here we retrieve all information about view by using Sp_Helptext System stored procedure.
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.
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.
Here address column added successfully in the table but it doesn’t display in the view because the schema of the view already defined.
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.
SCHEMABINDING IN VIEW:-
Schema binding uses when you want to prevent changes in the table. Here we create schema binding.
Now, If we try to change the datatype in the table then display the warning message on your screen.
ENCRYPT VIEW:-
We can encrypt the definition of the view help of WITH Encryption.
DROP VIEW:-
We can use the drop view by using the drop view command.
SYSTEN DEFINED 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.
CATALOG VIEW:-
Catalog view describes information about the database. These start with sys.
- 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: -
