Whenever you want to provide different types of restriction to different user such as different-2 users can see different records in same table then we can use the concept of views. Views can help in simplifying query execution when the query involves retrieving data from multiple tables by applying join.

“A view is a virtual table, which provides access to a subset of columns from one or more tables. It is a query stored as an object in the database, which does not have its own data.”

Views ensure data security by applying certain types of restriction on user table such as

  •    Specific rows of a table by using where clause.
  •    Specific columns of a table base on certain condition.
  •    Rows filled by using joins.
  •    Subset of another view or a subset of views and tables.
Syntax for creating View
create  view view_name   as
select_statement
where
     create is a keyword which is used to create a database object
     view is a keyword which indicates that object that is created is a view
                view_name  is any valid name to your view as is a keyword select_statement is a select statement which is valid combination of join, subquery etc.
Some example which demonstrate the use of view
create  view studentView  as
select sid,sname from student
 
create view studentView1 as
select sid,sname,scity from student
where sid between 'S0001' and 'S0005'
Executing view that was early created

 select * from studentView

Working with Views in Database

select * from studentView1

Working with Views in Database

Creating an index view by using create index statement

By default when we create any view then index is not created on that view. You can create index on the view as well as you create index on table by using create index statement.

Example to demonstrate creating index on view

Before we can create index on view be have to bound view to the schema at the time of creation.

 Binding view to the schema by using following statement
alter  view studentView1  with schemabinding  as
select sid,sname,scity from student
where sid between 'S0001' and 'S0005'
  Creating index on view
create unique clustered index sid_student  on studentView1(sid)
Altering Views

When you want to change the structure of the underlying tables such as adding new columns then we use alter commands. You can modify view without affecting its dependent objects.

Syntax for modifying view by using alter command
alter  view view_name
as select_statement
Example of altering view
alter  view studentView1  with schemabinding  as
select sid,sname,scity from student
where sid between 'S0001' and 'S0005'
Deleting Views from database

The syntax of the drop view statement is:

 drop view view_name

Example which demonstrate use of droping view

drop view studentView1

Renaming Views

At times for security permission we need to rename an existing view. For renaming an existing view use sp_rename system stored procedure.

Syntax for renaming views
sp_rename old_view_name, new_view_name
where
     old_view_name represents name of the old view that you want to rename
     new_view_name represents name of new view that you want
Example of renaming views

sp_rename studentView1, stdView1

While renaming view mention following things
  •    The view should be in current database
  •    The new name for the view must follow the rules for identifier
  •    No any other view stored in the database with new name
  •    The view can only be renamed by its owner

 

  Modified On Nov-16-2017 02:28:04 PM

Leave Comment