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. It is used to implement the security mechanism in the SQL Server.

NOTE:- we can use View in join also if our join become more complex then we add complex query in view and then  we use view in join.

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 a Employee_Details table for the basic info of an employee.

CREATE TABLE [dbo].[Student_Details]

( 

    [Id] [int] IDENTITY(1,1) NOT NULL, 

    [Name] [nvarchar](50) NOT NULL, 

    [Address] [nvarchar](50) NOT NULL, 

    CONSTRAINT [PK_Student_Details] PRIMARY KEY CLUSTERED  

   ( [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 Student_Details Values('Pankaj','Alwar') 

Insert Into Student_Details Values('Rahul','Jaipur') 

Insert Into Student_Details Values('Rajan','Delhi') 

Insert Into Student_Details Values('Sandeep','Alwar') 

Insert Into Student_Details Values('Sanjeev','Jaipur') 

Insert Into Student_Details Values('Narendra','Alwar') 

Insert Into Student_Details Values('Neeraj','Delhi') 

Insert Into Student_Details Values('Div','Jaipur') 

Insert Into Student_Details Values('Tanuj','Alwar') 

Insert Into Student_Details Values('Nitin','Jaipur') 

Now the table Student_Details will look as in the following.

select * from Student_Details

We create another table named Student_Contact.

CREATE TABLE [dbo].[Student_Contact]

( 

    [Id] [int] NOT NULL, 

    [MobileNo] [nvarchar](50) NOT NULL 

) ON [PRIMARY] 

 

GO 

 

ALTER TABLE [dbo].[Student_Contact]  WITH CHECK ADD

CONSTRAINT [FK_Student_Contact_Student_Details]

FOREIGN KEY([Id]) 

REFERENCES [dbo].[Student_Details] ([Id]) 

GO 

ALTER TABLE [dbo].[Student_Contact]

CHECK CONSTRAINT [FK_Student_Contact_Student_Details] 

GO 

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

Insert Into Student_Contact Values(1,'9813220191') 

Insert Into Student_Contact  Values(2,'9813220192') 

Insert Into Student_Contact  Values(3,'9813220193') 

Insert Into Student_Contact  Values(4,'9813220194') 

Insert Into Student_Contact  Values(5,'9813220195') 

Insert Into Student_Contact  Values(6,'9813220196') 

Insert Into Student_Contact  Values(7,'9813220197') 

Insert Into Student_Contact  Values(8,'9813220198') 

Insert Into Student_Contact  Values(9,'9813220199') 

Insert Into Student_Contact  Values(10,'9813220135') 

Now the table Student _Contact will look as in the following:

select * from Student_Contact  

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

Syntax of Creating  VIEW in SQL Server

CREATE VIEW view_name

AS 

SELECT columns 

FROM tables 

WHERE conditions; 

Let us create some views.

We can select all columns or few column of a table. The following example demonstrates that:

Create View vw_StudentDetail 

as 

select * from Student_Details 

or we select column name

Create View vw_StudentDetail 

AS

select Id,Name,[Address] from Student_Details 

We can select columns from a table with specific conditions.

Create View vw_StudentDetail 

AS

select Id,Name,[Address] from Student_Details 

where Id>3

We can create a view that will hold the columns of different tables.

Create View vw_StudentDetail 

AS

select A.Name,A.[Address],B.MobileNo

from Student_Details A 

JOIN Student_Contact B on A.Id=B.Id

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 vw_StudentDetail

OR

select Name,Address from vw_StudentDetail

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

DROP VIEW vw_StudentDetail

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

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 vw_StudentDetail

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 vw_StudentDetail 

AS

select A.Name,A.[Address],B.MobileNo,

A.Id

from Student_Details A 

JOIN Student_Contact B on A.Id=B.Id

WHERE A.Id>4 --add condition after created view

Refreshing a View in SQL Server

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

Create View vw_StudentDetail1 

as 

Select * from Student_Details

Now add a column in Employee_Details table 

Alter Table Student_Details Add RollNumber int

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

Select * from Student_Details 

Select * from vw_StudentDetail1 

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 into 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 vw_StudentDetail1   

Select * from Student_Details   

Select * from vw_StudentDetail1

Output-

SchemaBinding a VIEW

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

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 vw_StudentDetail2 

WITH SCHEMABINDING

as 

Select Name,Address from Student_Details 

NOTE:- If we use * then we get following error

Msg 1054, Level 15, State 6, Procedure vw_StudentDetail2, Line 13

Syntax '*' is not allowed in schema-bound objects.

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

Output-

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.

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.

DML Query In View

In a view we can implement many types of DML query 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 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.

     

NOTE:- If our view have group by,distinct then we get following error

Cannot update the view or function 'vw_StudentDetail'  because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.

           

System Define Views:-

 SQL Server also contains various predefined databases like Tempdb, Master, temp. Each database has their 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].

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