Saturday , August 18 2018
Home / DataBase / SQL SERVER / View Classification

View Classification

Views:- 

View Definition:-

A View is nothing but an image table or virtual table, which is created for a base table. A view can be created by taking all values from the base table or by taking only selected values from base table. There are two types views available in SQL Server.

  1. Simple Views
  2. Complex Views

View Classification explained given bellow one by one:

 

1.Simple views:- A view which is created basing on the column of a single table is known as a simple table.

2.complex views:-A view i.e created basing on multiple tables columns is a complex view.

Note:-

A view which is created basing on a single table will also be considered as a complex view provided.

If the query contain any of the following.

1.Distinct,Aggregate Function,Group By Clause, Having Clause, calculated column and set operators.

 

Updatable views:- A view which allows manipulation to the data associated with the view we called it as a updatable view.

Note:-By default all the columns of a simple view are updatable.

Non-updatable view:- A view which doesn’t allow manipulations it is a non-updatable view.

Note:-We can update complex views based on multiple tables but not all the columns we can update only columns associated with one table.

With Check option:-

If we want to restrict any DML operations on the view against the where condition we need to add the “with check option” either at the time of creating the view or we can add that option by altering the view.

With Encryption:-

After creating a view we can checkout the statement we have return for creating the viewunder that the text column of syscomment table. But for any security reason if we don’t want to disclosed that information to anyone we can hide it by using ” with Encryption” option either while creating the view or altering the view.

With Schemabinding:-

If a view is created by using the attribute “schemabinding”.We “cannot drop” on “Alter the table columns” on which the view is dependent.

After creating the view we cannot drop the Emp table or other any column that are specified in the view.

when we want to use the schemabinding option it is must to specify each and every column name in the select statement but cannot use ” * “.

While using the schema binding option the table name must be prefix with the owner name is dbo. Which tells the current user is only the owner of the table.

Note:-If required we can use the With Encryption and with schemabinding option at the same time.

 

Print Friendly, PDF & Email

Give us your valuable feedback

comments

About Samba

Check Also

sql server backup script

Backup all databases in SQL Instance with one script

Backup: A copy of SQL Server data that can be used to restore and recover the …