SQL Server 2005でNULL値のサイズを削減するためのベストプラクティス：ストレージの無駄遣いをなくす

2024-06-05

SQL Server における NULL 値のサイズは、データ型とストレージ方法によって異なります。

データ型ごとのサイズ

整数型:
- NULL: 1 ビットのみ使用されます。
文字列型:
- NULL: NVARCHAR 型の場合は 2 バイト、その他の文字列型の場合は 1 バイトを使用します。
その他: データ型によって異なります。詳細については、以下のリソースを参照してください。

ストレージ方法によるサイズの差異

行ストレージ: NULL 値は、行内のビットマップで管理されます。このため、テーブル内に NULL 値が多い場合は、ストレージ領域の無駄遣いになる可能性があります。
列ストレージ: NULL 値は、各列ごとに個別に管理されます。このため、行ストレージよりもストレージ効率が高くなりますが、より複雑なインデックス構造が必要となります。

メモリ最適化テーブルにおけるサイズ

メモリ最適化テーブルでは、NULL 値の管理方法が異なります。詳細については、以下のリソースを参照してください。

補足

上記の情報は、SQL Server 2005 以降に適用されます。
実際のサイズは、ハードウェア構成や設定によって異なる場合があります。

-- 各データ型の NULL 値のサイズを確認する
SELECT
    data_type,
    data_type_size,
    NULL_value_size
FROM
(
    SELECT
        data_type,
        data_type_size,
        CASE
            WHEN data_type IN ('bigint', 'int', 'smallint', 'tinyint') THEN 1
            WHEN data_type IN ('bit') THEN 1
            WHEN data_type LIKE 'nchar(%)' OR data_type LIKE 'nvarchar(%)' THEN 2
            WHEN data_type LIKE 'varchar(%)' OR data_type LIKE 'char(%)' THEN 1
            WHEN data_type IN ('datetime', 'datetime2', 'smalldatetime', 'date', 'time') THEN 8
            WHEN data_type IN ('real') THEN 4
            WHEN data_type IN ('float') THEN 8
            WHEN data_type IN ('money', 'smallmoney') THEN 4
            WHEN data_type IN ('decimal(%)') THEN
                CASE
                    WHEN SUBSTRING(data_type, PATINDEX(',', data_type) + 1) = 0 THEN 2
                    ELSE
                        SUBSTRING(data_type, PATINDEX(',', data_type) + 1) + 2
                END
            ELSE 1
        END AS NULL_value_size
    FROM
        sys.types
    WHERE
        data_type NOT IN ('image', 'text', 'ntext', 'xml')
) AS t1;

コードの説明

sys.types テーブルから、すべてのデータ型とそのサイズを取得します。
CASE 式を使用して、各データ型の NULL 値のサイズを判定します。
結果を data_type, data_type_size, NULL_value_size の列に格納します。

実行方法

上記のコードを SQL Server Management Studio で実行します。
結果セットが表示されます。各行には、データ型、データ型のサイズ、NULL 値のサイズが表示されます。

特定のデータ型の NULL 値のサイズを確認するには、WHERE 句を使用して条件を絞り込むことができます。
メモリ最適化テーブルの NULL 値のサイズは、上記のコードでは確認できません。メモリ最適化テーブルの NULL 値のサイズを確認するには、sys.dm_os_memory_objects DMV を使用してください。

SQL Server 2005 で NULL 値のサイズを確認するその他の方法

DBCC PAGE コマンドを使用して、特定のページのデータ構造を調べることができます。この方法で、そのページに格納されている NULL 値のサイズを確認することができます。

DBCC PAGE (<ページ番号>, 1)

sys.dm_db_partition_stats DMV は、各パーティションの統計情報に関する情報を提供します。この情報には、NULL 値のサイズも含まれます。

SELECT
    p.partition_id,
    p.index_id,
    p.partition_number,
    p.avg_record_size,
    p.data_pages,
    (p.data_pages * 8192) AS data_space,
    (p.avg_record_size * p.data_pages * 8192) AS total_data_space,
    (p.avg_record_size * p.data_pages * 8192) - (p.used_data_pages * 8192) AS unused_data_space
FROM
    sys.dm_db_partition_stats AS p
WHERE
    p.object_id = <テーブル ID>
    AND p.index_id = 0