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
- Refer a permanent objects
- TOP for ORDER BY clause
- VIEW's definition should result in a result set (only DQL statements are possible)
- COMPUTE BY is not allowed (don't remember, this option is no longer supported by SS)
5.No Select Into or Hints allowed
USES
- To restrict data or secure data for certain groups of people
- When we want to display static info on some web pages. Ex: Calender, Schedule
- Re-usability: If some query is used frequently, instead of writing it multiple times we can create a VIEW
- 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
- Select * allowed in Regular.. Not allowed in schemabinding
- 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
- 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
- Use ALTER VIEW statement
- 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
网友评论