SQL Server 2000 세미나 View, SP &Trigger

SQL Server 2000 세미나View, SP &Trigger 주최: Microsoft 강사: 정원혁

차 례 • 뷰 • 뷰를 사용하는 목적 • 만들기 /고치기 /지우기 / 암호화 • 뷰를 통한 데이터 변경 • with check option • 시스템 뷰 • 색인된 뷰

차 례 • 프로시저 • 일반 SQL 문장과 프로시져의 차이 • 컴파일이 성능에 미치는 영향 • 매개 변수 사용 • return 문 • 컴파일 해야만 하는 경우

차 례 • 트리거 • 수행순서 지정 • instead of 트리거 • 트랜잭션과의 관계 • 숨어있는 두 테이블 • 다중 행의 처리 • 중첩 트리거 • 재귀 트리거 • 성능에 미치는 영향

뷰 를 사용하는 목적 • 편의성 • 보안 • 네트웍 트래픽

만들기/ 고치기/ 지우기/ 암호화 • CREATE VIEW [owner.]VIEW_name • (column_name [, column_name]...)] • [WITH ENCRYPTION] • AS select_statement • [WITH CHECK OPTION]

뷰를 통한 데이터 변경/ 고려사항 • 입력되지 않는 컬럼: default , null, identity, timestamp • 계산된 컬럼/ 파생된 컬럼들 • 한번에 한 테이블만 수정할 수 있다 • Order by

with check option • with check option • 시스템 뷰 SELECT * FROM sysobjects WHERE type = 'V' SELECT * FROM information_schema.views

색인 (Indexed view) CREATE TABLE tempdb..a ( c1 int , c2 int , c3 as (c1 * c2) ) insert tempdb..a values (2, 3) go create index idx on tempdb..a (c3)

색인된 뷰 use northwind go CREATE VIEW vTotalProduct WITH SCHEMABINDING AS SELECT productid, sum( unitPrice * Quantity) as Total , sum(quantity) as totQty , COUNT_BIG(*) as count FROM dbo.[order details] GROUP BY productid Go CREATE UNIQUE CLUSTERED INDEX idx ON vTotalProduct (productid)

파티션드 뷰 CREATE TABLE SUPPLY1 ( supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150), supplier CHAR(50) ) ….. CREATE TABLE SUPPLY4 ( supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600), supplier CHAR(50) )

파티션드 뷰 CREATE VIEW all_supplier_view AS SELECT * FROM SUPPLY1 UNION ALL SELECT * FROM SUPPLY2 UNION ALL SELECT * FROM SUPPLY3 UNION ALL SELECT * FROM SUPPLY4

스토어드 프로시저

만들기/ 고치기 / 지우기 CREATE PROC[EDURE] procedure_name [;number] [ {@parameter data_type} [VARYING] [= default] [OUTPUT] ] [,...n] [WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [FOR REPLICATION] AS sql_statement [...n]

어떻게 수행되나 /SQL 문 • Parsing • 이름확인 • 보안 점검 • 최적화(Optimize) • Compile

어떻게 수행되나 /SP • 만들때 • Parsing • 이름확인 • 보안 점검 • 결과저장 • 첫 실행 • 최적화(Optimize) • Compile

프로시저의 장점 • 트래픽 • 편의성 • 매개변수 • 보안 • 성능 향상 (No more compile)

실행 • Exec • 매개 변수 • 리턴 값 반드시 컴파일 해야 할 때

트리거 • 정의 • 만들기/ 고치기/ 지우기 • 테이블 당 개수

만들기 CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS sql_statement [ ...n ] } ….

수행 순서 • sp_settriggerorder sp_settriggerorder 'MyTrigger', 'first', 'UPDATE' • FIRST • LAST • NONE • 하나씩만 지정가능

Non-updateable view CREATE VIEW AuthorsView AS SELECT au_fname+ ' ' + au_lname as au_name, au_id, phone, address, city, state, zip, contract FROM authors INSTEAD OF Trigger Example

CREATE TRIGGER AuIns on AuthorsView INSTEAD OF INSERT AS BEGIN INSERT authors(au_id,au_fname,au_lname,…) SELECT au_id, dbo.ExtractNamePart(au_name, 1), dbo.ExtractNamePart(au_name, 2), phone, address, city, state, zip, contract FROM inserted END INSERT AuthorsView VALUES(‘John Doe’, …) INSTEAD OF Trigger Example

트랜잭션과 관계 • 암시적 트랜잭션 • Rollback • 숨은 테이블 • Inserted • Deleted • 중첩 트리거 • 재귀 트리거

다중 행 처리 (1) alter trigger trg2 on sales2 for insert as declare @r int , @i int set @i = 0 select @r = count(*) from inserted while @i < @r begin insert sales3 values ('aa', 10) set @i = @i + 1 end

다중 행 처리 (2) alter trigger trg2 on sales2 for insert as insert sales3 select * from inserted go

기타 • 중첩 트리거 • 재귀 트리거 • 성능에 미치는 영향

SQL Server 2000 세미나 View, SP &Trigger