17.views

17.1 what is view?

• A view is often seen as a virtual table
• It displays data that you choose, but does not actually hold any data
• Good for security since you can prevent showing extra data
• DML operations just happen on the table

VIEWS: View is a permanent DB object and is considered as virtual table. It displays a result set based on multiple/single table for re-usability and security. It only refers to the permanent objects such as a table, view. Views will not improve performance unless we create an index on top of it.

Rules to follow creating a view

1. Refer a permanent objects
2. TOP for ORDER BY clause
3. VIEW's definition should result in a result set (only DQL statements are possible)
4. COMPUTE BY is not allowed (don't remember, this option is no longer supported by SS)
   5.No Select Into or Hints allowed

USES

1. To restrict data or secure data for certain groups of people
2. When we want to display static info on some web pages. Ex: Calender, Schedule
3. Re-usability: If some query is used frequently, instead of writing it multiple times we can create a VIEW
4. In a report which needs data from multiple tables, instead of matching the data within the report. We can create VIEWS

rename: SP_RENAME

view后面只能是select语句

17.2 Types of Views with Rules

17.2.1 Regular View

• Cannot be made with Compute By
• Order By only with a Top clause
• No Select Into or Hints allowed
• Must reference a permanent object

17.2.2 Schema Bound View / schema binding

• Must use a two part naming convention when specifying a Schema Bound View
• You must list each column needed, no Select * is allowed

advantage： 当table被删除，avoid view变成orphan view

Options
1. WITH ENCRYPTION - Encrypts the code used within the VIEW, so that when SP_HelpText is used to view the code it will not be displayed.
2. WITH CHECK OPTION

问题：：：：不能drop 主table, 当它们 reference的时候， 由于view的存在，不能删除主table

17.3 课上视频代码

/*

VIEW

*/

USE test_db

--CREATE VIEW
CREATE VIEW EMPINFO AS 
SELECT *
FROM AdventureWorks2017.HumanResources.Employee
GO

SELECT * 
FROM EMPINFO

--DROP
DROP VIEW EMPINFO

--ATTENTION: MIDDLENAME MAYBE NULL
CREATE VIEW EMPINFO AS
SELECT FirstName+' '+ISNULL(MiddleName+' ','')+LastName AS 'FULL NAME',
        JobTitle,Gender,MaritalStatus
FROM AdventureWorks2017.HumanResources.Employee
    INNER JOIN AdventureWorks2017.PERSON.PERSON
    ON Employee.BUSINESSENTITYID = PERSON.BUSINESSENTITYID
    
DROP VIEW EMPINFO

SELECT * FROM EMPINFO

CREATE VIEW EMPINFO AS
SELECT Employee.BusinessEntityID,FirstName+' '+ISNULL(MiddleName+' ','')+LastName AS 'FULL NAME',
        JobTitle,Gender,MaritalStatus
FROM AdventureWorks2017.HumanResources.Employee
    INNER JOIN AdventureWorks2017.PERSON.PERSON
    ON Employee.BUSINESSENTITYID = PERSON.BUSINESSENTITYID
    
--JOIN

SELECT * FROM EMPINFO 
    INNER JOIN AdventureWorks2017.PERSON.PERSON
    ON EMPINFO.BusinessEntityID = PERSON.BusinessEntityID


    --SCHEMA BOUND VIEW

--ERROR : NAME HAD TO BE DATABASE

CREATE VIEW SCHEMAEMPINFO WITH SCHEMABINDING AS
SELECT BusinessEntityID,JobTitle,Gender
FROM AdventureWorks2017.HumanResources.Employee

-- 这个可以成功，因为在test_db有这个schema

CREATE VIEW SCHEMAEMPINFO WITH SCHEMABINDING AS
SELECT EMPID,NAME,MANAGEDBY
FROM DBO.EMPLOYEE

在schema view中，不能drop???


SELECT * FROM SCHEMAEMPINFO

EXEC SP_HELPTEXT EMPINFO  --可以查看安全信息，创建表格那些代码

ALTER VIEW EMPINFO WITH ENCRYPTION AS
SELECT Employee.BusinessEntityID,FirstName+' '+ISNULL(MiddleName+' ','')+LastName AS 'FULL NAME',
        JobTitle,Gender,MaritalStatus
FROM AdventureWorks2017.HumanResources.Employee
    INNER JOIN AdventureWorks2017.PERSON.PERSON
    ON Employee.BUSINESSENTITYID = PERSON.BUSINESSENTITYID

    --SHOULD BE NO ONE CAN SEEN WHEN EXEC SP_HELPTEXT 
加了encryption之后，不可以查看创建的那些代码了

课上笔记
--orphan view/object
create view 之后，删除主表，再去查看view 就会报错

create 

--在schema中，不能select * 所有
在schema中，就不能drop table
drop column 也不行，因为这个column在用，除非这个column不在用
create，drop,update

17.4 notes: Restriction

VIEWS allow DML operations, that would in turn impact underlying table. There are some restrictions for DML operations on VIEWS. Following are some restrictions but there are more.
1. In VIEW's definition if all columns from a table are not referenced and one of the columns which are not referenced is NOT NULL then VIEWS doesn't allow INSERTING data.
2. If a view is created using multiple tables and the DML operation is impacting multiple tables it will not be successful.
3. When using GROUP BY clause, DISTINCT, AGREGATE Functions in the query VIEWS doesn't allow DML operations.
4. DELETE and UPDATE should satisfy the filtering condition in the view in order to apply the changes
5. All constraints on the tables should be satisfied
6. When there are SET operators (UNION, INTERSECT, EXCPT) then DML are not allowed.

When a WHERE condition is used within the definition of VIEW. DML operations on VIEWS would inherit those conditions. Ex: If a WHERE ID <=100 condition is used in the VIEW's definition. DML (Update and Delete) cannot be performed on values over 100 for ID. But Inserts are fine.

17.5 WITH SCHEMA BINDING

• When a VIEW is defined as SCHEMA BINDING, columns referenced in the VIEW cannot be altered/dropped(DDL) and tables referenced cannot be dropped. But new columns can be added and columns which are not referenced in the VIEW can be altered/dropped to the underlying table.

• schemabinding view does not allow any DDL operations on columns referred in the view

• ddl operations allowed on columns not referred in schemabinding view

 CREATE TABLE dbo.Licenses
(
 Id    int IDENTITY(1,1) PRIMARY KEY,
 Name  varchar(100),
 RUser nvarchar(128) DEFAULT USER_NAME()
)
GO

CREATE VIEW dbo.rLicenses with schemabinding
AS
SELECT Name
FROM   dbo.Licenses
WHERE  RUser = USER_NAME()
go

INSERT INTO dbo.rLicenses (name) VALUES ('test')

17.6 WITH CHECK OPTION

• When a VIEW is defined with CHECK OPTION user will not be able to insert data which violates the condition defined in the VIEW. Ex: WHERE ID < = 100 WITH CHECK OPTION, user will not be able to insert data with ID value greater than 100.
• It is used to restrict DML operations on the view according to search predicate (WHERE clause) specified creating a view.
• Users cannot perform any DML operations that do not satisfy the conditions in WHERE clause while creating a view.
• WITH CHECK OPTION has to have a WHERE clause.

The following example shows a view named SeattleOnly that references five tables and allows for data modifications to apply only to employees who live in Seattle.

CREATE VIEW dbo.SeattleOnly  
AS  
SELECT p.LastName, p.FirstName, e.JobTitle, a.City, sp.StateProvinceCode  
FROM HumanResources.Employee e  
INNER JOIN Person.Person p  
ON p.BusinessEntityID = e.BusinessEntityID  
    INNER JOIN Person.BusinessEntityAddress bea   
    ON bea.BusinessEntityID = e.BusinessEntityID   
    INNER JOIN Person.Address a   
    ON a.AddressID = bea.AddressID  
    INNER JOIN Person.StateProvince sp   
    ON sp.StateProvinceID = a.StateProvinceID  
WHERE a.City = 'Seattle'  
WITH CHECK OPTION ;  
GO  

17.7 VIEW WITH ENCRYPTION

• Sp_helptext helps you see the syntax of the view created. To prevent the syntax view, we need to encrypt it
• So create a view with encryption.

The following example uses the WITH ENCRYPTION option and shows computed columns, renamed columns, and multiple columns.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-2017

CREATE VIEW Purchasing.PurchaseOrderReject  
WITH ENCRYPTION  
AS  
SELECT PurchaseOrderID, ReceivedQty, RejectedQty,   
    RejectedQty / ReceivedQty AS RejectRatio, DueDate  
FROM Purchasing.PurchaseOrderDetail  
WHERE RejectedQty / ReceivedQty > 0  
AND DueDate > CONVERT(DATETIME,'20010630',101) ;  
GO  

17.8 question: select into can't use in view

We can't use select into in view but we can use views in select into clause

17.9 dirrerence between regular view and complex view?

complex view is the schema view/encryption view/with check view

1. Select * allowed in Regular.. Not allowed in schemabinding
2. Can create a view from another db in regular view whereas schemabinding requires to be created from the db because of 2 part naming convention
3. Regular view allows DDL operations on columns in underlying tables, wheres schemabinding does not

how update complex view?
use triggers

benefit of SCHEMABINDING
https://www.mssqltips.com/sqlservertip/4673/benefits-of-schemabinding-in-sql-server/

17.10 refresh regular view

When a VIEW (Non Sch Binding) uses SELECT * in its definition and there is a structural change (add a column/ drop a column)
in the underlying table, VIEW needs to be refreshed
Refreshing Options

1. Use ALTER VIEW statement
2. Use SP_REFRESHVIEW system procedure

17.11 当存在view的时候，原table

Tables	Alter	Drop
column ref in view	no	no
column not ref on view	yes	yes
drop ref table	no	no

17.12 例子

当我们有四个不同地点的同一个部门要建立数据库
两种办法：

• view
• create different schema

17.13 compare

compare	view	Schema-binding View
DDL	yes	yes
SELECT INTO	no	no
JOIN	yes	yes
DML	yes(restriction)	yes(restriction)
DDL	yes	yes
indentity	no	yes
change base table	yes	yes
drop	yes	yes
*	yes	no
insert into(当创造view的时候）	no	no

17.14 alter view

alter view v_name
select a,b, cast( c as int)
from t_name