View

OVERVIEW

•  A virtual entity of DB which looks like a table but does not store the records physically.
•  It is something like window over a table
•  Complex query can be converted into view and can be used in FROM clause
•  A table can be dropped even though view is existing which is using this table. However view created with SCHEMABINDING option won’t allow dropping a table
•  All DML operations are possible on View but again it has some limitations. 
• It shouldn't violate any constraint like foreign key, unique, identity, not null, etc.
• View should point to only 1 table and not multiple tables
• View should not have GROUP by clause or any arithmetic functions 
•  Won’t allow SELECT INTO, ORDER BY
• We can use ORDER BY but it should have TOP operator then only we can use it
•  Index can be created on view but with following conditions. If we do this, view will start storing the data physically
• View should be schemabound
• First we have to create unique clustered index then only we can create other non-clustered indexes
• Creating index on view is sort of alternate solution of partition.

SYNTAX

• Basic Syntax

CREATE VIEW vwName

AS

SELECT QUERY

• With Schemabinding syntax

CREATE VIEW vw

WITH SCHEMABINDING

AS

    SELECT eid, ename , salary, dob

    FROM dbo.Employee

    WHERE Salary > 10000