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; 

msdb Veri Tabanı - System Database

Giriş

Açıklaması şöyle

In SQL Server environments, system databases play crucial roles in ensuring smooth and reliable database operations. Among these, the msdb database is critical as it handles a variety of operational tasks, including job scheduling via SQL Server Agent, alert management, database mail configuration, and backup and restore history tracking. These functions are essential for automating routine maintenance, monitoring system health, and managing administrative workflows.

Açıklaması şöyle

msdb is a system database that stores metadata and configuration for:

• SQL Server Agent jobs
• 
  
• Backup and restore history
• 
  
• Database mail
• 
  
• Service Broker
• 
  
• Log shipping
• 
  
• Maintenance plans
• 
  
• Alerts and operators
• 
  
• SSIS package storage (when using MSDB storage)

Sütun Tipleri - REAL

Giriş

Açıklaması şöyle

- Equivalent to a 32-bit floating-point number (single precision)

- IEEE standard floating-point value (7-digit precision)

- Storage size: 4 bytes

- Synonym: FLOAT(24)

ORDER BY + OFFSET + FETCH - Sayfalama İçindir

Örnek

SQL Server 2012 ve sonrasında şöyle yaparız

SELECT * FROM Employees ORDER BY EmployeeID ASC
OFFSET 10 ROWS         -- Skip the first 10 rows
FETCH NEXT 5 ROWS ONLY; -- Fetch the next 5 rows

Örnek

SQL Server 2008 ve öncesinde şöyle yapılır

SELECT TOP 5 *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY EmployeeID ASC) AS RowNum
    FROM Employees
) AS SubQuery
WHERE RowNum > 10;

DROP DATABASE

Örnek

Force drop için şöyle yaparız

-- Set the database to SINGLE_USER mode with ROLLBACK IMMEDIATE to kill all connections
ALTER DATABASE my_new_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Drop the database
DROP DATABASE my_new_db;

Sütun Tipleri - TINYINT - 1 BYTE - UNSIGNED

Giriş

0 - 255 arasında değer saklar

CREATE NONCLUSTERED INDEX

Örnek

Şöyle yaparız

CREATE NONCLUSTERED INDEX IDX_MYINDEX

ON Orders(OrderDate, ProductID);