Table Partitioning

Giriş

Data Loding 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;