Views in SQL Server Database - DotNet Brother

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

1) select * from studentView

2)  select * from studentView1

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.

1)      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'

2)      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