Views in SQL Server with Example-Part2

Hello friends, this article is in continuation of my last article related to Views. In this article we will learn about few more feature of using Views.

Reduction of complexity: - We create views for big and complex queries those are frequently used. By creating views we do not require to write those queries again and again, all we need to do is just put the name of view.

Few facts about Views: - Below are the few facts about Views.

We cannot use Order by clause in Views, an alternative way of using Order by clause is, by including Top clause in views

If we try to create a view with order by clause, we will be getting a below error message.

Msg 1033, Level 15, State 1, Procedure vw_employee, Line 3

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

To overcome this problem, we can create the view using below SQL Script.

Create view vw_employee

as

select top 100 percent Eid,Name from Employee order by Eid

Schemabinding Option :- While creating a view, we can enable schemabinding option of the view which prevents the user to drop the Base table and also prevents the user to alter the schema of the columns used in the View definition.

Example of Schemabinding :- We use below syntax to enable “schemabinding” option.

Create view vw_employee

with Schemabinding

as

select  Eid,Name from dbo.Employee

Lets try to drop the “Employee” table after enabling the schemabinding option.

Encryption Option:- We can encrypt the definition of the View by enabling the Encryption option. After enabling the encryption option we will not be able to view the definition of the View. Hence, we need be careful while enabling this option, as there is no direct method to decrypt the View definition.

Note:- Before applying Encryption in a View, we should take a copy of the View definition and save it to our central location. So that, in case of any change View can be modified.

Example of Encryption:- We use below syntax to enable Encryption option.

Alter view vw_employee

with Encryption

as

select  Eid,Name from dbo.Employee

Lets try to see the definition of the encrypted View using sp_helptext stored procedure.

sp_helptext vw_employee

Check option:- Enabling the check option restrict the user to insert a row into the view which is not satisfying the where clause statement. To understand this lets apply check option in the View.

Syntax of Check Option:-

Create view vw_employee

as

select  Eid,Name from dbo.Employee where Eid <104

with check option

In the above definition of View, we have put where clause which says that Eid<4, It means if a user try to insert a row into Base table (“Employee”) through the view which is havig Eid>=4, that will not be allowed. Let’s try to insert a row with Eid 105.

We are getting an error message while inserting a row which is having Eid value greater than 104.

Schema Changes:- If we change the Schema of the base(which is being reference) table , it is not reflected in the View. To prove this I want to show an example.

I have created a View using below SQL Statement.

create view vw_Employee

as

select  * from Employee

Now I am going to add one more column(Bonus) into Employee table using below SQL statement.

alter table employee add Bonus int default 800 with values

added the column “Bonus” with default value 800, Now I run below two SQL statements.

select  * from Employee

select * from vw_Employee

lets see the results, In normal scenario, both queries should return same output.But that is not the case. Schema change is not reflected in the View.

Hence, In the View, “Bonus” column is not getting appear.

Solution:-There are two solutions to resolve this problem.

• Use Sp_refreshview view to update the Schema in the View.

           Sp_refreshview vw_Employee

• Alter the View

          create view vw_Employee

          as

          select  * from Employee

So this was all about Views and its characteristics and features. I hope you will find this article useful for you. In my forthcoming articles we will see what are Updatable Views and Non Updatable Views.