Table Partitioning

Giriş

Partition Funciton

Örnek

Başlangıçta şöyle yaparız

CREATE PARTITION FUNCTION pfAudit(datetime2)
AS RANGE RIGHT FOR VALUES
(
    '2025-01-01',
    '2026-01-01',
    '2027-01-01'
);

Split ile yeni range eklemek için sonra şöyle yaparız

ALTER PARTITION FUNCTION pfAudit()
SPLIT RANGE ('2028-01-01');

2025 artık arşive taşınınca şöyle yaparız. MERGE RANGE ile 2025 silinir.

ALTER PARTITION FUNCTION pfAudit()
MERGE RANGE ('2026-01-01');

1. Data Delete

The idea is:

Move old data out of the active table.

Then TRUNCATE the old partition/table instead of doing a huge DELETE.

This is much faster because TRUNCATE is minimally logged and doesn't scan rows.

Örnek

CREATE TABLE AuditLog (
    Id bigint IDENTITY,
    CreatedDate datetime2 NOT NULL,
    Message nvarchar(200)
);  

Step 1: Create partition function

CREATE PARTITION FUNCTION pfAuditLog(datetime2)
AS RANGE RIGHT FOR VALUES
(
    '2024-01-01',
    '2025-01-01',
    '2026-01-01'
);

Step 2: Switch out old partition

ALTER TABLE AuditLog
SWITCH PARTITION 1
TO AuditLog_Archive;

Step 3: Truncate archive

TRUNCATE TABLE AuditLog_Archive;

2. Data Loading 

Data Loading yaparken kullanılabilecek yöntemler şöyle

1. bcp

2. BULK INSERT

3. SQL Server Integration Services (SSIS)

Açıklaması şöyle

Table partitioning is one of the most powerful strategies for improving load performance while maintaining data availability. At a financial services company processing billions of monthly transactions, implementing partition switching transformed their loading process. Instead of inserting directly into the main table and blocking queries, we loaded an identical staging table and instantly switched it into the partitioned main table: 

Örnek

Şöyle yaparız

-- Create staging table with same structure as target partition
SELECT * INTO Sales.Transactions_Staging
FROM Sales.Transactions
WHERE 1 = 0;

-- Load staging table (much faster than loading production table)
BULK INSERT Sales.Transactions_Staging
FROM '\\FileServer\Imports\NewTransactions.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

-- Switch staging table into partition in an instant operation
ALTER TABLE Sales.Transactions
SWITCH PARTITION 12 TO Sales.Transactions_Staging;