안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 4-1. SQL Server 데이터베이스와 데이터베이스 개체의 이해를 진행 하겠습니다.

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

TL;DR

데이터베이스는 여러 테이블과 개체로 구성되어있습니다. 데이터베이스 개체는 테이블, 뷰, 저장 프로시저 등이 있습니다.
 

이번에 진행할 강좌는 SQL Server 데이터베이스와 데이터베이스 개체의 이해입니다.
이번 강좌부터는 데이터베이스에 대해서 진행합니다. 잠시 정리를 해볼까요? 지금까지 공부한 SELECT, INSERT, UPDATE, DELETE 구문은 모두 테이블 단위로 수행되는 SQL 구문입니다.

위의 이미지를 기억하시나요? SQL 서버 개체 구조입니다.

데이터베이스는 이렇게 여러 테이블과 데이터베이스 개체를 포함합니다. 먼저 데이터베이스 생성을 진행하면서 설명하겠습니다. SSMS에서 이렇게 새로운 데이터베이스를 생성합니다.

데이터베이스 생성

SSMS 서버 - 데이터베이스 - 새 데이터베이스를 선택합니다. 이어서 sqler_test라는 데이터베이스 이름만 지정합니다.　

옵션 탭에서는 아무것도 건드리지 않았습니다.

파일그룹 탭에서도 아무것도 건드리지 않았습니다. 　

확인을 누르고 잠시 기다리면 이렇게 데이터베이스가 생성됩니다.

간단한 과정으로 새로운 DB를 생성했습니다. DB를 만드는 게 아주 어려운 작업일 것 같았지만 이렇게 간단하게 생성됩니다.

데이터베이스 개체 리뷰

데이터베이스를 생성했으니 이제 sqler_test DB에 어떤 데이터베이스 개체들이 있는지 확인해 보겠습니다.

1. 데이터베이스 다이어그램(Database diagram)

지난 JOIN 강좌에서 짧게 데이터베이스 다이어그램을 사용했습니다. 데이터베이스 테이블과 관계를 다이어그램으로 자동 생성합니다.

2. 테이블(Table)

지난 강좌까지 테이블의 데이터를 조회하고 수정하면서 많이 다루었습니다. SQL 쿼리로도 이미 여러 번 생성했지요. 데이터가 차곡차곡 쌓이는 저장소입니다.

3. 뷰(View)

뷰는 실제 테이블은 아니지만 테이블을 비추고 있는 거울입니다. 짧게 예제 쿼리로 살펴보겠습니다.

USE AdventureWorks;
GO

-- 컬러가 검은색인 제품들로만 표시하는 뷰를 생성
CREATE VIEW BlackProducts
AS
SELECT * FROM Production.Product 
WHERE Color like 'Black';
GO

-- 생성한 뷰 조회
SELECT * FROM BlackProducts;
GO

실제로 데이터가 저장되지는 않고 (여러) 테이블의 결과셋을 정의해 사용 가능합니다. 여러 테이블을 JOIN 한 복잡한 SQL 쿼리를 하나의 논리적인 구조로 유도할 수 있어 데이터 사용자에게 편리한 데이터 조작을 제공합니다. 실제 물리적으로 데이터를 포함하지 않기 때문에 공간을 차지하지 않고, 항상 최신의 테이블 데이터를 그대로 반영합니다.(공간을 사용하는 특수한 뷰도 있습니다)
뷰는 이후 SQLER 강좌에서 좀 더 상세히 진행합니다.

4. 외부 데이터 리소스(External data resource)

SQL Server에서 제공하는 PolyBase 쿼리를 이용해 다양한 외부 데이터베이스나 저장소를 연결해 쿼리 할 수 있습니다.
Azure Storage Account, ADLS(Azure Data Lake Storage), Oracle, Teradata, MongoDB, AWS S3 등을 지원합니다.

-- External Resource 등록 후 AWS의 S3 버킷에 이런 SQL 형태로 쿼리 가능 
SELECT  *
FROM    OPENROWSET
        (   BULK '/<bucket>/<parquet_folder>'
        ,   FORMAT       = 'PARQUET'
        ,   DATA_SOURCE  = 's3_ds'
        ) AS [cc];

☑️ 챗GPT 활용: Data Lake Storage에 대해서 설명해 줘

5. 동의어(Synonym)

동의어는 데이터베이스 개체의 참조 별명입니다. 예를 들어, AdventureWorks 예제 데이터베이스의 AddressType 테이블을 쿼리에서 사용하려면, 아래와 같이 3파트 또는 4파트 쿼리를 수행해야 합니다.

-- SELECT * FROM DB명.스키마명.테이블명  : 3파트
SELECT * FROM AdventureWorks.Person.AddressType;
GO

-- 또는 SELECT * FROM 서버명.DB명.스키마명.테이블명  : 4파트

다른 데이터베이스에서 해당 AddRessType을 호출할 때 이렇게 3파트로 매번 쿼리해야 하는데 만약 데이터베이스 이름이 변경되거나 서버명 등으로 변경되면 그간 사용했던 모든 SQL 쿼리 구문을 변경해야 하는 난감한 상황이 생깁니다.
이때 동의어를 등록해 사용하면 테이블을 바로 참조할 수 있고, 만약 DB명이나 서버가 변경되어도 동의어 매핑만 수정해 주면 됩니다. 

-- SELECT * FROM DB명.스키마명.테이블명  : 3파트 테스트 조회
SELECT * FROM AdventureWorks.Person.AddressType;
GO

-- 다른 DB에서 AdventureWorks DB의 AddressType을 동의어로 사용하기 위해 등록
USE tempdb;
GO

-- 동의어 생성
CREATE SYNONYM MyAddressType  
FOR AdventureWorks.Person.AddressType;
GO

-- 동의어 테이블로 데이터 삽입
INSERT INTO MyAddressType (Name) VALUES ('Test');
GO  

-- 동의어 테이블 테스트
SELECT Name FROM MyAddressType;
GO

프로그래밍 기능

6. 저장 프로시저(Stored Procedure)

저장 프로시저는 하나 이상의 SQL 구문을 실행할 수 있는 데이터베이스 모듈화 개체입니다. SQL 구문을 캡슐화할 수 있어서 데이터베이스 작업과 개발 작업을 격리할 수 있습니다. 또한, SQL Server에서 컴파일되어 저장하므로 빠른 실행 속도와 재사용이 가능합니다. 

간단한 저장 프로시저 예제를 살펴보도록 하겠습니다.

USE AdventureWorks;
GO

-- 저장 프로시저 생성. @ProdID를 파라미터로 받아 1개 제품 정보를 출력.
CREATE PROC ProductInfo @ProdID INT
AS
SELECT * FROM Production.Product
WHERE ProductID = @ProdID;
GO

-- 저장 프로시저 실행
EXEC ProductInfo 316;
GO

프로그래밍 경험이 있다면 함수 호출과 유사하다고 느껴질 거에요. 함수와 매우 유사한 형태이고 입력 파라미터 사용도 가능합니다.
실제 어느 정도 규모 있는 데이터베이스 프로젝트를 수행할 때, 거의 모든 SQL구문을 저장 프로시저로 생성해 진행합니다. 데이터베이스 개발 업무를 하게 되면, 제일 먼저 저장 프로시저와 친해지셔야 합니다.

7. 함수(Function)

SQL Server 함수를 생성하고 재사용할 수 있습니다. 프로그래밍에서 사용하던 함수와 비슷하며 다른 점은 테이블형을 리턴하는 함수를 생성할 수도 있습니다. 아래는 값을 리턴하는 스칼라 함수(Scalar function) 예제입니다.
 

USE AdventureWorks;
GO

-- 10% 할인된 제품 가격을 출력하는 스칼라 함수(Scalar function) 생성
CREATE FUNCTION dbo.fn_ProductDiscount
(@Price decimal(10,2))
RETURNS decimal(10,2)
AS
BEGIN
   RETURN @Price * 0.9
END;
GO

-- 함수로 데이터 조회
SELECT ProductID, Name, ListPrice, dbo.fn_ProductDiscount(ListPrice) as DiscountPrice 
FROM Production.Product 
WHERE ListPrice > 0;
GO

결과
ProductID   Name                       ListPrice     DiscountPrice
----------- -------------------------  --------    ----------
515         ML Mountain Seat Assembly   147.14       132.43
516         HL Mountain Seat Assembly   196.92       177.23
...

이렇게 함수를 생성하고 사용할 수 있습니다.

8. 트리거(Trigger)

트리거는 데이터베이스 테이블에 INSERT/UPDATE/DELETE와 같은 수정 작업이 시작되면 자동으로 실행되는 모듈입니다. 데이터 수정 시 특정 루틴을 자동 실행하거나, 데이터 일관성을 유지하기 위한 로직 등을 트리거로 자동 실행할 수 있습니다. 저장 프로시저와 구문이 유사합니다. 

9. 어셈블리(Assembly)

어셈블리는 T-SQL 함수가 아닌 Microsoft .NET Framework CLR(Common Language Runtime-공용 언어 런타임) 언어(C# 등)로 생성한 함수, 저장 프로시저, 트리거 등을 사용하기 위한 SQL Server의 확장 기능입니다. 예를 들어, 복잡한 암호화 함수를 생성해야 하거나, SQL 기본 구문으로는 원하는 함수 제작에 한계가 있을 때 C# 등으로 DLL을 만들어 SQL Server에 등록하고, SQL 쿼리에서 사용할 수 있습니다.

10. 사용자 정의 데이터 형식(UDT-User Defined data Type)

사용자 정의 데이터 형식을 생성하면, 손쉬운 데이터 유형을 사용할 수 있습니다. 예를 들어, 우리나라의 주민등록번호 같은 14자리 문자열을 하나의 사용자 정의 데이터 형식으로 등록해 사용할 수 있으며 어셈블리(Assembly)를 이용해 사용자 정의 형식을 생성하고 사용할 수 있습니다.

USE AdventureWorks;
GO

-- 사용자 정의 데이터형 UDT(User Defined data Type)
CREATE TYPE KR_SSN  
FROM varchar(14) NOT NULL;
GO

CREATE TABLE Korean_member
(
  Idx INT,
  Name NVARCHAR(12),
  JuminNum KR_SSN  -- UDT 사용
);
GO

-- 데이터 추가
INSERT INTO Korean_member VALUES(1, N'김대우', '900101-1234567');
GO

SELECT * FROM Korean_member;
GO

사용자 정의 테이블 형식(User Defined Table Type) 역시 비슷하게 생성해 사용할 수 있습니다.

11. XML 스키마 컬렉션 (Schema Collection)

SQL Server는 XML 데이터 형식으로 XML 데이터 저장을 지원합니다. XSD 스키마를 따로 저장해 XML 문서와 스키마를 연결할 수 있습니다. 메타정보를 포함하는 스키마를 이용해 XML 문서의 유효성을 검증하거나 데이터베이스에 저장할 때 포맷팅 할 수 있습니다.

12. 체크 제약(CHECK Constraint)과 규칙(RULE) 

주민등록번호나 우편번호 123-456과 같은 데이터를 강제해서 입력받으려면 어떻게 해야 할까요? 이럴 때 CHECK 제약이나 RULE을 사용할 수 있습니다. 
규칙(RULE)은 SQL Server 차기 버전에서는 제거될 예정이니 CHECK 제약을 이용하세요.

--RULE, CHECK 제약 테스트
USE AdventureWorks;
GO

--테이블을 생성합니다.
CREATE TABLE rule_test(
  idx iNT,
  KR_SSN VARCHAR(15)
);
GO

-- 주민등록번호 패턴을 체크하는 예제 RULE 생성(실제로는 애플리케이션에서  체크하세요)
CREATE RULE KR_SSN_rule   
AS  
@value LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]';
GO

--생성한 RULE을 테이블 컬럼에 바인딩
EXEC sp_bindrule 'KR_SSN_rule', 'dbo.rule_test.KR_SSN';
GO

INSERT INTO rule_test VALUES(1, '123456-1234567'); -- 정상
GO

INSERT INTO rule_test VALUES(2, 'A23456-1234567');  -- RULE 위반
GO

오류 메시지
메시지 513, 수준 16, 상태 0, 줄 859
열 삽입이나 업데이트가 이전 CREATE RULE 문에서 설정한 규칙과 충돌하여 문이 종료되었습니다. 데이터베이스 'AdventureWorks', 테이블 'dbo.rule_test', 열 'KR_SSN'에서 충돌이 발생했습니다.
문이 종료되었습니다.

-- CHECK 제약 추가
ALTER TABLE dbo.rule_test   
   ADD CONSTRAINT check_idx   
   CHECK (idx > 0 AND idx < 3);  -- idx 컬럼은 0 초과 3 미만 값을 받음
GO  

INSERT INTO rule_test VALUES(5, '123456-1234567');  -- CHECK 제약 위반
GO

오류 메시지
메시지 547, 수준 16, 상태 0, 줄 871
INSERT 문이 CHECK 제약 조건 "check_idx"과(와) 충돌했습니다. 데이터베이스 "AdventureWorks", 테이블 "dbo.rule_test", column 'idx'에서 충돌이 발생했습니다.
문이 종료되었습니다.

이렇게 둘 다 입력값에 제한을 걸어 데이터 무결성을 높입니다.

그렇다면, CHECK과 RULE의 차이점은 뭘까요? CHECK 제약은 테이블 컬럼에 바인딩되고, RULE은 데이터 유형에 바인딩되는 차이가 있습니다.

13. SQL 계획 지침(Plan Guides)

SQL 구문을 실행하면 SQL Server의 쿼리 최적화기(Query Optimizer)는 테이블 데이터 분포와 통계 정보를 확인해 최적의 쿼리 실행 계획을 결정하고 수행합니다. SSMS의 “쿼리”-”예상 실행 계획 표시”를 수행하거나 컨트롤+L 키를 눌러 SELECT 구문 등을 실행하면 쿼리 실행 계획을 볼 수 있습니다.

권장하지 않지만, 이런 실행 계획을 개발자가 직접 쿼리 실행 힌트(Hint)를 지정해 제어할 수 있습니다. 이때, SQL 계획 지침으로 이런 사용자 지정 실행 계획을 저장하고 재사용할 수 있습니다.

14. 시퀀스(SEQUENCE)

시퀀스는 일련번호와 같은 순차적인 값을 생성하는 데이터베이스 개체입니다. 주로 고유 값을 생성할 때 사용하며, 숫자 또는 날짜 데이터를 시퀀스로 설정합니다. 여기서 잠시 INSERT 구문에서 보았던 IDENTITY와 비슷해 보이는데?라는 생각이 듭니다. 

SEQUENCE와 IDENTITY의 차이

IDENTITY는 테이블 컬럼의 속성으로 증가하는 값을 설정하며, 시퀀스는 데이터베이스 개체로(정확히는 스키마에 바인딩) 독립되어 있습니다. 따라서, 시퀀스는 데이터베이스 전체에서 공유될 수 있고, 여러 테이블에서 시퀀스를 사용해 순차값을 할당할 수 있습니다.

-- 테스트 테이블 생성
CREATE TABLE sequence_test1 (
  member_num INT,
  member_name NVARCHAR(10)
);
GO

-- seq_test 시퀀스 생성
CREATE SEQUENCE seq_test  
    START WITH 1  
    INCREMENT BY 1 ;  
GO 

-- NEXT VALUE FOR로 다음 시퀀스 번호 확인
SELECT NEXT VALUE FOR seq_test;  
GO

-- INSERT 구문에 사용
INSERT INTO sequence_test1(member_num, member_name)
VALUES (NEXT VALUE FOR seq_test, N'김대우');
INSERT INTO sequence_test1(member_num, member_name)
VALUES (NEXT VALUE FOR seq_test, N'박은빈');

-- 테스트 조회
SELECT * FROM sequence_test1;
GO

15. SQL Service Broker

Service Broker는 SQL Server의 비동기 메시지 전달 서비스입니다. 주로, 데이터베이스 애플리케이션 통신에 사용합니다. 비동기 메시징 플랫폼인 Apache Kafka와 비슷하지만 SQL Service Broker는 좀 더 데이터베이스 간 통신에 특화되어 있습니다. 

☑️ 챗GPT 활용: Apache Kafka에 대해서 설명해 줘

SQLER의 강좌에서는 진행하지 않을 예정이며 SQL 팁 등으로 풀어서 정리할 예정입니다.

16. SQL Server 파티션

대용량 데이터를 다룰 때 사용되는 파티션은 테이블이나 인덱스를 분할해 입출력하는 기능입니다. 하나의 데이터베이스를 여러 개의 파일 그룹(File Group)으로 생성하고, 파일 그룹을 물리적으로 다른 디스크에 저장합니다. 테이블의 데이터를 분할해 개별 파일 그룹에 위치시키면, 데이터 입출력 과정이 물리적으로 다른 디스크에서 분산 처리되기 때문에, 더 빠른 속도로 데이터베이스 작업을 수행할 수 있습니다.

데이터베이스 파티셔닝 - 이미지: SQLHack

데이터 파티셔닝 - Range를 이용한 수평 분할

17. 전체 텍스트 검색(Full Text Search)

전체 텍스트 검색은 SQL 데이터베이스의 텍스트 데이터를 색인하여 검색하는 텍스트 기반 검색 기능입니다. 일반적인 SQL의 INDEX와 다르게 검색엔진처럼 텍스트 검색에 최적화되어 있으며, 한글 형태소 기반 색인이 이루어지고, 영어 등 몇몇 언어는 자연어 검색(Natural Language Search)도 지원합니다. SQL에 저장된 대량의 텍스트 데이터를 효율적으로 검색할 때 사용합니다.

18. SQL 로그인(Login), 사용자(User), 역할(Role)

SQL 로그인은 SQL Server에 접근하기 위한 계정입니다. 아이디와 패스워드를 넣고 인증할 수 있습니다. 역할은 사용자들의 그룹에 속하는 권한입니다. 예를 들어, 여러 데이터베이스 권한을 사용자 또는 역할에 부여할 수 있습니다. 역할에 권한을 부여하고, 역할에 속하는 사용자를 묶어서 사용자들의 권한을 쉽게 관리할 수 있습니다. 사용자는 데이터베이스 권한을 가지고 작업을 수행하는 단위입니다. 

☑️ 챗GPT 활용: SQL Server 로그인(Login), 사용자(User), 역할(Role), 권한(Permission)에 대해서 설명해 줘

19. 스키마(Schema)

스키마는 개체를 별도의 네임스페이스로 그룹화할 수 있는 데이터베이스 개체의 명명된 컨테이너입니다. 프로그래밍 경험이 있다면 네임스페이스에 익숙할 겁니다. 스키마는 데이터베이스 내에서 테이블과 같은 개체가 고유한 이름을 가지게 하는 식별자입니다.

20. SQL 인증서(Certificate)와 암호화(Encryption)

SQL 인증서는 공개키가 포함된 디지털 서명입니다. 외부에서 가져와 사용할 수도 있고, SQL Server에서 생성할 수도 있습니다. SQL은 대칭키(Symmetric Key)와 비대칭키(Asymmetric Key) 암호화를 제공합니다. 이 암호화에 사용되는 요소들이 데이터베이스에 포함됩니다.

☑️ 챗GPT 활용: 대칭키(Symmetric Key), 비대칭키(Asymmetric Key) 암호화 방식을 설명해 줘

이렇게 간략히 데이터베이스 개체들에 대해서 살펴봤습니다. 프로그래밍 경험이 있어도 거의 대부분 처음 접하는 개체에 이름도 생소할 거예요.

SQLER 강좌에서 예제로 차근차근 진행하니 걱정 마시고요. 지금은 이런 게 있구나 정도만 익히고 다음으로 넘어가도 충분합니다.
 

SQL 강좌 책 구매

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

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