jjali's Blog: MS-SQL View

VIEW는 실제 존재하지 않는 가상적 Table로서 다른 Table에서 Data를 가져와 이를 한곳에 묶어서 보여주는 역활을 합니다. 이러한 특징을 통해 개발자는 사용자가 보길 원하는 Data만 보여줄 수 있을 뿐 아니라 VIEW가 참조하고 있는 실제 Table에 대한 접근제한(VIEW를 통해서도 Data의 추가, 삭제, 변경등이 가능합니다.)을 통해 보안성을 기대할 수 있습니다.

1. View의 생성

Create view <이름>
As
<table 조회 구문>

<이름>에는 View의 이름을 지정하며 <table 조회 구문>에서는 실제 Table을 조회하는 Select문을 작성합니다.

Create view viewDepartment
As
Select BusinessEntityID, A.DepartMentID, Name, GroupName
From HumanResources.EmployeeDepartmentHistory As A, HumanResources.Department As B
Where A.DepartmentID = B.DepartmentID;

HumanResources.EmployeeDepartmentHistory와 HumanResources.Department Table의 Join을 통해 Department의 ID와 Name, GroupName를 같이 조회하는 View를 생성합니다.

Select * From viewDepartment;

생성된 View를 조회합니다.

만일 생성된 VIEW를 변경하고자 한다면 Alter를 사용하면 됩니다.

Alter view viewDepartment
As
Select BusinessEntityID, A.DepartMentID, Name, GroupName, StartDate
From HumanResources.EmployeeDepartmentHistory As A, HumanResources.Department As B
Where A.DepartmentID = B.DepartmentID;

HumanResources.EmployeeDepartmentHistory Table에 있는 StartDate열을 View에 추가합니다.

Select * From viewDepartment;

변경된 View를 조회합니다.

View의 삭제는 Drop문을 통해 구현합니다.

Drop view viewDepartment;

앞서 보신바와 같이 View는 실제존재하는 Table를 참조하여 원하는 Data를 보여주고 있습니다. 그런데 이 상태에서 View가 참조하고 있는 Table 혹은 Table의 Column 이름(또는 형식)이 변경되는 상황이 발생하면 분명 문제가 발생할 것입니다.

이러한 문제를 방지하려면 Schemabinding Option을 사용합니다.

Create view viewDepartment With Schemabinding
As
Select BusinessEntityID, A.DepartMentID, Name, GroupName
From HumanResources.EmployeeDepartmentHistory As A, HumanResources.Department As B
Where A.DepartmentID = B.DepartmentID;

Schemabinding Option을 통해 View를 생성하면 해당View에서 쓰이는 Table(혹은 Column과 형식)이름은 변경이 불가능하게 됩니다.

Exec Sp_rename 'HumanResources.EmployeeDepartmentHistory','EmployeeDepartment'

Table이름 변경을 시도하고 있습니다.

Exec Sp_rename 'HumanResources.Department.[Name]', 'Title', 'Column'

Table의 Column이름을 변경합니다.

Alter Table HumanResources.Department Alter Column Name nVarchar(60);

Table의 Column형식을 변경합니다.

참고:
sp_rename은 명칭변환에 관한 Procedure입니다.

Schemabinding 옵션으로 Error가 발생한다는 것을 알 수 있습니다.

2. View를 통한 data변경

생성된 View만을 가지고도 Data를 Update하거나 Insert, Delete할 수 있습니다. 실제 이렇게 하기위한 특별한 방법이 있는 것은 아니므로 일반 Table과 똑같이 쓰시면 됩니다.

Update viewDepartment
Set GroupName = 'Engineering and Development'
Where BusinessEntityID = 2;

BusinessEntityID가 2인것의 GroupName을 변경하고 있습니다.

Select * From viewDepartment;

View를 확인해 보시면

뭔가 이상한듯 합니다. Update할때는 BusinessEntityID가 2인것만 변경하라고 했는데 다른 부분이 같이 변경되어 있기 때문이죠.

이런 현상이 발생한 이유는 View에서의 Data변경이 실제 Table의 Data에도 반영되기 때문입니다. 즉, GroupName열의 실제 Table에 해당하는 HumanResources.Department Table에서 변경작업이 반영되었기 때문에 이를 참조하는 View에서도 GroupName을 가져오는 부분이 전부 바뀌게 된 것입니다.

이처럼 View에서의 Data변경(Insert와 Delete 포함)은 실제 Table에도 영향을 미치게 되므로 주의해야 합니다. 또한 View에서 Data를 변경하는 작업을 할때는 View가 참조하고 있는 Table하나만을 대상으로 해야 합니다.

예를 들어 위에서 구현한 Update문에서는 HumanResources.Department Table의 GroupName열만을 변경하려고 했기 때문에 가능했지만 다음 예제와 같이 GroupName과 HumanResources.EmployeeDepartmentHistory Table에 해당하는 BusinessEntityID열의 변경을 같이 추가하는 변경작업은 불가능합니다.

Update viewDepartment
Set GroupName = 'Engineering and Development',
BusinessEntityID = 300
Where BusinessEntityID = 2;

HumanResources.Department 와 HumanResources.EmployeeDepartmentHistory 두개의 Table을 대상으로 View를 통한 Update를 시도하였습니다.

주의:
위 예제는 이해를 돕기위한 예제일 뿐입니다. View의 제약사항이 아니라 하더라도 HumanResources.EmployeeDepartmentHistory Table의 특성상 실제로는 작동할 수 없는 Query입니다.

View를 통해 Data를 변경할 수 없는 경우는 또 있습니다. 그것은 View를 생성할때 다른 Table의 열을 그대로 가져와 생성한 것이 아닌 Sum이나 Avg혹은 기타 다른 방법을 통해서 계산된 형태의 열을 가져오는 경우오도록 구현된것 또한 변경이 불가능합니다.

3. View의 Index

일반적인 View는 실제 Data를 가지지 않고 다른 Table로 부터 Data를 가져와 보여주는 역활을 합니다. 이런 View에 Index를 부여하게 되면 이 View는 다른 Table로 부터가 아닌 View에서 직접적으로 Data를 조회하게 됩니다.

그래서 Index를 가지는 View는 보통 일반 View보다 성능적으로 개선될 수 있지만 만일 이 View가 참조하고 있는 Table에서 Data변경이 발생하면 View에도 Data변경이 같이 발생하기 때문에 변경작업에는 오히려 좋지않은 영향을 끼칠 수있습니다.

따라서 이와 같은 특징을 잘 고려하여 View에 Index를 생성할지 여부를 결정해야 합니다.

View에 Index를 생성하는 방법은 다음과 같습니다.

Create Unique Clustered Index inx_viewDepartment On viewDepartment(BusinessEntityID, DepartMentID);

위에서 생성한 viewDepartment View에 BusinessEntityID와 DepartMentID열을 가지고 Index를 생성합니다.(현재 내용상 BusinessEntityID에 중복값이 있기 때문에 EusinessEntityID열만 가지고 Index를 만들 수 없는 없습니다.)

View에 Index를 생성하고 나서 해당 View를 조회 하려면 다음과 같이 할 수도 있겠으나

Select * From viewDepartment;

이것은 Index가 생성된 View에는 권장할 만한 방법이 아닙니다. Index를 제대로 타지 않기 때문이므로 되도록 다음과 같이 조회하는 것을 권장합니다.

Select *
From viewDepartment With (Noexpand);

Noexpand Option을 추가하여 View에 Index를 제대로 적용하도록 지정합니다.

4. View에 대한 상세한 정보확인

View가 생성되면 생성될때의 Create문은 sys.sql_modules에 저장됩니다.

Select * From sys.sql_modules;

sys.sql_modules는 object id별로 Data를 저장하는데 이것만 봐서는 내가 원하는 View가 어떤 id로 저장되어 있는지 알 수가 없습니다. 따라서 OBJECT_ID를 이용하여 생성한 View에 대한 object id를 얻고 이를 통해 얻어진 id값을 비교해서 조회 하여야 합니다.

Select * From sys.sql_modules
Where object_id = OBJECT_ID('viewDepartment');

이렇게 하면 View를 생성할때의 생성문을 확인할 수 있습니다.

이때 만일 View생성에 대한 Crate문을 볼 수 없도록 보안조치를 취하려면 View생성시 Encryption을 사용하여 암호화 하도록 합니다.

Create view viewDepartment With Encryption
As
Select BusinessEntityID, A.DepartMentID, Name, GroupName
From HumanResources.EmployeeDepartmentHistory As A, HumanResources.Department As B
Where A.DepartmentID = B.DepartmentID;

Select * From sys.sql_modules
Where object_id = OBJECT_ID('viewDepartment');

암호화가 처리가 정상적으로 적용되었는지 확인하기 위해 sys.sql_modules를 조회합니다.