Powered by Blogger.

Saturday, February 15, 2014

View in sql server 2008

In this post we will discuss about what are view in sql server 2008.

You can also check my previous posts on:

Difference between Abstract Class and Interface in C#.Net,

Left outer join and Right outer join in SQL Server

- Difference between primary key and foreign key in SQL server.

- A view in sql server is like a virtual table. It can have rows and columns which are fields of one or more database table.

- Views are stored in the database. A view can have a maximum of 1,024 columns.

- It provides security mechanism to sql server. Suppose you do not want to show all the columns of a table to some users then you can make a view based on the table with limited columns that you want to share.

You can also use all conditions like where clause, order by or group by while creating view in sql server.

You can create a view like below:

 Create View vEmployeeDetails As
        Select ID, Name, Location from Employees

You can also use any condion if you want like

Create View vEmployeeDetails As
        Select ID, Name, Location from Employees where ID>100

Through management studio it is very easy to create view.

- First open the database in Sql server 2008 management studio.

Then navigate to the Views folder, Right click on the Views folder and then click on New view... as shown in the figure below:

Then it will open the window to select tables and select columns.

Some points about view:
- The name of the view should not be same as base table name.

- A view can be created only if there is a SELECT permission on its base table.

- A trigger or an index cannot be defined on a view.