1. 뷰(View)란 무엇인가?
다음의 가정을 세우고 시작하겠습니다.
o 인사관리 시스템을 위한 [직원] 테이블이 있습니다.
o [직원] 테이블에는 직원들의 급여 정보가 들어 있습니다.
o 직원들의 급여정보는 Confidential 이기 때문에 인사팀 외에는 보아서는 안됩니다.
o 총무팀에서 직원관리를 위하여 직원들의 신상정보를 요구해 왔습니다.
o 하지만 총무팀에도 급여정보는 제공할 수가 없습니다.
위 경우에 별도의 [직원2] 테이블을 만들어 총무팀에서 필요로하는 정보만 [직원] 테이블로부터 가져와 기록하여 제공해도 되지만 별도의 테이블을 만들지 않고 [직원] 테이블에서 총무팀에 제공할 컬럼만을 선택하여 가상의 테이블을 만들어 제공 할 수 있습니다. 이렇게 되면 총무팀에서는 만들어진 가상의 테이블을 사용하여 원하는 작업을 할 수 있고, 인사팀은 급여정보가 유출되는 문제를 막을 수 있게 됩니다. 이때 사용되는 가상의 테이블을 뷰(View)라고 합니다.
즉, 뷰(View)란 하나 또는 그 이상의 테이블로부터 생성된, 하지만 물리적으로 존재하지 않는 가상의 논리적인 테이블입니다. 하나의 테이블에서만 SELECT 되어진 결과를 가지고 뷰를 만들 수도 있고, 여러개의 테이블을 조인(JOIN)하여 SELECT한 결과를 가지고 뷰를 만들수 있습니다. 사용자(End User) 입장에서는 뷰를 일반적인 테이블과 똑같은 방법으로 사용하게 되므로, 이 뷰가 어떻게 만들어 졌는지 알지 못합니다.
지금까지의 내용으로 보아도 우리는 뷰의 장점을 다음과 같이 이야기 할 수 있습니다.
o 보안성 : 중요한 컬럼(급여정보 처럼)을 숨길 수 있어 보안을 유지 할 수 있습니다.
o 편의성 : 여러개의 테이블 사용을 위해 복잡한 조인(Join)이 사용되었다 하더라도 사용자는 이를 몰라도 됩니다.
o 간결성 : 뷰를 사용하면 Application을 개발할 때 복잡한 쿼리문을 숨길 수 있어 소스가 간결해집니다.
이외에도 뷰를 사용하다보면 많은 장점들을 경험할 수 있습니다.
2. 뷰(View)를 만드는 방법(CREATE VIEW)
다음의 Employee 테이블의 구조와 Employee 테이블에서 몇몇 컬럼을 가져오는 쿼리문입니다.
CREATE TABLE Employee
(
emp_code char(3) NOT NULL,
emp_name char(10) NOT NULL,
emp_address varchar(70) NULL,
emp_age int,
emp_salary money
)
GO
SELECT emp_code, emp_name, emp_address
FROM Employee |
다음과 같이 위 SELECT 퀴리문에 CREATE VIEW를 추가함으로써 뷰(View)를 만들 수 있습니다.
CREATE VIEW vw_Employee
AS
SELECT emp_code, emp_name, emp_address
FROM Employee |
이제 vw_Employee 뷰가 만들어 졌으므로 사용자는 이 뷰를 일반 테이블과 마찬가지 방법으로 사용 할 수 있습니다.
SELECT * FROM vw_Employee |
o 수행 결과는 맨 처음의 SELECT 문과 같습니다.
o 이상태에서 실제 테이블 Employee에 5개의 Row가 추가 되었다면 vw_Employee 역시 똑같이 5개의 Row를 갖고 있게 됩니다. 단지 컬럼중에 뷰를 만들때 선언한 컬럼만을 가지고 있다는 차이뿐입니다.
3. 뷰(View)의 특징 몇가지
1) 테이블에서 컬럼이 삭제되면?
다음에 포함된 두 SELECT 문은 결과가 다릅니다.
ALTER TABLE Employee
DROP COLUMN emp_age
GO
SELECT * FROM vw_Employee -- 1)
ALTER TABLE Employee
DROP COLUMN emp_address
GO
SELECT * FROM vw_Employee -- 2) |
o 처음에 테이블에서 제거된 컬럼 emp_age는 vw_Employee에 포함되지 않는 컬럼이므로 1)번 SELECT문은 정상적으로 수행됩니다.
o 두번째 제거된 컬럼 emp_address는 vw_Employee 선언시 포함된 컬럼이므로 2)번 SELECT문은 다음의 오류를 발생시킵니다.
서버: 메시지 207, 수준 16, 상태 3, 프로시저 vw_employee, 줄 3
열 이름 'emp_address'이(가) 잘못되었습니다.
서버: 메시지 4413, 수준 16, 상태 1, 줄 1
바인딩 오류로 인해 뷰 또는 함수 'vw_Employee'을(를) 사용할 수 없습니다. |
2) 테이블에 컬럼이 추가되면?
다음과 같이 MyTable 이 구성되어 있고 이 테이블의 모든 내용을 SELECT 해오는 vw_MyTable을 만들었습니다.
CREATE TABLE MyTable
(
col1 CHAR(2),
col2 CHAR(3),
col3 CHAR(1)
)
GO
CREATE VIEW vw_MyTable
AS
SELECT * FROM MyTable
SELECT * FROM MyTable --1)
SELECT * FROM vw_MyTable --2) |
o 두 SELECT문의 결과는 의심할 여지없이 동일한 결과를 갖습니다.
이 상태에서 다음과 같이 MyTable에 컬럼을 추가 했습니다.
ALTER TABLE MyTable
ADD col4 CHAR(2)
GO
SELECT * FROM MyTable --1)
SELECT * FROM vw_MyTable --2) |
o 1)번 SELECT문은 당연히 추가된 컬럼 col4를 포함한 결과를 보여줍니다.
o 뷰를 선언할 때 모든 컬럼을 포함하라고 SELECT * 했으므로 2)번 SELECT문도 추가된 행이 보일것 같습니다. 하지만!
o 2)번의 결과에는 추가된 컬럼 col4 가 포함되지 않습니다.
o 뷰를 만들때 SELECT * 이라고 했어도 그 당시에 존재하는 컬럼의 정보가 시스템 테이블(syscolumns)에 기록되어 이를 이용해 뷰의 결과가 보여지므로 이후에 추가된 컬럼은 뷰에 포함되지 않게 됩니다.
o 이 사항을 잘 몰라 뷰를 사용할 때 오류를 범하는 경우가 있습니다.
3) 뷰를 사용하면 속도가 빨라진다?
물론 SQL Server 2000에서 제공하는 새로운 기능인 뷰에 인덱스를 만들어주면(많은 제약이 있지만) 일반적인 쿼리문 수행보다 속도가 빨라집니다. 하지만 일반적으로 뷰는 속도 향상보다는 앞 강좌에서 이야기 되었던 장점을 위해 사용됩니다. 사용자가 뷰로부터 SELECT하게 되면 그 순간 SQL Server는 뷰른 만들때 선언한 쿼리문을 읽어와(syscomments 에서) 그 쿼리문을 일반 쿼리문과 같은 형식으로 수행하므로 속도에는 차이가 없게됩니다.
4. 뷰(View)를 통한 데이터 업데이트
물론 View는 데이터를 업데이트하기 위한 목적보다는 편리하게 검색하기 위한 목적으로 사용됩니다. 하지만 분명 View를 통하여 우리는 실제 테이블의 데이터를 수정할 수 있습니다(View가 수정되는 것이 아니고 실제 데이터가 변경되어 View에는 변경된 내용이 보이는것입니다).
View를 통해서 테이블의 데이터를 변경하는 것은 다음과 같은 몇가지 제약이 있습니다.
o 동시에 하나의 테이블(View를 구성하는 실제 테이블)만을 변경 할 수 있습니다.
o 계산된 컬럼은 변경할 수 없습니다.
o View를 만들때 WHERE절을 이용하여 선택에 제한을 두었다면 이 제한을 벗어나는 값으로는 변경을 할 수 없습니다.
다음의 예제를 보면서 몇가지 경우를 설명드리도록 하겠습니다.
CREATE TABLE Employee
(
emp_code char(05),
emp_name char(10),
dept_code char(05)
)
GO
CREATE TABLE Department
(
dept_code char(05),
dept_name char(10)
)
GO
CREATE VIEW vw_Employee1 --1)
AS
SELECT emp_code, emp_name
FROM Employee
GO
CREATE VIEW vw_Employee2 --2)
AS
SELECT e.emp_code, e.emp_name, e.dept_code, d.dept_name
FROM Employee e
JOIN Department d ON e.dept_code = d.dept_code
GO |
o 1)에서는 Employee 테이블만을 참조하는 뷰를 만들었습니다.
o 2)에서는 Employee 테이블과 Department 테이블을 참조하는(조인으로 연결된) 뷰를 만들었습니다.
[예제1]
UPDATE vw_Employee1 SET emp_name = '이장래' -- 1)
WHERE emp_code = 'EM103'
GO
UPDATE vw_Employee2 SET emp_name = '이장래' -- 2)
WHERE emp_code = 'EM103'
GO
UPDATE vw_Employee2 SET dept_name = '개발팀' --3)
WHERE dept_code = 'DT001'
GO |
o 위 모든 경우는 뷰를 통하여 테이블의 데이터를 변경할 수 있는 예입니다.
o 변경의 대상이 되는 실제 테이블이 1), 2)의 경우는 Employee, 3)의 경우는 Department 하나뿐이기 때문입니다.
[예제2]
UPDATE vw_Employee2 SET emp_name = '한국인', dept_name = '인사팀'
WHERE emp_code = 'EM103'
GO |
o emp_name을 변경하기 위해서는 Employee테이블이, dept_name을 변경하기 위해서는 Department 테이블이 실제 변경되어야할 테이블입니다.
o 동시에 두 테이블이 변경되어야 하므로 위 예제는 오류를 발생시킵니다.
5. 뷰(View)에 인덱스 만들기
제가 처음 SQL Server 2000을 접하고 놀란 이유중의 하나가 뷰에 인덱스를 걸수 있다는 사실입니다. 그전에는 뷰의 편리함을 알았지만 이 뷰라는 것이 성능에는 별 효과가 없어서 항상 불만을 품고 있었습니다. SQL Server 2000은 과감히 이러한 불만을 없애주었습니다. 물론 멏가지 제약이 있기는 하지만 이제 우리는 뷰에 인덱스를 만들어 성능 향상을 볼 수 있습니다.
다음의 예제를 보면서 몇가지 제약과 함께 부에 인덱스를 만드는 방법을 알아보도록 하겠습니다.
1) SCHEMABINDING 옵션
뷰에 인덱스를 만들 수 있다는 사실에 너무 기뻐 다음과 같이 앞에서 다루었던 뷰에 인덱스를 걸어보고자 했습니다.
CREATE VIEW vw_Employee2
AS
SELECT e.emp_code, e.emp_name, e.dept_code, d.dept_name
FROM Employee e
JOIN Department d ON e.dept_code = d.dept_code
GO
CREATE INDEX idx1 ON vw_Employee2(emp_code) |
하지만 우리는 다음과 같은 오류를 보게됩니다.
서버: 메시지 1939, 수준 16, 상태 1, 줄 1
'vw_Employee2' 뷰는 스키마 바운드 뷰가 아니므로 이 뷰에 인덱스을(를) 만들 수 없습니다. |
SCHEMABOUND 뷰가 아니기에 인덱스를 만들수 없다고 합니다. 즉 인덱스를 만들수 있는 뷰는 다음과 같이 SCHEMABOUND된 뷰에 한해서입니다. 테이블명 앞에 소유자를 붙여야함도 확인하시기 바랍니다.
CREATE VIEW vw_Employee3
WITH SCHEMABINDING -- 여기를 주목
AS
SELECT e.emp_code, e.emp_name, e.dept_code, d.dept_name
FROM dbo.Employee e
JOIN dbo.Department d ON e.dept_code = d.dept_code
GO |
2) CLUSTERED UNIQUE 인덱스 필요
하지만 역시 새로만든 vw_Employee3 뷰에 인덱스를 만들려고 하면 다른 오류를 보게됩니다. SCHEMABOUND된 뷰임에도 불구하고.
서버: 메시지 1940, 수준 16, 상태 1, 줄 1
'vw_Employee3' 뷰에 인덱스을(를) 만들 수 없습니다. 클러스터된 고유 인덱스가 없습니다. |
클러스터드된 인덱스가 없어서 안된다고 합니다. 즉 뷰에 인덱스를 만들때는 다음과 같이 제일 먼저 클러스터드된 고유 인덱스를 만들어야 합니다.
CREATE UNIQUE CLUSTERED INDEX idx1 ON vw_Employee3(emp_code) |
이제 원한다면 다음과 같은 다른 인덱스도 만들 수 있습니다.
CREATE INDEX idx2 ON vw_Employee3(dept_code) |
3) 이외에도
실제적인 테이블을 참조하는 뷰에만 인덱스를 만들수 있습니다. 뷰를 참조하는 뷰에는 인덱스를 만들 수 없습니다.
출처: http://www.sqlworld.pe.kr/mboard/mboard/mboard.asp?board_id=sql01&group_name=board&idx_num=10&page=1&category=&search=&b_cat=0&order_c=idx_num&order_da=asc
