有効なテーブルを作成する
効果的なテーブル設計は、任意のデータベースの基礎を形成します。 テーブルはデータを構造化し、クエリが情報にアクセスして変更する効率を決定します。
テーブルの設計と作成
テーブルはリレーショナル データベースの基本的な構成要素であり、エンティティとその属性を表す行と列にデータを編成します。 リレーショナル システムでは、テーブルはトランザクション データを格納するための構造を定義し、外部キーを介してリレーションシップを適用し、クエリとレポートの基盤を提供します。
多次元分析の場合、テーブルは測定可能なイベントを格納する ファクト テーブル として機能し、分析のコンテキストを提供する ディメンション テーブル として機能します。 テーブルの作成時に行う設計上の決定 (データ型、列のサイズ設定、制約、リレーションシップ) は、運用ワークロードと分析ワークロードの両方で、ストレージ効率、クエリパフォーマンス、データ整合性、スケーラビリティに直接影響します。
適切なデータ型を選択する
データ型は、データベースに影響を与える基本的な決定事項です。 間違った選択は、ストレージの無駄、パフォーマンスの低下、データ損失、またはアプリケーション エラーにつながる可能性があります。 簡単にリファクタリングできるアプリケーション コードとは異なり、運用データベースで列データ型を変更するには多くの場合、テーブルの再構築が必要です。これは、大規模なテーブルのダウンタイムの数時間を意味する可能性があります。
最初のスキーマを設計するときに適切なデータ型を選択します。これは、正しく行う最も簡単なタイミングです。 また、次の場合は、データ型を慎重に検討してください。
- 精度が重要な場所にデータを格納している
- 大量のテーブルを扱うと、ストレージ コストが増加します。
- 小さい型を使うことで処理が高速になる、頻繁にクエリされる列を定義しています。
一般的なデータ型を調べる
適切なデータ型は、ストレージ、パフォーマンス、および操作に影響します。
| タイプカテゴリ | データ型 | ストレージ サイズ | 使用方法のガイドライン | 例 |
|---|---|---|---|---|
| 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句で使用される列にインデックスを付ける - 分析用の列ストアの選択 - 分析クエリで大規模なテーブルに列ストア インデックスを使用する
- 適切な場合に正規化 する - 正規化とクエリ パフォーマンスのニーズのバランスを取る
- 行とページの圧縮を監視 する - 大きなテーブルの圧縮を有効にしてストレージを保存する
ほとんどのデータベース パフォーマンスの問題は、開発の早い段階で行われた設計上の不十分な決定に起因します。 サイズが大きいデータ型では、ストレージが無駄になり、クエリが遅くなります。 インデックスの種類が見つからないか間違っていると、リソースのアップグレードで解決できないボトルネックが発生します。 テーブルを作成または変更する前に、適切なオブジェクト設計に時間を費やして、これらの問題を回避します。 設計時に行う決定 (適切なデータ型の選択、テーブル サイズの見積もり、適切なインデックスの種類の選択) は、後で適用できる最適化よりもはるかに長期的なパフォーマンスとコストに大きな影響を与えます。