VIEWS (Database Engine)

• A view can be thought of as either a virtual table or a stored query.

Unless a view is indexed, its data is not stored in the database as a distinct object.

What is stored in the database is a SELECT statement. The result set of

the SELECT statement forms the virtual table returned by the view. A user

can use this virtual table by referencing the view name in Transact-SQL

statements the same way a table is referenced.

• Like a real table, a view consists of a set of named columns and

rows of data.

• A view acts as a filter on the underlying tables referenced in the view.

The query that defines the view can be from one or more tables or from

other views in the current or other databases.

The SELECT clauses in a view definition cannot include the following:

• COMPUTE or COMPUTE BY clauses
• An ORDER BY clause, unless there is also a TOP clause in the

select list of the SELECT statement

• The INTO keyword
• The OPTION clause
• A reference to a temporary table or a table variable.
• Functions and multiple SELECT statements separated by UNION or

UNION ALL can be used in select_statement.

CHECK OPTION

Forces all data modification statements executed against the view to

follow the criteria set within select_statement. When a row is modified

through a view, the WITH CHECK OPTION makes sure the data

remains visible through the view after the modification is committed.

ENCRYPTION

Encrypts the entries in sys.syscomments that contain the text of the

CREATE VIEW statement. Using WITH ENCRYPTION prevents

the view from being published as part of SQL Server replication

SCHEMABINDING

Binds the view to the schema of the underlying table or tables. When

SCHEMABINDING is specified, the base table or tables cannot be

modified in a way that would affect the view definition. The view

definition itself must first be modified or dropped to remove

dependencies on the table that is to be modified. When you use

SCHEMABINDING, the select_statement must include the two-part

names (schema.object) of tables, views, or user-defined functions

that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the

SCHEMABINDING clause cannot be dropped unless that view is

dropped or changed so that it no longer has schema binding.

Otherwise, the Database Engine raises an error.

Also, executing ALTER TABLE statements on tables that

participate in views that have schema binding fail when these

affect the view definition. SCHEMABINDING cannot be specified

if the view contains alias data type columns.

VIEW_METADATA

Specifies that the instance of SQL Server will return to the DB-Library,

ODBC, and OLE DB APIs the metadata information about the view,

instead of the base table or tables, when browse-mode metadata is

being requested for a query that references the view.

For views created with VIEW_METADATA, the browse-mode metadata

returns the view name and not the base table names when it describes

columns from the view in the result set.

Types of Views:

1) Standard Views

2) Indexed Views

3) Partitioned Views

1) Standard Views

Combining data from one or more tables through a standard view lets you

satisfy most of the benefits of using views. These include focusing on specific

data and simplifying data manipulation.

2) Indexed Views

An indexed view is a view that has been materialized. This means it has been

computed and stored. You index a view by creating a unique clustered index

on it. Indexed views dramatically improve the performance of some types of queries.

Indexed views work best for queries that aggregate many rows. They are not

well-suited for underlying data sets that are frequently updated.

3) Partitioned Views

A partitioned view joins horizontally partitioned data from a set of member tables

across one or more servers. This makes the data appear as if from one table.

A view that joins member tables on the same instance of SQL Server is a

local partitioned view.

EX: 1 Simple CREATE VIEW

Create View EmpJoinDate as

Select E.Empid,E.EmpName,E.Designation,I.EmpInfoId,

Convert(varchar,I.DateOfJoin,104) as DOJ,I.Salary

From Employees E Inner Join EmployeeInfo I

ON E.empid=I.EmpId

MODIFY VIEW:

Alter View EmpJoinDate as

Select E.Empid,E.EmpName,E.Designation,I.EmpInfoId,

Convert(varchar,I.DateOfJoin,104) as DOJ,I.Salary

From Employees E Inner Join EmployeeInfo I

ON E.empid=I.EmpId and I.salary <> 15000

and I.salary <>18000

Select * from EmpJoinDate

TO DROP VIEW:

Drop view EmpJoinDate

EX: 2 Using WITH ENCRYPTION

Create View EmpSalary WITH ENCRYPTION as

Select E.Empid,E.EmpName,E.Designation,I.Addrees,

Convert(varchar,I.DateOfJoin,104) as DOJ,I.Salary

From Employees E Join EmployeeInfo I

ON E.empid=I.EmpId and

datepart(year,I.DateOfJoin)<

datepart(year,getdate())

Select * from EmpSalary

EX: 3 Using WITH CHECK OPTION

create view ChennaiEmp as

Select E.Empid,E.EmpName,E.Designation,I.Addrees,

Convert(varchar,I.DateOfJoin,104) as DOJ,I.Salary

From Employees E Inner Join EmployeeInfo I

ON E.empid=I.EmpId where I.Addrees ='Chennai'

with check option

select * from ChennaiEmp

EX: 4 using partitioned data Schema Binding

Create VIEW all_supplier_view

WITH SCHEMABINDING

AS

SELECT supplyID, supplier FROM dbo.SUPPLY1

UNION ALL

SELECT supplyID, supplier FROM dbo.SUPPLY2

UNION ALL

SELECT supplyID, supplier FROM dbo.SUPPLY3

UNION ALL

SELECT supplyID, supplier FROM dbo.SUPPLY4

select * from all_supplier_view

drop view all_supplier_view

When we try to drop a table which is defined in

schema binded view than it will not allow us to

drop the table.