Views -- About view and limitations of view

About view and limitations of view --

A View is a virtual table whose contents are defined by query. The defined query can be form of one or more column of table or on the current view of current or other database.

-          Simple view – Whenever creating the view on a single table is called as simple view.

-          Complex view – whenever creating the view with multiple table or other view related to same database or other database called as complex view

-- Create the view

CREATE VIEW hiredate_view

AS

SELECT c.FirstName, c.LastName, e.EmployeeID, e.HireDate

FROM HumanResources.Employee e

JOIN Person.Contact c

ON e.ContactID = c.ContactID ;

-- Drop the view

IF OBJECT_ID ('hiredate_view', 'view') IS NOT NULL

DROP VIEW hiredate_view ;

Check Option –

-          If any row is modified through view Check option ensure that the after committed modified data should be visible in the view.

-          Can’t use the Check option if any where top clause have used in the select statement.

ENCRYPTION –

-          This functionality encrypt the view .It will not allow to know the business behind the view .

SCHEMABINDING –

-          This clause not allow to user to modify the base relational tables have used inside the view.

-          It not allow to drop the base table if view is existing in the database.

Limitation of views –

-          Does not create table

-          Does not refer any temporary table

-          Does not contain compute or compute by column

-          Can’t use the select into  clause inside the views

-          Can’t use the  trigger and index inside the views

-          Can’t use the  Order by clause in the select statement

-          COMPUTE or COMPUTE BY clauses

-          An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement

-          Can’t use the OPTION clause

-          Can’t a reference to a temporary table or a table variable.

Updateable View –

-          Through view base table data can be possible to modify if that view contains only one base table.

-          If view contains multiple tables with the help of Instead of trigger can be modify the data.

-          Limitations -  view should not contains any aggregate functions, any  group by or having or order by clause and cross join, Intersect