MS-SQL LOB테이블 생성

SQL Server LOB(Large Object) 테이블

개요

1. LOB 데이터 타입 종류

LOB 데이터 타입 종류
데이터 타입	설명	최대 크기
VARCHAR(MAX)	가변 길이 문자 데이터	2GB
NVARCHAR(MAX)	가변 길이 유니코드 문자	2GB
VARBINARY(MAX)	가변 길이 이진 데이터	2GB
TEXT (구식)	문자 데이터 (사용 지양)	2GB
NTEXT (구식)	유니코드 문자 (사용 지양)	2GB
IMAGE (구식)	이진 데이터 (사용 지양)	2GB
XML	XML 데이터	2GB

> ⚠️ TEXT, NTEXT, IMAGE는 **Deprecated**(사용 중단 예정)이므로 VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) 사용을 권장합니다.

2. 기본 LOB 테이블 생성

CREATE TABLE Documents (
    DocumentID INT IDENTITY(1,1) PRIMARY KEY,
    Title NVARCHAR(200) NOT NULL,
    Content NVARCHAR(MAX),           -- 대용량 텍스트
    FileData VARBINARY(MAX),         -- 대용량 이진 데이터 (파일)
    Summary VARCHAR(MAX),            -- 대용량 텍스트
    Metadata XML,                    -- XML 데이터
    CreatedDate DATETIME DEFAULT GETDATE()
);

3. 파일 저장용 테이블 예제

CREATE TABLE FileStorage (
    FileID INT IDENTITY(1,1) PRIMARY KEY,
    FileName NVARCHAR(255) NOT NULL,
    FileExtension NVARCHAR(10),
    FileSize BIGINT,
    FileContent VARBINARY(MAX) NOT NULL,   -- 실제 파일 바이너리 저장
    ContentType NVARCHAR(100),
    UploadedBy NVARCHAR(100),
    UploadedDate DATETIME DEFAULT GETDATE()
);

파일 삽입 예제

INSERT INTO FileStorage (FileName, FileExtension, FileSize, FileContent, ContentType)
SELECT 
    'sample.pdf',
    '.pdf',
    100000,
    BulkColumn,
    'application/pdf'
FROM OPENROWSET(BULK 'C:\Files\sample.pdf', SINGLE_BLOB) AS FileData;

---

4. FILESTREAM을 사용한 LOB 저장 (대용량 파일 최적화)

FILESTREAM은 파일시스템을 이용해 대용량 이진 데이터를 효율적으로 저장합니다.

4-1. FILESTREAM 활성화 (서버 레벨)

-- SQL Server 구성 관리자에서도 활성화 필요
EXEC sp_configure filestream_access_level, 2;
RECONFIGURE;

4-2. FILESTREAM 지원 데이터베이스 생성

CREATE DATABASE FileStreamDB
ON PRIMARY 
    (NAME = FileStreamDB_Data, 
     FILENAME = 'C:\Data\FileStreamDB.mdf'),
FILEGROUP FileStreamGroup CONTAINS FILESTREAM
    (NAME = FileStreamDB_Filestream, 
     FILENAME = 'C:\Data\FileStreamDB_Filestream')
LOG ON 
    (NAME = FileStreamDB_Log, 
     FILENAME = 'C:\Data\FileStreamDB.ldf');

4-3. FILESTREAM 테이블 생성

USE FileStreamDB;
GO

CREATE TABLE FileStreamDocuments (
    DocumentID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWID(),
    FileName NVARCHAR(255),
    FileData VARBINARY(MAX) FILESTREAM,   -- FILESTREAM 컬럼
    CreatedDate DATETIME DEFAULT GETDATE()
);

> 💡 FILESTREAM 컬럼은 반드시 테이블에 `ROWGUIDCOL` 속성을 가진 UNIQUEIDENTIFIER 컬럼이 있어야 합니다.

---

5. LOB 컬럼 조회 및 처리

5-1. 텍스트 LOB 삽입/조회

INSERT INTO Documents (Title, Content)
VALUES ('긴 문서', REPLICATE('가나다라마바사', 10000));  -- 대용량 텍스트 생성

SELECT DocumentID, Title, LEN(Content) AS ContentLength
FROM Documents;

5-2. 이진 파일 데이터 조회 (내보내기)

-- 특정 행의 이진 데이터를 파일로 저장 (SSMS에서 결과를 파일로 저장)
SELECT FileContent 
FROM FileStorage
WHERE FileID = 1;

5-3. TEXTPTR 및 UPDATETEXT (구식, TEXT 타입에서 사용)

-- 구식 방식은 잘 사용하지 않음. MAX 타입은 일반 UPDATE로 처리 가능
UPDATE Documents
SET Content = Content + N' 추가 텍스트'
WHERE DocumentID = 1;

---

6. LOB 데이터 관련 성능/저장 옵션

6-1. 테이블 저장 옵션 (LOB 데이터 별도 파일 그룹 지정)

CREATE TABLE LargeDataTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Content NVARCHAR(MAX)
)
TEXTIMAGE_ON [SECONDARY];   -- LOB 데이터를 SECONDARY 파일 그룹에 저장

6-2. LOB 압축 (페이지/행 압축)

CREATE TABLE CompressedLOB (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    LargeText NVARCHAR(MAX)
)
WITH (DATA_COMPRESSION = PAGE);

> 💡 참고: 압축은 LOB 데이터 자체가 아니라 행 내 저장된(인라인) 부분에 적용됩니다. LOB이 별도 페이지에 저장되면 압축 대상에서 제외될 수 있습니다.

---

실전 예제: 게시판 첨부파일 테이블

CREATE TABLE Board (
    BoardID INT IDENTITY(1,1) PRIMARY KEY,
    Title NVARCHAR(200) NOT NULL,
    Content NVARCHAR(MAX),
    WriterID NVARCHAR(50),
    CreatedDate DATETIME DEFAULT GETDATE()
);

CREATE TABLE BoardAttachment (
    AttachmentID INT IDENTITY(1,1) PRIMARY KEY,
    BoardID INT NOT NULL,
    FileName NVARCHAR(255) NOT NULL,
    FileData VARBINARY(MAX) NOT NULL,
    FileSize BIGINT,
    UploadedDate DATETIME DEFAULT GETDATE(),
    CONSTRAINT FK_Board FOREIGN KEY (BoardID)
        REFERENCES Board(BoardID) ON DELETE CASCADE
);

데이터 삽입 및 조회

-- 게시글 삽입
INSERT INTO Board (Title, Content, WriterID)
VALUES ('공지사항', N'매우 긴 게시글 내용...', 'admin');

-- 첨부파일 삽입
INSERT INTO BoardAttachment (BoardID, FileName, FileData, FileSize)
SELECT 
    1,
    'notice.docx',
    BulkColumn,
    DATALENGTH(BulkColumn)
FROM OPENROWSET(BULK 'C:\Files\notice.docx', SINGLE_BLOB) AS Doc;

-- 게시글과 첨부파일 조회
SELECT b.Title, a.FileName, a.FileSize
FROM Board b
INNER JOIN BoardAttachment a ON b.BoardID = a.BoardID
WHERE b.BoardID = 1;

---

💡 LOB 사용 시 주의사항

**VARCHAR(MAX)/NVARCHAR(MAX)/VARBINARY(MAX)** 사용 권장 (TEXT/NTEXT/IMAGE는 지양)
대용량 파일은 **FILESTREAM** 또는 **파일시스템 + 경로 저장 방식** 고려
LOB 컬럼이 많으면 **백업/복구 시간 증가**
자주 변경되지 않는 대용량 데이터는 **읽기 전용 파일그룹** 활용 고려
애플리케이션에서 스트리밍 방식으로 처리하면 메모리 효율적