SQL

SQL Tutorial

SQL Server Views

A view is logical view of data from a pre-defined sql query.
Create View:
Create view v1
as
Select ProductID from Product

Execute View:
Select * from v1

Output:
1
2
3
4

Update View:
Update v1
Set ProductID=5
Where ProductID=4

Execute View:
Select * from v1

Output:
1
2
3
5

Drop View:
Drop view v1

With Schemabinding: binds the view to the object it refers. The object cannot be modified the way it affects the definition of view

Create view v2
With Schemabinding
As
Select ProductID from [dbo].[Product]

Drop table Product
Error: Cannot DROP TABLE ‘Product’ because it is being referenced by object ‘v2’

Drop view v2

Indexed view (Materialized view):
An indexed view is logical view of data from a pre-defined sql query that stores the result of the query in disk or table. It can be indexed and so has better performance

Create View v3
WITH SCHEMABINDING AS
Select ProductID from [dbo].[Product]
Go

Create Unique Clustered index UCI_ID on dbo.v3(ProductID)

Select * from v3

Output:
1
2
3
4