- 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.
- Basic Syntax
CREATE VIEW vwName
- With Schemabinding syntax
CREATE VIEW vw
SELECT eid, ename , salary, dob
WHERE Salary > 10000