MSSQL에서 파티션 테이블 구성하기 파티션 테이블 만드는 법

파티션 테이블이란?

하나의 거대한 테이블을 **파티션 키(Partition Key)**를 기준으로 논리적인 여러개의 작은 단위(파티션)로 나누어 저장하는 기술입니다. 이를 통해 대용량 데이터의 을 크게 향상시킬 수 있습니다.성능, 관리, 가용성

---

파티션 테이블 구성 순서 및 SQL 스크립트

파티션 테이블은 다음 4단계에 걸쳐 순서대로 생성해야 합니다.

예시 시나리오: Sales히스토리 테이블의 (주문일자)를 기준으로 로 파티션을 구성하는 경우 주문날짜 연도별

1단계: 파티션을 저장할 파일 그룹(Filegroup) 및 파일 생성 (선택사항이지만 강력히 권장)

각 파티션을 물리적으로 다른 파일 그룹에 저장하면 I/O 성능을 높이고 백업/복원 등의 관리를 파티션 단위로 수행할 수 있어 매우 효과적입니다.

생성된 SQL

-- 데이터베이스에 파일 그룹 추가
ALTER DATABASE [YourDatabaseName] ADD FILEGROUP FG_Sales_2022;
ALTER DATABASE [YourDatabaseName] ADD FILEGROUP FG_Sales_2023;
ALTER DATABASE [YourDatabaseName] ADD FILEGROUP FG_Sales_2024;
ALTER DATABASE [YourDatabaseName] ADD FILEGROUP FG_Sales_Future; -- 미래 데이터를 위한 파일 그룹

-- 각 파일 그룹에 데이터 파일(.ndf) 추가
-- 실제 경로(FILENAME)는 서버 환경에 맞게 수정해야 합니다.
ALTER DATABASE [YourDatabaseName]
ADD FILE
(
    NAME = N'Sales_2022',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Sales_2022.ndf',
    SIZE = 5GB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 512MB
)
TO FILEGROUP [FG_Sales_2022];

ALTER DATABASE [YourDatabaseName]
ADD FILE
(
    NAME = N'Sales_2023',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Sales_2023.ndf',
    SIZE = 5GB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 512MB
)
TO FILEGROUP [FG_Sales_2023];

ALTER DATABASE [YourDatabaseName]
ADD FILE
(
    NAME = N'Sales_2024',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Sales_2024.ndf',
    SIZE = 5GB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 512MB
)
TO FILEGROUP [FG_Sales_2024];

ALTER DATABASE [YourDatabaseName]
ADD FILE
(
    NAME = N'Sales_Future',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Sales_Future.ndf',
    SIZE = 5GB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 512MB
)
TO FILEGROUP [FG_Sales_Future];

content_copydownload

코드를 주의해서 사용합니다.SQL (영문)

2단계: 파티션 함수(Partition Function) 생성

데이터를 어떻게 나눌지 **경계값(Boundary Value)**을 정의합니다.

• 범위 오른쪽: 경계값을 기준으로 파티션에 값이 포함됩니다. (일반적으로 날짜 데이터에 사용, 더 직관적)오른쪽
• 왼쪽 범위: 경계값을 기준으로 파티션에 값이 포함됩니다.왼쪽

생성된 SQL

-- 'pf_SalesByYear'라는 이름의 파티션 함수 생성
-- OrderDate(datetime)를 기준으로 파티션을 나눔
CREATE PARTITION FUNCTION pf_SalesByYear (datetime)
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2024-01-01', '2025-01-01');

/*
- RANGE RIGHT 설명:
  - 파티션 1: OrderDate < '2023-01-01' (2022년 데이터)
  - 파티션 2: '2023-01-01' <= OrderDate < '2024-01-01' (2023년 데이터)
  - 파티션 3: '2024-01-01' <= OrderDate < '2025-01-01' (2024년 데이터)
  - 파티션 4: OrderDate >= '2025-01-01' (미래 데이터)
*/

content_copydownload

코드를 주의해서 사용합니다.SQL (영문)

3단계: 파티션 구성표(Partition Scheme) 생성

파티션 함수에 의해 나뉜 각 파티션을 매핑(연결)합니다.어떤 파일 그룹에 저장할지

생성된 SQL

-- 'ps_SalesByYear'라는 이름의 파티션 구성표 생성
-- pf_SalesByYear 함수와 파일 그룹을 연결
CREATE PARTITION SCHEME ps_SalesByYear
AS PARTITION pf_SalesByYear
TO (FG_Sales_2022, FG_Sales_2023, FG_Sales_2024, FG_Sales_Future);

content_copydownload

코드를 주의해서 사용합니다.SQL (영문)

• 받는 사람 절에 파일 그룹을 순서대로 지정합니다. 파티션 함수에서 정의한 경계값 순서와 일치해야 합니다.

4단계: 파티션 테이블 생성

실제 테이블을 생성하면서 와 를 지정합니다.파티션 구성표파티션 키

생성된 SQL

CREATE TABLE dbo.SalesHistory
(
    SalesID bigint IDENTITY(1,1) NOT NULL,
    OrderID int NOT NULL,
    OrderDate datetime NOT NULL,
    ProductCode nvarchar(50) NOT NULL,
    Quantity int NOT NULL,
    UnitPrice money NOT NULL
) ON ps_SalesByYear(OrderDate); -- 파티션 구성표와 파티션 키(OrderDate) 지정

-- 중요: 클러스터형 인덱스도 파티션 구성표에 맞춰 생성해야 성능이 극대화됩니다.
CREATE CLUSTERED INDEX CIX_SalesHistory_OrderDate
ON dbo.SalesHistory(OrderDate)
ON ps_SalesByYear(OrderDate);

-- 비클러스터형 인덱스도 필요하다면 파티션 정렬(Aligned)하는 것이 좋습니다.
CREATE NONCLUSTERED INDEX NCIX_SalesHistory_ProductCode
ON dbo.SalesHistory(ProductCode)
ON ps_SalesByYear(OrderDate);

content_copydownload

코드를 주의해서 사용합니다.SQL (영문)

---

가장 효과적인 파티션 구성 방법

단순히 테이블을 나누는 것을 넘어, 파티셔닝의 효과를 극대화하기 위한 핵심 전략은 다음과 같습니다.

1. 최적의 파티션 키(Partition Key) 선택

• 조회 조건(WHERE절)에 가장 빈번하게 사용되는 컬럼을 선택해야 합니다. 이렇게 해야 쿼리 실행 시 불필요한 파티션을 읽지 않고 필요한 파티션만 스캔하는 **파티션 제거(Partition Elimination)**가 발생하여 성능이 크게 향상됩니다.
• 날짜(datetime), 연월(yyyymm) 등 데이터의 논리적 그룹화가 가능한 컬럼이 이상적입니다.

2. 인덱스 정렬(Index Alignment)

• 클러스터형 인덱스와 모든 비클러스터형 인덱스를 테이블의 파티션 구성표에 맞춰 생성하는 것을 '인덱스 정렬'이라고 합니다.
• 인덱스가 정렬되면, SQL Server가 인덱스를 통해 데이터를 찾을 때도 파티션 제거의 혜택을 받을 수 있습니다.
• 정렬하지 않으면 특정 파티션의 데이터를 조회하더라도 모든 파티션의 인덱스를 스캔해야 할 수 있어 성능이 저하됩니다.

3. 슬라이딩 윈도우(Sliding Window) 기법 활용

이는 이며 파티셔닝의 꽃이라고 할 수 있습니다.시간 기반 데이터(시계열 데이터)를 관리하는 가장 효과적인 방법

개념:
오래된 데이터는 보관(Archive)하고 새로운 데이터를 위한 공간을 만드는 작업을 가 아닌 **파티션 전환(SWITCH)**을 통해 수행하는 기법입니다. 는 로그를 많이 발생시키고 매우 느리지만, 는 메타데이터 작업이라 거의 즉시 완료됩니다.삭제하다삭제하다스위치

예시: 2025년 데이터를 위한 새 파티션 추가 및 2022년 데이터 보관

생성된 SQL

-- 1. 2026년 데이터를 받을 새 파티션 경계값 추가 (SPLIT)
-- 먼저 다음 파티션이 사용할 파일 그룹을 지정해야 합니다.
ALTER PARTITION SCHEME ps_SalesByYear NEXT USED FG_Sales_Future;

-- '2026-01-01'을 경계로 파티션을 분할합니다.
ALTER PARTITION FUNCTION pf_SalesByYear() SPLIT RANGE ('2026-01-01');
-- 결과: 2025년 데이터는 FG_Sales_Future를 사용하게 되고,
-- 2026-01-01 이후 데이터는 그 다음 파티션에 할당됩니다.


-- 2. 가장 오래된 2022년 데이터 보관 (SWITCH OUT)
-- 원본 테이블과 동일한 구조의 보관용 테이블을 만듭니다. (반드시 동일한 파일 그룹에)
CREATE TABLE dbo.SalesHistory_Archive_2022
(
    -- 컬럼은 SalesHistory와 완벽히 동일해야 함
    SalesID bigint NOT NULL,
    OrderID int NOT NULL,
    OrderDate datetime NOT NULL,
    ProductCode nvarchar(50) NOT NULL,
    Quantity int NOT NULL,
    UnitPrice money NOT NULL
) ON FG_Sales_2022; -- 보관할 파티션과 동일한 파일 그룹 지정

-- SalesHistory의 첫 번째 파티션(2022년 데이터)을 보관용 테이블로 전환
ALTER TABLE dbo.SalesHistory
SWITCH PARTITION 1 TO dbo.SalesHistory_Archive_2022;
-- 이 작업은 순식간에 끝납니다. 데이터 삭제가 아닌 포인터 변경입니다.

-- 3. 불필요해진 파티션 경계값 제거 (MERGE)
ALTER PARTITION FUNCTION pf_SalesByYear() MERGE RANGE ('2023-01-01');
-- 이제 가장 오래된 파티션은 2023년 데이터가 됩니다.

content_copydownload

코드를 주의해서 사용합니다.SQL (영문)

요약

1. 순서: 파일 그룹 생성 → 파티션 함수 생성 → 파티션 구성표 생성 → 테이블 생성
2. 가장 효과적인 방법:
   • 최적의 파티션 키를 선택하여 효과를 극대화하세요.파티션 제거
   • 모든 인덱스를 파티션에 정렬하여 쿼리 성능을 보장하세요.
   • 시계열 데이터라면 반드시 기법을 도입하여 데이터 추가/삭제를 효율적으로 관리하세요. 이것이 파티셔닝을 사용하는 가장 큰 이유 중 하나입니다.슬라이딩 윈도우

이 가이드를 따라 구성하시면 대용량 테이블을 매우 효과적으로 관리하고 성능을 크게 향상시킬 수 있을 것입니다.