본문 바로가기
Programming/DB (SQL)

[SQL Server] Chapter 13. 뷰

by 곰네Zip 2014. 12. 9.

* 본 포스팅은 개인의 학습 내용을 정리한 것입니다.

 

- 뷰 : 쿼리로 정의된 가상 테이블. 데이터를 저장하고 있는 공간은 없음.

- 뷰 사용의 장점

  1) 사용자의 관심부분 데이터에 초점을 맞출 수 있다.

  2) 자주 사용하거나 복잡한 쿼리에 대해 복잡성을 감추고 재사용 할 수 있다.

  3) 특정 사용자에게 일부 자료를 보여주고 싶을 때, 허가 관리를 간소화 할 수 있다.

  4) 다른 어플리케이션으로 데이터를 보낼 때, 편리하다.

 

1. 뷰의 생성 

 CREATE [ <소유자>] VIEW <뷰이름> [ (컬럼목록)] [ WITH <뷰속성목록>] AS

   <select구문> [WITH CHECK OPTION]

 

 *뷰속성 : { ENCRYPTION | SCHEMABINDING | VIEW_METADATA}

 (1) 뷰의 컬럼명 명시

   - 컬럼명을 정의하지 않을 경우 원시 컬럼명이 상속되어 정의된다. 만약 컬럼명이 변경되는 경우 컬럼명을 반드시 지정해야 한다. 조인 등으로 컬럼명이 중복될 경우 정확히 명시해 주어야 한다.

   - 컬럼명 명시는 리스트를 이용하거나 쿼리에 포함하는 방법이 있다.

 

 

 (2) 뷰 정의시 포함할 수 없는 구문

   1) COMPUTE, COMPUTE BY문 : 156메시지. 컴파일 안된다.

   2) ORDER BY문 : 1033메시지 반환된다. 뷰에서 사용할 수 없다고 나온다.

   3) INTO문 : 156.

   4) 임시 테이블 참조 : 4508. 임시테이블 참조할 수 없다고 나온다.

 

 (3) 뷰에서 외부 조인의 자제 : 외부 조인 포함을 허용하지만, 이 뷰와 다시 외부조인할 경우 결과가 많아지고, 방향에 따라 조인에 실패할 수 있다.

 

2. 뷰 생성시 옵션

 (1) WITH CHECK 

   - 뷰를 통한 데이터 입력, 수정, 삭제시 작업 범위에 제한을 두기 위함.

 

 (2) WITH ENCRYPTION

  - 뷰의 내용을 암호화 하는 옵션. 암호화를 제거할 수 있는 방법은 없다.

 

 (3) WITH SCHEMABINDING

  - 뷰는 독립 객체이므로 참조하고 있는 하위 개체의 스키마만 변형이 가능하다.

 

3. 뷰를 통한 데이터 변환

 - 뷰는 실제 데이터를 가지고 있지 않으므로, 실제 데이터는 실 테이블을 변경한다.

 - 제약조건

  1) 계산식 함수 사용 등으로 새로 생성된 컬럼에 대해서는 수정할 수 없다.

  2) 하나 이상의 테이블과 조인하여, 구성된 뷰를 통해 서로 다른 기초 테이블의 칼럼을 동시에 수정할 수 없다.

  3) WHERE절 검색 조건에 벗어나는 범위에 대한 UPDATE, DELETE문에 대해서는 오류는 없으나 작업되는 행은 없다

  4) VIEW CHECK OPTION으로 정의된 뷰에 대해 범위 밖의 자료에 대해 INSERT나 UPDATE를 수행할 수 없다.

 

4. 뷰의 변경 및 제거

  - ALTER VIEW, DROP VIEW를 이용하여 제거한다.

 

5. 뷰의 정보를 담고 있는 시스템 테이블

  - 뷰 또는 참조하는 개체의 테이블의 컬럼이 변경되어 일치하지 않는 컬럼이 있으면 오류가 발생된다.

  1) 정보 스키마 뷰

    SQL 7.0부터 등장하는 정보 스키마 뷰.

   - 테이블과 뷰의 정보를 한번에 확인하기. (sysobject를 사용해도 비슷한 결과를 얻는다. 다만, sysobject가 더 많은 정보를 보여준다.

 SELECT * FROM sysobject WHERE type IN ('U', 'V')

 SELECT * FROM INFORMATION_SCHEMA.TABLES

 

   - 뷰와 테이블 간의 참조를 확인하려면 다음 쿼리를 실행하자

 SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE

 

   - 뷰의 스크립트까지 전체의 뷰를 한번에 확인하려면 다음과 같이 실행하자

 SELECT * FROM INFORMATION_SCHEMA.VIEWS

 

   - 뷰의 컬럼명과 기초 테이블, 컬럼에 대한 자료 확인

 SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

 

6. 뷰에 인덱스 생성

 - 필요에 따라 뷰에 인덱스를 생성하여 쿼리 속도를 향상시킬 수 있다. 그러나 여러가지 제약 사항이 따른다.

 - 매우 많은 제약사항이 존재한다. 옆의 링크 참조: http://msdn.microsoft.com/ko-kr/library/ms191432.aspx

 

7. 분할된 뷰

 - 분할 뷰는 다른 뷰와 다르게 UNION ALL을 이용한 종적 결합으로 구성된 뷰를 이야기한다. 

 - 분할 뷰의 목적은 대용량 DB나 사용자가 많은 경우, 여러 서버나 분리된 DB에 테이블을 분산하고 처리하는 방법

   > 스케일 업 : 같은 서버에서 업그레이드를 통해 서버의 처리용량을 늘리는 방법

   > 스케일 아웃 : 여러서버를 다중 계층으로 구성하여 병렬처리를 통한 대용량 지원이 목적

 1) 분할된 뷰 생성시 제약사항

   a) SELECT list

    - 뷰에 나열된 모든 컬럼은 구성한 테이블에서 추출된 컬럼이어야 한다.

    - 종적으로 결합되는 테이블 컬럼의 순서는 같아야 하고, 데이터 유형은 같아야 한다.

    - 뷰를 구성하는 기초 테이블은 동일한 컬럼들로 구성된 기본키를 가지고, CHECK제약조건 역시 동일한 컬럼으로 구성되어야 한다.

    - 복합 제약 조건을 사용하기 위해서는 제약조건 셋을 사용할 수 있고, 그 구간은 겹치지 않아야 한다.

   b) 파티셔닝 컬럼 (파티션 시, 기준으로 할 컬럼)

    - 파티셔닝 컬럼은 기본 키의 부분집합이어야 한다. 

    - 계산에 의해 생성된 컬럼을 사용할 수 없다.

    - 여러 제약조건이 동시 구현된 경우에는 다른 제약조건을 무시하고, 한 파티션 컬럼에 한 제약조건만을 수용하게 됨.

   c) 구성테이블

    - 원격 서버와 작업하기 위해서는 객체명은 4파트로 구성된 이름(서버.DB.소유주.객체)을 사용해야 함

    - 같은 테이블의 테이블 셋이 두번이상 나열될 수 없다.

    - 구성 테이블은 모두 PRIMARY KEY 제약조건을 가지고 있어야 한다.

 

 2) 분할된 뷰의 사용 규칙

  - INSERT구문의 데이터 입력을 위해, 뷰 컬럼에 포함되지 않은 기초테이블의 컬럼은 NULL허용이나 DEFAULT필요

  - INSERT INTO로 여러 행 입력시, 제약조건에 위배되는 사항이 있으면, 그 작업은 실패.

  - 컬럼에 DEFAULT가 정의되어 있어도, INSERT,UPDATE에 DEFAULT키워드 사용불가

  - text,ntext,image 컬럼이 포함된 경우 UPDATE구문으로 PRIMARY KEY컬럼을 수정할 수 없음

  - IDENTITY컬럼에 INSERT / UPDATE구문으로 수정할 수 없다.

  - timestamp컬럼을 포함하고 있을 때, UPDATE,INSERT구문으로 수정 불가

  - 재귀 조인을 가지고 있는 경우에 INSERT,UPDATE,DELETE 구문을 수행할 수 없다.

  - 리모트 서버에 수정을 할 때, SET  ROWCOUNT 옵션은 무시된다.

 

8. 소유권 체인

 - SELECT, DELELTE, UPDATE, INSERT등의 구문은 사용 권한에 제약을 받게 된다. 그러나 저장프로시저의 권한EXEC, DRI는 영향을 받지 않는다. 즉, EXEC의 권한을 가지고 있으면, 다른 접근 불가능한 테이블에도 접근이 가능해짐.

 

 

 

- 출처 : SQL Server advanced programming

반응형

댓글