---
title: "Working with Views in Database"  
description: "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 w"  
author: "Anonymous User"  
published: 2011-01-27  
updated: 2020-03-14  
canonical: https://www.mindstick.com/articles/433/working-with-views-in-database  
category: "database"  
tags: ["database"]  
reading_time: 3 minutes  

---

# Working with Views in Database

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   asselect_statementwhere      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  asselect  sid,sname from student  create  view studentView1  asselect  sid,sname,scity from studentwhere sid between 'S0001' and 'S0005'
```

##### Executing view that was early created

select * from **studentView**

![Working with Views in Database](https://www.mindstick.com/mindstickarticle/028e3856-3b2f-454d-95d4-f24cdf73999c/images/5d9e4ee1-dbaa-45d9-89e8-f43725bd7765.png)

select * from **studentView1**

![Working with Views in Database](https://www.mindstick.com/mindstickarticle/028e3856-3b2f-454d-95d4-f24cdf73999c/images/0b667e1d-4ee1-4939-8355-9ce3f9d5b0aa.png)

##### 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  asselect  sid,sname,scity from studentwhere 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_nameas select_statement
```

##### Example of altering view

```
alter  view studentView1  with schemabinding  asselect  sid,sname,scity from studentwhere 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_namewhere     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

---

Original Source: https://www.mindstick.com/articles/433/working-with-views-in-database

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.