有効なテーブルを作成する

完了済み

効果的なテーブル設計は、任意のデータベースの基礎を形成します。 テーブルはデータを構造化し、クエリが情報にアクセスして変更する効率を決定します。

テーブルの設計と作成

テーブルはリレーショナル データベースの基本的な構成要素であり、エンティティとその属性を表す行と列にデータを編成します。 リレーショナル システムでは、テーブルはトランザクション データを格納するための構造を定義し、外部キーを介してリレーションシップを適用し、クエリとレポートの基盤を提供します。

多次元分析の場合、テーブルは測定可能なイベントを格納する ファクト テーブル として機能し、分析のコンテキストを提供する ディメンション テーブル として機能します。 テーブルの作成時に行う設計上の決定 (データ型、列のサイズ設定、制約、リレーションシップ) は、運用ワークロードと分析ワークロードの両方で、ストレージ効率、クエリパフォーマンス、データ整合性、スケーラビリティに直接影響します。

適切なデータ型を選択する

データ型は、データベースに影響を与える基本的な決定事項です。 間違った選択は、ストレージの無駄、パフォーマンスの低下、データ損失、またはアプリケーション エラーにつながる可能性があります。 簡単にリファクタリングできるアプリケーション コードとは異なり、運用データベースで列データ型を変更するには多くの場合、テーブルの再構築が必要です。これは、大規模なテーブルのダウンタイムの数時間を意味する可能性があります。

最初のスキーマを設計するときに適切なデータ型を選択します。これは、正しく行う最も簡単なタイミングです。 また、次の場合は、データ型を慎重に検討してください。

  • 精度が重要な場所にデータを格納している
  • 大量のテーブルを扱うと、ストレージ コストが増加します。
  • 小さい型を使うことで処理が高速になる、頻繁にクエリされる列を定義しています。

一般的なデータ型を調べる

適切なデータ型は、ストレージ、パフォーマンス、および操作に影響します。

タイプカテゴリ データ型 ストレージ サイズ 使用方法のガイドライン 例
Numeric INT、 BIGINT、 DECIMAL、 FLOAT 4 バイト、8 バイト、異なる 範囲と精度のニーズに基づいて選択する Quantity INT、Revenue DECIMAL(10,2)、Population BIGINT
ストリング VARCHAR、CHAR、NVARCHAR 1 バイト/char、固定、2 バイト/char 可変長データには VARCHAR を使用し、固定長の場合は CHAR 、Unicode の場合は NVARCHAR を使用します Email VARCHAR(100)、CountryCode CHAR(2)、ProductName NVARCHAR(100)
日付/時刻 DATE、DATETIME2、DATETIMEOFFSET 3 バイト、6 ~ 8 バイト、10 バイト DATETIME2 より優れた精度を提供します。 DATETIME BirthDate DATE、OrderTimestamp DATETIME2、EventTime DATETIMEOFFSET
バイナリ VARBINARY、IMAGE varies 画像やドキュメントなどのバイナリ データを格納する場合 ProfilePhoto VARBINARY(MAX)、DocumentContent VARBINARY(MAX)
特別 UNIQUEIDENTIFIER、XML、JSON 16 バイト、可変、ネイティブ バイナリ UNIQUEIDENTIFIER GUID の場合、XML ドキュメントの XML 、ネイティブ バイナリ形式の JSON ドキュメントの JSON (SQL 2025 以降) RowGUID UNIQUEIDENTIFIER、Config XML、Settings JSON

データ型の微妙な違いには注意が必要です。 たとえば、FLOATではなく財務データにDECIMALを使用すると、すべての依存値を再計算しないと修正できない丸めエラーが発生する可能性があります。 INT で十分な場合でも UNIQUEIDENTIFIER を主キーにすると、インデックス サイズが 3 倍になり、あらゆる JOIN 操作が遅くなります。 これらの決定のほとんどは、データベースのパフォーマンスに影響を与え、クエリが実行される時間がミリ秒か分かを判断できます。

テーブル サイズの要件を見積もる

テーブル サイズはストレージ コストだけではありません。これはデータベース操作に直接影響します。 テーブル サイズは、バックアップと復元の時間、インデックスの再構築期間、およびクエリのパフォーマンスに影響します。

Important

100 バイトではなく行あたり 200 バイトを格納する不適切に設計されたテーブルでは、ストレージのニーズ、バックアップ時間、および I/O 要件が 2 倍になります。

テーブルのサイズ設定を計画するもう 1 つのシナリオは、クラウド データベースのストレージ コストの計算、限られたディスク領域の設計、アーカイブ戦略の計画を行う場合です。 これらのシナリオはすべて、リソースと操作に関する情報に基づいた意思決定を行うために、正確なサイズ見積もりを必要とします。

たとえば、毎日 1 億件のトランザクションを格納し、1 行あたり 50 バイトを余分に格納している小売企業では、1 日あたり 5 GB が無駄になります。これは、年間 1.8 TB の不要なストレージに加えて、バックアップ時間とコストが比例して増加します。

次の例は、 Employee テーブルのサイズを見積もる方法を示しています。

-- Estimate row size for a table
-- Fixed-length columns: sum of column sizes
-- Variable-length: estimate average size
-- Example row calculation:
CREATE TABLE Employee (
    EmployeeID INT,              -- 4 bytes
    FirstName NVARCHAR(50),      -- ~2-100 bytes (avg 40)
    LastName NVARCHAR(50),       -- ~2-100 bytes (avg 40)
    HireDate DATE,               -- 3 bytes
    Salary DECIMAL(10,2)         -- 5 bytes
);  
-- Estimated row size: 4 + 40 + 40 + 3 + 5 = ~92 bytes
-- Plus row overhead (~7 bytes) = ~99 bytes per row
-- 1 million rows ≈ 94 MB

ヒント

Copilot を使用すると、テーブル サイズの見積もりを生成できます。

有効な列を設計する

次の例は、このユニットで説明する原則を適用する適切に設計された Product テーブルを示しています。

CREATE TABLE Product (
    ProductID INT PRIMARY KEY IDENTITY(1,1),       -- Auto-incrementing surrogate key (4 bytes)
    ProductName NVARCHAR(100) NOT NULL,             -- Unicode support, appropriate length, enforced
    Category NVARCHAR(50) NOT NULL,                 -- Smaller than ProductName (categorization needs less space)
    Price DECIMAL(10,2) NOT NULL,                   -- Exact precision for financial data
    StockQuantity INT NOT NULL DEFAULT 0,           -- Integer sufficient for inventory, default prevents nulls
    LastRestocked DATETIME2 DEFAULT GETUTCDATE()    -- Modern date type with automatic timestamp
);

次の表は、いくつかのベスト プラクティスを示しています。

  • 適切なデータ型: 主キーのINT はBIGINTまたはUNIQUEIDENTIFIERよりも小さくする、DECIMAL(10,2)ではなくFLOATを使用して正確な財務計算を行う、レガシーDATETIMEよりも精度が高いDATETIME2を使用する
  • 適切なサイズの列: 予想されるデータ長に基づいた製品名用の NVARCHAR(100) とカテゴリ用の NVARCHAR(50)
  • 制約: NOT NULL は、重要な値が見つからないのを防ぐことでデータ品質を確保します
  • 既定値: StockQuantity (0) と LastRestocked (現在の UTC 時刻) の自動値により、アプリケーション コードの複雑さが軽減されます
  • 効率的な主キー: IDENTITY はクラスター化されたシーケンシャル キーを効率的に生成し、最小限のストレージ (GUID の場合は 4 バイトと 16 バイト) を使用します。

注

この例では、Unicode のサポートに NVARCHAR (1 文字あたり 2 バイト) を使用します。 データが ASCII 専用の場合、 VARCHAR (1 文字あたり 1 バイト) は文字列ストレージを半分に切り取ります。 ProductName VARCHAR(100)は、30 文字の名前のNVARCHAR(100)に対して最大 30 バイトと最大 60 バイトを使用します。 1,000 万行の場合、約 300 MB が節約されます。 国際データには NVARCHAR を使用します。ストレージの効率が重要であり、データが ASCII 専用のままになる場合は、 VARCHAR を使用します。

設計のベスト プラクティス

パフォーマンスと保守容易性を確保するために、テーブルを設計および実装する際に、次の重要な原則を適用します。

  • 適切なデータ型を使用する - データ型が小さいとストレージが削減され、パフォーマンスが向上する
  • テーブル サイズを早期に検討 する - ストレージとインデックス作成を計画するために行サイズと合計テーブル サイズを見積もる
  • 意味のある制約を実装 する - データベース レベルでデータ品質を確保する
  • 成長を計画する - 将来のデータ 量を処理するためのテーブルを設計する
  • 戦略的にインデックスを行う - WHERE、JOIN、およびORDER BY 句で使用される列にインデックスを付ける
  • 分析用の列ストアの選択 - 分析クエリで大規模なテーブルに列ストア インデックスを使用する
  • 適切な場合に正規化 する - 正規化とクエリ パフォーマンスのニーズのバランスを取る
  • 行とページの圧縮を監視 する - 大きなテーブルの圧縮を有効にしてストレージを保存する

ほとんどのデータベース パフォーマンスの問題は、開発の早い段階で行われた設計上の不十分な決定に起因します。 サイズが大きいデータ型では、ストレージが無駄になり、クエリが遅くなります。 インデックスの種類が見つからないか間違っていると、リソースのアップグレードで解決できないボトルネックが発生します。 テーブルを作成または変更する前に、適切なオブジェクト設計に時間を費やして、これらの問題を回避します。 設計時に行う決定 (適切なデータ型の選択、テーブル サイズの見積もり、適切なインデックスの種類の選択) は、後で適用できる最適化よりもはるかに長期的なパフォーマンスとコストに大きな影響を与えます。