# 一、Designing and Implementing Views

## (一) What Is a View?
1. 建立 View 的當下，會存入當下的物件()
2. exec sp_refreshview 即可更新 View 的定義
)

USE tempdb;
DROP TABLE IF EXISTS t;
CREATE TABLE t(c1 INT);
GO

CREATE VIEW vw
AS
     SELECT *
     FROM t;
GO

ALTER TABLE t
ADD c2 INT; 

SELECT *
FROM vw;

exec sp_refreshview 'vw'

3. View 存語法不存資料

## (二) Types of Views

### User-defined views:

* Partitioned views
https://dotblogs.com.tw/killysss/2012/09/11/74717

### System views:

select * from sysprocesses


select * from sys.dm_exec_sessions
select * from sys.dm_exec_connections
select * from sys.dm_exec_requests
select * from sys.dm_tran_locks

## (三) Advantages of Views
簡化 將複雜結構藏起來
架構 安全
重新命名列名

1. Create or Alter (2016開始提供) => view、sp、function

# 二、Designing and Implementing Stored Procedures

## Lesson 4: Controlling Execution Context
### Controlling Executing Context

Select HASHBYTES('SHA2_256','abraham')
Select HASHBYTES('SHA2_256','rog')
Select HASHBYTES('SHA2_256','engels')
Select HASHBYTES('SHA2_256','light')

USE tempdb;
DROP user u;
CREATE user u WITHOUT LOGIN;
CREATE OR ALTER PROC sp
WITH EXEC AS 'u'
AS
     SELECT user, 
            SUSER_SNAME();
GO
EXEC sp;

# 三、Designing and Implementing User-Defined Functions

select fn1(col) 
from fn2() 
where fn3(col)= fn4(input)


select top (10)* from t
select * from t where c1 = 1 or c1 = -1

CREATE TABLE dbo.t(c1 INT);
CREATE FUNCTION fn
(
    @c1 INT
)
RETURNS INT
AS
     BEGIN
         RETURN @c1;
     END;
GO
SELECT dbo.fn(c1)
FROM t;

create table t3(c1 nvarchar(10))
alter table t3 add c2 as substring(c1,2,1) -- Deterministic functions

alter table t3 add c3 as rand() -- Nondeterministic

create index idx on t3(c2)
select * from t3 where substring(c1,2,1) = 'a'

## Inline Table-Valued Functions

CREATE OR ALTER FUNCTION fn3(@c1 INT) 
RETURNS TABLE AS RETURN
            SELECT *
            FROM t
            WHERE c1 > @c1;
GO

DROP TABLE IF EXISTS t, t2;
CREATE TABLE t(c1 INT);
CREATE TABLE t2(c21 INT);
GO
CREATE OR ALTER VIEW vw
AS SELECT *
   FROM t
        JOIN t2 ON t.c1 = t2.c21;
GO
INSERT INTO vw
VALUES
(1, 1);
GO

CREATE TRIGGER trg ON vw
INSTEAD OF INSERT
AS
     INSERT INTO t(c1)
            SELECT c1
            FROM inserted;
     INSERT INTO t2(c21)
            SELECT c21
            FROM inserted;
GO
SELECT *
FROM t;
SELECT *
FROM t2;

row-level security
role based security