안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 7-2. 뷰 생성을 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 뷰(VIEW) 생성입니다.

TL;DR

뷰 생성 구문 예제로 뷰를 생성하고, 암호화 옵션, WITH CHECK 옵션을 이용하여 데이터 무결성을 유지하는 방법, 뷰 정보 확인을 위한 sp_help 사용법을 진행합니다.

지난 뷰 소개 강좌에서 뷰의 많은 부분을 설명해 드렸습니다. 그럼 조금 더 상세하게 뷰 생성에 대해서 살펴보겠습니다.

뷰 생성 구문

이런 패턴 정도만 보면 됩니다. 그럼 다양한 뷰 생성 예제를 실행하겠습니다.

뷰 생성 구문 확인

지난 강좌에서 v_Employee 뷰를 생성했습니다. 이런 코드였습니다.

-- v_Employee 뷰는 지난 강좌에서 이미 생성
CREATE VIEW v_Employee
AS
SELECT emp_num, emp_name, email FROM Employee;
GO

-- 뷰 조회
SELECT * FROM v_Employee;
GO

지난번 트리거와 앞으로 배울 저장 프로시저와 마찬가지로, 뷰 생성 구문을 아래 명령으로 확인 가능합니다.

EXEC sp_helptext v_Employee

실행하면 뷰 생성 구문이 보입니다. 

뷰 생성 구문 암호화

뷰 생성 구문도 암호화할 수 있습니다. WITH ENCRYPTION 구문을 사용합니다.

-- 뷰가 존재하면 삭제
IF OBJECT_ID(N'dbo.v_Employee', N'V') IS NOT NULL  
   DROP VIEW dbo.v_Employee;  
GO

-- v_Employee 뷰 생성구문 암호화
CREATE VIEW v_Employee
WITH ENCRYPTION  -- 생성 구문 암호화
AS
SELECT emp_num, emp_name, email FROM Employee;
GO

EXEC sp_helptext v_Employee;
GO

결과
The text for object 'v_Employee' is encrypted.

이렇게 뷰 생성 구문이 암호화되었습니다. 뷰 생성 SQL 구문을 잘 백업해 두세요.

뷰의 WITH CHECK 옵션

다음은 뷰의 WITH CHECK 옵션입니다. 간략히 다음 경우를 생각해 볼까요?

-- 제품 가격이 1,250원 이상인 제품만 보는 뷰를 생성
CREATE VIEW v_expensive_product
AS
SELECT * FROM products
WHERE price > 1250;
GO

SELECT * FROM v_expensive_product
WHERE product_name LIKE N'포테토칩';
GO

뷰를 조회해 보면 조회가 잘 됩니다. 그렇다면, 이런 경우를 가정해 보겠습니다. 

뷰의 정의대로 1,250원 이상 제품만 보입니다. 만약 제품 가격 1,800원 제품을 1,000으로 업데이트하면 뷰의 정의인 1,250원 이상 조건에 위배됩니다. 수정을 허용해야 할까요? 아니면 하면 안 될까요?

-- 1,800원인 포테토칩을 UPDATE 해서 가격을 1,000원으로 낮추는 작업
UPDATE v_expensive_product SET price = 1000
WHERE product_name LIKE N'포테토칩';
GO
-- UPDATE 됨.

업데이트가 됩니다. 그리고 뷰를 조회해 보면? 뷰는 1,250원 이상 제품만 보게 되니 포테토칩은 보이지 않게 되겠죠. 

허용하는 것도 가능하지만, 뷰를 통해 마련한 데이터가 갑자기 사라지는 것처럼 보이면서 혼란스러울 수 있습니다. 비즈니스 로직상 뷰는 제품가격 1,250원 이하를 막아야 한다면, 어떻게 이런 수정 작업이 불가하게 해야 할까요? 이때 WITH CHECK OPTION을 사용합니다.

-- 원래 목적은 이런 수정 작업이 뷰에서 불가하게 하는 것이 목표
-- 뷰가 존재하면 삭제
IF OBJECT_ID(N'dbo.v_expensive_product', N'V') IS NOT NULL  
   DROP VIEW dbo.v_expensive_product;  
GO

-- WITH CHECK 옵션으로 생성
CREATE VIEW v_expensive_product
AS
SELECT * FROM products
WHERE price > 1250
WITH CHECK OPTION;  -- 체크 옵션 적용
GO

-- 다시 1,300원인 꼬북칩을 1,000원으로 업데이트 수행
UPDATE v_expensive_product SET price = 1000
WHERE product_name LIKE N'꼬북칩';
GO

메시지 550, 수준 16, 상태 1, 줄 117
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.


-- 다시 1,300원인 꼬북칩을 뷰 정의인 1,250 이상 - 1290원으로 업데이트 수행
UPDATE v_expensive_product SET price = 1290
WHERE product_name LIKE N'꼬북칩';
GO
-- 성공

이렇게 뷰 정의 조건에 맞는 CHECK 제약을 추가해 뷰의 데이터 업데이트를 제한할 수 있습니다. 

뷰 정보 확인

뷰 정보를 보는 방법은 테이블 정보 확인 방법과 같은 sp_help를 이용합니다.

-- 뷰 정보 확인 sp_help - 테이블 정보 확인과 같음
EXEC sp_help 'v_expensive_product'
GO

SSMS에서 뷰 생성 및 설계

SSMS에서 뷰 설계와 정보 확인이 가능합니다. 데이터베이스 - 뷰 - “뷰 설계”를 선택해 수정하거나 “새 뷰”를 선택해 뷰를 생성할 수 있습니다.

이미지 - SSMS에서 뷰 생성 및 설계

늘 말씀드리지만, SSMS와 같은 GUI 도구보다는, 항상 쿼리로 생성하시길 권장해 드립니다.
다음 강좌에서는 뷰 수정에 대해서 살펴보겠습니다.

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022