* 본 포스팅은 개인의 학습 내용을 정리한 것입니다.
- 뷰 : 쿼리로 정의된 가상 테이블. 데이터를 저장하고 있는 공간은 없음.
- 뷰 사용의 장점
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
댓글