view in sql server

A view is nothing more than a saved SQL query. A view can also be considered as avirtual table. 

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE coHEndition

SQL Script to create tblEmployee table:
CREATE TABLE tblEmployee
(
 Id int Primary Key,
 Name nvarchar(30),
 Salary int,
 Gender nvarchar(10),
 DepartmentId int
)

When this query is executed, the database engine actually retrieves the data from the underlying base tables, tblEmployees and tblDepartments. The View itself, doesnot store any data by default.

When to Use a View

There are a number of scenarios where we will like to create our own View:

1. To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
2. To control access to rows and columns of data.

View Creation Option

There are two different options for creating a View:

• Schema Binding Option
• Encryption

Schema BindSchema Binding Option

If we create a View with the SCHEMABINDING option, it will lock the tables being referred to by the View and restrict all kinds of changes that may change the table schema (no Alter command). While creating a schema binding View, we can't mention "Select * from tablename" with the query. We have to mention all column names for reference.

For example:

CREATE VIEW DemoSampleView
With SCHEMABINDING
As
SELECT
          EmpID,
          EmpName,
FROM DBO.EmpInfo

And one more thing that we need to remember, while specifying the database name, we have to use Dbo.[DbName]. After creating the View, try to alter the table EmpInfo, we won't be able to do it! This is the power of the SCHEMABINDING option.

Encryption

This option encrypts the definition. This option encrypts the definition of the View. Users will not be able to see the definition of the View after it is created. This is the main advantage of the View where we can make it secure:

CREATE VIEW DemoView
With ENCRYPTION.EmpInfo

same thing we can use in stored procedure :)

Advantages of using views:
1. Views can be used to reduce the complexity of the database schema, for non IT users. The sample view, vWEmployeesByDepartment, hides the complexity of joins. Non-IT users, finds it easy to query the view, rather than writing complex joins.

2. Views can be used as a mechanism to implement row and column level security.
Row Level Security:
For example, I want an end user, to have access only to IT Department employees. If I grant him access to the underlying tblEmployees and tblDepartments tables, he will be able to see, every department employees. To achieve this, I can create a view, which returns only IT Department employees, and grant the user access to the view and not to the underlying table.

View that returns only IT department employees:
Create View vWITDepartment_Employees
as
Select Id, Name, Salary, Gender, DeptName
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
where tblDepartment.DeptName = 'IT'

Column Level Security:
Salary is confidential information and I want to prevent access to that column. To achieve this, we can create a view, which excludes the Salary column, and then grant the end user access to this views, rather than the base tables.

View that returns all columns except Salary column:
Create View vWEmployeesNonConfidentialData
as
Select Id, Name, Gender, DeptName
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId

. Views can be used to present only aggregated data and hide detailed data.

View that returns summarized data, Total number of employees by Department.
Create View vWEmployeesCountByDepartment
as
Select DeptName, COUNT(Id) as TotalEmployees
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
Group By DeptName

To look at view definition - sp_helptext vWName
To modify a view - ALTER VIEW statement 
To Drop a view - DROP VIEW vWName