------------
--- VIEW ---
------------
Use Northwind
Go
----------------------------------------
--- Long and Complax Select Statment ---
----------------------------------------
SELECT o.OrderID, s.CompanyName as SuppliersCompanyName, c.CompanyName, p.ProductName,
  od.Quantity * od.UnitPrice AS PRICE
  FROM Orders o JOIN Shippers s
  ON o.ShipVia = s.ShipperID 
  JOIN Customers c
  ON o.CustomerID = c.CustomerID
  JOIN [Order Details] od
  ON o.OrderID = od.OrderID
  JOIN Products p
  ON od.ProductID = p.ProductID
  WHERE od.Quantity * od.UnitPrice > 999 

-------------------
--- Create View ---
-------------------
CREATE VIEW VIEW_TMP AS
SELECT o.OrderID, s.CompanyName as SuppliersCompanyName, c.CompanyName, p.ProductName,
  od.Quantity * od.UnitPrice AS PRICE
  FROM Orders o JOIN Shippers s
  ON o.ShipVia = s.ShipperID 
  JOIN Customers c
  ON o.CustomerID = c.CustomerID
  JOIN [Order Details] od
  ON o.OrderID = od.OrderID
  JOIN Products p
  ON od.ProductID = p.ProductID
  WHERE od.Quantity * od.UnitPrice > 999 

Select * from VIEW_TMP

----------------------------------------------
---Create Table---
Create Table TblTmp1
(
Col1 int,
Col2 Nvarchar(20)
)

Insert Into TblTmp1 Values(1, 'A');

select * from TblTmp1

---Create View---
Create View ViewTmp1
As
select * from TblTmp1

select * from ViewTmp1

--Insert Into View---
Insert Into ViewTmp1 Values(2, 'B')

---Select From The View And From The Table---
Select * from TblTmp1
Select * from ViewTmp1

---Drop Column From The Table---
Alter Table TblTmp1
Drop Column Col2;

---Run Select From The View And Table---
Select * from TblTmp1
Select * from ViewTmp1

---Drop The Table--- 
Drop Table TblTmp1

---Drop The View--- 
Drop View ViewTmp1

---------------------
--- SchemaBinding ---
---------------------

---Create View With SchemaBinding---
Create Table TblTmp1
(
Col1 int,
Col2 Nvarchar(20)
)

Insert Into TblTmp1 Values(1, 'A');

select * from TblTmp1

---Create View Attempt 1---
Create View ViewTmp1
With SchemaBinding
As
select * from TblTmp1

---Create View Attempt 2---
Create View ViewTmp1
With SchemaBinding
As
select col1, Col2 from TblTmp1

---Create View Attempt 3---
Create View dbo.ViewTmp1
With SchemaBinding
As
select col1, Col2 from dbo.TblTmp1


---Run Select From The View And Table---
Select * from TblTmp1
Select * from ViewTmp1

---Insert Into View---
Insert Into ViewTmp1 Values(2, 'B')

---Run Select From The View And Table---
Select * from TblTmp1
Select * from ViewTmp1

---Drop Column From The Table---
alter table TblTmp1
drop column Col2

---Drop Table---
drop table TblTmp1
drop view ViewTmp4

-- Once you create a view with schemabinding - 
-- You cannot change the underlying tables in a way that would break the view.  
-- Examples of this would be removing columns or dropping tables that are specified in the view.

---------------------------
--- DML On Complex View ---
---------------------------

Drop View VW_ORDERS_BY_EMP

select COUNT(case when year(o.OrderDate) = '1996' then o.OrderDate end) as OrderCnt, 
e.FirstName +' '+ e.LastName as FullName
from Orders o join Employees e
on o.EmployeeID = e.EmployeeID
group by e.FirstName +' '+ e.LastName

Create View VW_ORDERS_BY_EMP
as 
select COUNT(case when year(o.OrderDate) = '1996' then o.OrderDate end) as OrderCnt, 
e.FirstName +' '+ e.LastName as FullName
from Orders o join Employees e
on o.EmployeeID = e.EmployeeID
group by e.FirstName +' '+ e.LastName

select * from VW_ORDERS_BY_EMP

-- Try To Insert New Row
Insert into VW_ORDERS_BY_EMP Values(100, 'Nick')

-- Try To Update An Existing Row
Update VW_ORDERS_BY_EMP
set FullName = 'Andrew Full'
where OrderCnt = 16

------------------
--- Encryption ---
------------------
select * from sys.objects where name like '%vw%'
exec sp_helptext VW_ORDERS_BY_EMP
-- Can Also Run With Only - sp_helptext VW_ORDERS_BY_EMP (Without exec)

Alter View VW_ORDERS_BY_EMP
With encryption
as 
select COUNT(case when year(o.OrderDate) = '1996' then o.OrderDate end) as OrderCnt, 
e.FirstName +' '+ e.LastName as FullName
from Orders o join Employees e
on o.EmployeeID = e.EmployeeID
group by e.FirstName +' '+ e.LastName

select * from VW_ORDERS_BY_EMP
sp_helptext VW_ORDERS_BY_EMP

-- In Order To Drop the Encryption
-- Alter The View With All The Select Statement

Alter View VW_ORDERS_BY_EMP
as 
select COUNT(case when year(o.OrderDate) = '1996' then o.OrderDate end) as OrderCnt, 
e.FirstName +' '+ e.LastName as FullName
from Orders o join Employees e
on o.EmployeeID = e.EmployeeID
group by e.FirstName +' '+ e.LastName

sp_helptext VW_ORDERS_BY_EMP

--------------------
--- Indexed View ---
--------------------

Create View vw_order_emp
AS
select o.orderid, o.OrderDate,
e.FirstName +' '+ e.LastName as FullName
from Orders o join Employees e
on o.EmployeeID = e.EmployeeID

select * from vw_order_emp

Create Unique Clustered Index vw_ix_ui_cl
On vw_order_emp (OrderDate)

-- Altering The View With Schemabinding
Alter View vw_order_emp
With Schemabinding
AS
select o.orderid, o.OrderDate,
e.FirstName +' '+ e.LastName as FullName
from dbo.Orders o join dbo.Employees e
on o.EmployeeID = e.EmployeeID

Create Unique Clustered Index vw_ix_ui_cl
On vw_order_emp (orderid)

Create NonClustered Index vw_ix_nc
On vw_order_emp (orderdate)

-- The First Index Always Will Be Unique Clustered Index.
-- The Index Transform the View to A Physical Object.
-- The Index Is Updating By The Source Tables.
-- A Indexed view will Cope much better with changes In the Source Tables.

-----------------
--- Count_Big ---
-----------------
-- When A View Is Created With Group Functions & Group By We Should Use Count_Big
-- The Count_Big Is Assisting The Index By Counting Rows For Each Group.

Drop View VW_ORDERS_BY_EMP

Create View VW_ORDERS_BY_EMP
With Schemabinding
as 
select COUNT_BIG(*) as OrderCnt, 
e.FirstName +' '+ e.LastName as FullName
from dbo.Orders o join dbo.Employees e
on o.EmployeeID = e.EmployeeID
group by e.FirstName +' '+ e.LastName

Create Unique Clustered Index vw_ix_ui_cl1
On VW_ORDERS_BY_EMP (FullName) 

select * from VW_ORDERS_BY_EMP
where OrderCnt between 1 and 70

-------------------------
--- View - With Check ---
-------------------------

Use Northwind
Go

-- Definition - Disable the option to Update rows out of the view's context:

-- 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

--Create Table From Employees Table
select * into emps from Employees

CREATE VIEW LondonEmps
AS
SELECT * FROM emps
WHERE city = 'London'

select * from LondonEmps;

UPDATE LondonEmps
SET city = 'Tel-Aviv'
WHERE EmployeeID = 6

-- With Check...
ALTER VIEW LondonEmps
AS
SELECT * FROM emps
WHERE City IN ('London')
WITH CHECK OPTION

select * from LondonEmps;

-- Will allow..?
UPDATE LondonEmps
SET city = 'Tel-Aviv'
WHERE EmployeeID = 5;

UPDATE LondonEmps
SET FirstName = 'NewName'
WHERE city = 'London';

select * from emps;

Drop Table emps;
Drop View LondonEmps;