MS-SQL문 강좌 14장

-- ** 테이블의 컬럼은 되도록 적게(성능문제-데드락 발생가능성 높음)

 

1. 편의성(자주 사용하는 소스코드 저장-질의, 조인등...)

2. 보안성(숨기고자하는 내용 숨김)

create view view이름 "컬럼 목록"

as

select ...

 

create view viewTitle

as -- 다음절에는 select만 온다

select title_id, price from titles

 

select * from viewTitle -- 앞에서 선언한 뷰이름

 

select cast(title as char(30)) as bookTitle, price -- 타이틀이 길어서 자름

from titles

 

create view viewTilte2 -- 뷰 컬럼에 이름을 정해주어야 한다.

as

select cast(title as char(30)) as bookTitle, price -- as를 사용하여 이름을 지정해준다

from titles

 

create view viewTitle2("줄인제목", "가격") -- 컬럼에 이름을 주는 다른 방법

as

select cast(title as char(20)), price

from titles

 

select * from viewTitle2

where 가격 <7000 -- 이름을 바꿨을 경우 price가 아닌 바뀐 '가격'을 사용하여 질의

 

alter view viewTitle -- 뷰를 고칠때는 alter를 사용한다(새로 만들경우 권한이 삭제됨)

as

select title, price from titles

 

-- 뷰를 만들거나 고칠때 권장사항

1. dbo로 로그인한다.

2. 뷰에서 사용할 SQL문장을 먼저 작성한다

3. 먼저 작성한 문장을 실행ㅎ여 제대로 된 결과가 나타나는지 확인한다.

4. 문장앞에 "Create View dbo.뷰이름 as"를 붙인다. 컬럼이름을 준다

5. 만든 뷰를 사용할 사용자에게 권한을 준다

6. 사용자로 로그인하여 뷰에서 데이터를 가져온다. 원하던 결과인지 확인한다.

 

begin tran

drop view viewTitle -- 뷰를 지울때

rollback

 

sp_help viewTitle -- 뷰 테이블 정보보기

 

sp_helptext viewTitle -- 뷰 소스코드 보기

 

sp_depends viewTitle -- 의존하고 있는 테이블

 

alter view viewTitle -- 소스코드를 숨긴뒤에는 볼수 없으므로 다른곳에 저장해둔다

with encryption -- 뷰 소스코드 숨기기

as

select title_id, price from titles

 

-- 뷰에 대한 뷰 만들기

create view v1

as

select title_id, price *1 as VAT from viewTitle

 

select  * from v1

 

begin tran

drop view viewTitle -- 뷰는 상위테이블이라해도 지워지므로

                             -- 뷰에대한 뷰는 만들지않는것이 좋다

commit

rollback

 

select * from viewtitle2

 

update viewtitle2 set 가격 = 가격/2 -- 원래 테이블을 수정한다.

update viewtitle2 set "줄인제목" = "줄인제목" +'a' -- 삽입에 대해는 규제

 

select cast(title as char(20))as title, qty, price, t.title_id

from titles t join sales s -- 조인 테이블에서 뷰가 걸려 잇으면 한쪽 테이블만 업데이트 해야한다

             on t.title_id=s.title_id

 

create view vTitlePrice

as

select title_id, price

from titles

where price>$15.00

 

select * from vTitlePrice

 

begin tran

update vTitlePrice

set price = 10

where title_id = 'bu1032'

rollback

 

-- 뷰에서 사용할 수 없는 것

1. 뷰를 만들때 select into, order by, comute, compute by를 사용 할 수 없다.

2. 임시 테이블로 뷰를 만들 수 없다(데이터 없어짐)

3. 일괄처리 안에 뷰를 만들고 참조 불가(go로 분리해야함)

 

select * from sysobjects

where xtype= 'v' -- 뷰테이블을  가져와라(U-UserTable, P-Procedure)

 

select * from information_schema.views --뷰에대한 정보(권장)

 

-- 인덱스된 뷰(계산을 미리 저장해 두기 위한 뷰)

create view exampleView(prikey, sumColx, countColx)

with schema_binding

select prikey, sum(Colx), count_big(Colx)

from myTable

group by prikey -- 계산

go

-- 뷰안에 데이터를 만듬(다시 계산을 안하므로 속도향상에 도움

create union clustered index idx on exampleView(prikey)

 

select getdate() -- 비확정적 함수

 

select dateadd(m, 20, '2001.1.1') -- 확정적 함수(뷰에서 사용)

 

-- 분할된 뷰 (유니온으로 연결되며 대용량 디비에서만 사용 - 서버 3개 이상)

 

select orderID, productID, UnitPrice, Quantity

into tmpOd

from "Order Details"

 

select * from tmpOd

 

alter table tmpOd

add amt as UnitPrice * Quantity --amt 컬럼 추가(계산된 컬럼)

 

alter table tmpOd

add constraint pk_tmpod primary key

nonclustered (orderID, productID) -- 복합 키 설정

 

create nonclustered index idxAmt on tmpod(amt) -- 계산되어진 amt를 인덱스 추가

 

sp_helpindex tmpod

 

-- 인덱스된 컬럼 (계산된 테이블을 사용한 예-빠르기 정도)

select amt  from tmpOd -- 가장 빠름

where amt <50

order by amt

 

select amt

from tmpOd with (index(pk_tmpod)) -- primary key를 사용

where amt <50

order by amt

 

select amt from tmpod whth (index(0)) --테이블 스캔(index(0))

where amt < 50

order by amt

 

-- 인덱스된 뷰

 procuctid,

sum(unitPrice * quantity) as total

sum(quantity) as totQty

count (*) as count -- procuctid별로 갯수를 세라

 

select productid, sum(unitPrice * Quantity) as total,

             sum(quantity) as totQty, count(*) as count

from "order details"

group by productid

 

 

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" -- dbo를 꼭 써준다

group by productid

 

create unique clustered index idx on vTotalProduct(productid)

 

select productid, sum(quantity) as totalQty --Unique 를 만들고 나서는 뷰에 데이터가 저장됨

from "order details"

group by productid

option(expand views) -- 뷰가 아닌 테이블에서 데이터를 가져옴(다른 많은 작업 시)