Developer's Catalog

データベース

Microsoft SQL Server

概要

Microsoft SQL Serverは、マイクロソフトが開発・提供する企業向けリレーショナルデータベース管理システムです。Windows環境との高い親和性、強力な統合開発環境、包括的なビジネスインテリジェンス機能を特徴とし、中小企業から大企業まで幅広く利用されています。

詳細

SQL Serverは1989年に初版がリリースされ、継続的な進化により現在では以下の特徴を持つ統合データプラットフォームとなっています。

主要特徴

• Always On Availability Groups: 高可用性とディザスタリカバリ
• In-Memory Technologies: In-Memory OLTP（Hekaton）と列ストアインデックス
• Intelligent Query Processing: AI駆動のクエリ最適化
• Azure Integration: ハイブリッドクラウド機能とAzure SQL Database
• Stretch Database: ホットとコールドデータの自動階層化
• PolyBase: 外部データソースとの統合
• JSON Support: ネイティブJSONサポート
• R and Python Integration: 高度な分析とML機能
• Graph Database: グラフデータモデルのサポート

エディション

• Express: 軽量で無料の開発用エディション（10GB制限）
• Standard: 中小企業向け標準機能版
• Enterprise: エンタープライズ向け全機能版
• Developer: 開発者向け無料版（Enterprise機能付き）
• Web: Web アプリケーション向け
• Azure SQL Database: フルマネージドクラウド版

統合コンポーネント

• SQL Server Reporting Services (SSRS): レポート作成・配布
• SQL Server Integration Services (SSIS): ETL/データ統合
• SQL Server Analysis Services (SSAS): OLAP・データマイニング
• SQL Server Master Data Services (MDS): マスターデータ管理

メリット・デメリット

メリット

• Windows統合: Active Directory認証、Windows Services統合
• 豊富な開発ツール: SQL Server Management Studio (SSMS)、Visual Studio統合
• 包括的なBI機能: SSRS、SSIS、SSAS による完全なBI ソリューション
• 高い可用性: Always On、フェイルオーバークラスタリング
• Azure統合: シームレスなクラウド移行とハイブリッド構成
• 強力なセキュリティ: 行レベルセキュリティ、動的データマスキング
• 優秀なパフォーマンス: In-Memory技術と自動チューニング
• Microsoft サポート: 包括的な技術サポートとドキュメント

デメリット

• Windows依存: LinuxサポートはあるがWindows中心の設計
• ライセンスコスト: コア単位の高額なライセンス体系
• リソース消費: 大量のメモリとCPUを必要とする
• ベンダーロックイン: Microsoft テクノロジーエコシステムへの依存
• 学習コスト: T-SQL固有の機能習得が必要
• 仮想化制限: 一部エディションで仮想化制約

主要リンク

• 公式サイト
• SQL Server Documentation
• SQL Server Downloads
• Azure SQL Database

書き方の例

インストール・セットアップ

# SQL Server 2022 Expressのダウンロードとインストール
# https://www.microsoft.com/sql-server/sql-server-downloads

# サイレントインストール例
.\SQLEXPR_x64_JPN.exe /ACTION=Install /FEATURES=SQL /INSTANCENAME=SQLEXPRESS /SECURITYMODE=SQL /SAPWD="StrongPassword123!" /IAcceptSQLServerLicenseTerms

# Docker Linuxコンテナ
docker pull mcr.microsoft.com/mssql/server:2022-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=StrongPassword123!" \
  -p 1433:1433 --name sql-server \
  mcr.microsoft.com/mssql/server:2022-latest

# サービス確認
Get-Service MSSQLSERVER
net start MSSQLSERVER

# SQL Server Configuration Manager
# Services → SQL Server (MSSQLSERVER) → Properties

基本操作（CRUD）

-- データベース作成
CREATE DATABASE CompanyDB
ON (
    NAME = 'CompanyDB',
    FILENAME = 'C:\Data\CompanyDB.mdf',
    SIZE = 100MB,
    MAXSIZE = 1GB,
    FILEGROWTH = 10MB
)
LOG ON (
    NAME = 'CompanyDB_Log',
    FILENAME = 'C:\Data\CompanyDB.ldf',
    SIZE = 10MB,
    FILEGROWTH = 10%
);

USE CompanyDB;

-- テーブル作成
CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) UNIQUE,
    HireDate DATETIME2 DEFAULT GETDATE(),
    Salary DECIMAL(10,2),
    DepartmentID INT,
    IsActive BIT DEFAULT 1
);

-- データ挿入
INSERT INTO Employees (FirstName, LastName, Email, Salary, DepartmentID)
VALUES 
    (N'太郎', N'田中', '[email protected]', 75000.00, 1),
    (N'花子', N'佐藤', '[email protected]', 65000.00, 2);

-- データ選択
SELECT 
    EmployeeID,
    FirstName + ' ' + LastName AS FullName,
    Email,
    FORMAT(HireDate, 'yyyy/MM/dd') AS HireDateFormatted,
    FORMAT(Salary, 'C', 'ja-JP') AS SalaryFormatted
FROM Employees
WHERE IsActive = 1;

-- データ更新
UPDATE Employees 
SET Salary = Salary * 1.1 
WHERE HireDate < DATEADD(YEAR, -1, GETDATE());

-- データ削除（論理削除）
UPDATE Employees SET IsActive = 0 WHERE EmployeeID = 2;

データモデリング

-- 外部キー制約付きテーブル設計
CREATE TABLE Departments (
    DepartmentID INT IDENTITY(1,1) PRIMARY KEY,
    DepartmentName NVARCHAR(100) NOT NULL,
    ManagerID INT,
    Budget MONEY DEFAULT 0
);

ALTER TABLE Employees
ADD CONSTRAINT FK_Employees_Department
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

-- JSON列の使用
CREATE TABLE CustomerProfiles (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(100),
    ProfileData NVARCHAR(MAX) CHECK (ISJSON(ProfileData) = 1),
    Preferences NVARCHAR(MAX) CHECK (ISJSON(Preferences) = 1)
);

INSERT INTO CustomerProfiles (CustomerName, ProfileData, Preferences)
VALUES (
    N'田中太郎',
    N'{"age": 30, "city": "Tokyo", "skills": ["C#", "SQL Server"]}',
    N'{"newsletter": true, "language": "ja"}'
);

-- 計算列とインデックス
ALTER TABLE CustomerProfiles
ADD Age AS JSON_VALUE(ProfileData, '$.age');

CREATE INDEX IX_CustomerProfiles_Age ON CustomerProfiles(Age);

-- テンポラルテーブル（履歴管理）
CREATE TABLE Products (
    ProductID INT IDENTITY(1,1) PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    Price MONEY,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON);

インデックス・最適化

-- インデックス作成
CREATE NONCLUSTERED INDEX IX_Employees_Department 
ON Employees(DepartmentID) INCLUDE (FirstName, LastName);

CREATE NONCLUSTERED INDEX IX_Employees_Email 
ON Employees(Email) WHERE IsActive = 1;

-- カラムストアインデックス
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Sales_Columnstore
ON Sales (ProductID, CustomerID, SaleDate, Amount);

-- フルテキストインデックス
CREATE FULLTEXT CATALOG EmployeeFullTextCatalog;

CREATE FULLTEXT INDEX ON Employees(FirstName, LastName)
KEY INDEX PK__Employee__7AD04F11A11A1A1A
ON EmployeeFullTextCatalog;

SELECT * FROM Employees 
WHERE CONTAINS((FirstName, LastName), '"田中*"');

-- 実行プラン分析
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- 実際の実行プランを表示
SET SHOWPLAN_ALL ON;
SELECT * FROM Employees WHERE DepartmentID = 1;
SET SHOWPLAN_ALL OFF;

-- インデックス使用状況
SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    s.user_seeks, s.user_scans, s.user_lookups,
    s.user_updates
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN sys.tables t ON i.object_id = t.object_id;

実用例

-- ストアドプロシージャ（T-SQL）
CREATE PROCEDURE sp_CalculateBonus
    @DepartmentID INT,
    @BonusRate DECIMAL(5,4) = 0.10
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @TotalBonus MONEY = 0;
    
    UPDATE Employees 
    SET Bonus = Salary * @BonusRate
    WHERE DepartmentID = @DepartmentID AND IsActive = 1;
    
    SELECT @TotalBonus = SUM(Bonus)
    FROM Employees 
    WHERE DepartmentID = @DepartmentID AND IsActive = 1;
    
    PRINT 'Total bonus allocated: ' + FORMAT(@TotalBonus, 'C', 'ja-JP');
    
    SELECT 
        EmployeeID, 
        FirstName + ' ' + LastName AS FullName,
        FORMAT(Salary, 'C', 'ja-JP') AS Salary,
        FORMAT(Bonus, 'C', 'ja-JP') AS Bonus
    FROM Employees 
    WHERE DepartmentID = @DepartmentID AND IsActive = 1;
END;

EXEC sp_CalculateBonus @DepartmentID = 1, @BonusRate = 0.15;

-- ユーザー定義関数
CREATE FUNCTION dbo.GetEmployeeAge(@BirthDate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE()) - 
           CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @BirthDate, GETDATE()), @BirthDate) > GETDATE()
                THEN 1 ELSE 0 END;
END;

-- トリガー
CREATE TRIGGER tr_EmployeeAudit
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO EmployeeAudit (
        EmployeeID, ChangeDate, OldSalary, NewSalary, ChangedBy
    )
    SELECT 
        i.EmployeeID, 
        GETDATE(), 
        d.Salary, 
        i.Salary, 
        SUSER_SNAME()
    FROM inserted i
    INNER JOIN deleted d ON i.EmployeeID = d.EmployeeID
    WHERE i.Salary <> d.Salary;
END;

-- Common Table Expression (CTE)
WITH DepartmentStats AS (
    SELECT 
        d.DepartmentName,
        COUNT(e.EmployeeID) AS EmployeeCount,
        AVG(e.Salary) AS AvgSalary,
        MAX(e.Salary) AS MaxSalary
    FROM Departments d
    LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID
    WHERE e.IsActive = 1
    GROUP BY d.DepartmentID, d.DepartmentName
)
SELECT * FROM DepartmentStats
WHERE EmployeeCount > 0
ORDER BY AvgSalary DESC;

ベストプラクティス

-- セキュリティ設定
-- 行レベルセキュリティ
CREATE FUNCTION dbo.fn_securitypredicate(@DepartmentID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @DepartmentID = CAST(SESSION_CONTEXT(N'DepartmentID') AS INT);

CREATE SECURITY POLICY DepartmentSecurityPolicy
ADD FILTER PREDICATE dbo.fn_securitypredicate(DepartmentID) ON dbo.Employees
WITH (STATE = ON);

-- 動的データマスキング
ALTER TABLE Employees
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

-- 透明なデータ暗号化 (TDE)
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;

ALTER DATABASE CompanyDB SET ENCRYPTION ON;

-- バックアップとリストア
-- 完全バックアップ
BACKUP DATABASE CompanyDB 
TO DISK = 'C:\Backups\CompanyDB_Full.bak'
WITH FORMAT, 
     NAME = 'CompanyDB Full Backup',
     DESCRIPTION = 'Full backup of CompanyDB';

-- トランザクションログバックアップ
BACKUP LOG CompanyDB 
TO DISK = 'C:\Backups\CompanyDB_Log.trn';

-- リストア
RESTORE DATABASE CompanyDB_Test
FROM DISK = 'C:\Backups\CompanyDB_Full.bak'
WITH MOVE 'CompanyDB' TO 'C:\Data\CompanyDB_Test.mdf',
     MOVE 'CompanyDB_Log' TO 'C:\Data\CompanyDB_Test.ldf',
     REPLACE;

-- Always On 可用性グループ
-- (事前にWindows Server Failover Clustering設定が必要)
CREATE AVAILABILITY GROUP AG_CompanyDB
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE CompanyDB
REPLICA ON 
N'SQL-NODE1' WITH (ENDPOINT_URL = N'TCP://SQL-NODE1:5022',
                   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                   FAILOVER_MODE = AUTOMATIC),
N'SQL-NODE2' WITH (ENDPOINT_URL = N'TCP://SQL-NODE2:5022',
                   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                   FAILOVER_MODE = AUTOMATIC);

-- 監視とメンテナンス
-- インデックス断片化チェック
SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30;

-- 統計情報の更新
UPDATE STATISTICS Employees WITH FULLSCAN;