decimal データの可変長での格納

  • [アーティクル]

新規 : 2006 年 12 月 12 日

通常、decimal データ型と numeric データ型は固定長データとしてディスクに保存されます。numeric データ型は機能的には decimal データ型と同等です。SQL Server 2005 Service Pack 2 (SP2) 以降のバージョンでは、vardecimal ストレージ形式を使用して、decimal データ型と numeric データ型を可変長列として格納できます。vardecimal ストレージ形式は、SQL Server 2005 Enterprise Edition、Developer Edition、および Evaluation Edition でのみ使用できます。

Bb326755.note(ja-jp,SQL.90).gifメモ :
vardecimal はストレージ形式であり、データ型ではありません。

vardecimal ストレージ形式を使用すると、データの格納サイズを大幅に削減できる可能性があります。ただし、CPU オーバーヘッドが多少増加します。vardecimal ストレージ形式はテーブル レベルで適用されます。つまり、テーブル内の一部の decimal 列だけを vardecimal ストレージ形式で格納することはできません。vardecimal ストレージ形式を適用しても、decimal が真数データ型であることに変わりはありません。

テーブルに対して vardecimal データ形式を有効にすると、decimal データは、データ ページ、インデックス ページ、およびログ ページに vardecimal ストレージ形式で格納されます。ストレージ形式の変更はオフラインで行います。操作中、変更対象のテーブルは排他的にロックされ、その間は読み取りアクセスや書き込みアクセスが禁止されます。

vardecimal ストレージ形式の実装

列の有効桁数 (1 ～ 38) に応じて、decimal 値の格納には 5 ～ 17 バイトが必要です。テーブルで vardecimal ストレージ形式を使用しない場合、テーブル内の各エントリでは、定義された decimal 列ごとに決まったバイト数が消費されます。行の値が 0、NULL、または "3" のように少ないバイト数で表現できる値であっても、消費バイト数は同じです。一方、テーブルを vardecimal ストレージ形式で格納する場合、各行の decimal 列では、その列の値プラス 2 バイトのオーバーヘッドを収容できる領域だけが消費されます。このバイト数は常に 5 ～ 20 バイトで、これには値のオフセット分である 2 バイトのオーバーヘッドも含まれます。ただし、NULL 値および 0 は特別に扱われ、2 バイトのみの消費となります。

テーブルに可変長の列がない場合は、可変長の列の数を格納するために、行ごとに 2 バイトのオーバーヘッドが追加されます。既にテーブルに可変長の列が少なくとも 1 つ存在する場合、オーバーヘッドは追加されません。

次の表に、通常の固定形式での decimal データの格納に必要なバイト数と、vardecimal ストレージ形式での decimal データの格納に必要な最大バイト数を示します。テーブルが固定形式で格納される場合、各行では表示されている値が消費されます。テーブルが vardecimal ストレージ形式で格納される場合、多くの値では、表示されている値より少ないバイト数が消費されます。

列の有効桁数 固定の decimal データ サイズ (バイト) vardecimal データの最大領域 (バイト) オフセット分のオーバーヘッド (バイト) vardecimal データで消費される最大領域 (バイト)

1-3

5

3

2

5

4-6

5

4

2

6

7-9

5

5

2

7

10-12

9

6

2

8

13-15

9

8

2

10

16-18

9

9

2

11

19

9

10

2

12

20-21

13

10

2

12

22-24

13

11

2

13

25-27

13

13

2

15

28

13

14

2

16

29-30

17

14

2

16

31-33

17

15

2

17

34-36

17

16

2

18

37-38

17

18

2

20

vardecimal ストレージ形式の使用

vardecimal ストレージ形式は、次の問題を解決しようとするときに使用できます。

  • ディスク領域が不足している。
  • ディスク アクセス (I/O) がシステム パフォーマンスのボトルネックになっている。
  • NULL、0、または小さな値が多い中で、一部のデータに大きな有効桁数が必要 (たとえばデータ ウェアハウスのテーブルに decimal 列があり、その多くの行が 0 または整数値の場合)。

テーブルのストレージ形式を変更する場合は、テーブルのストレージ構造 (クラスタ化インデックスまたはヒープ) の再構築が必要になります。テーブルのクラスタ化インデックスに decimal 列が含まれている場合は、すべての非クラスタ化インデックスも再構築する必要があります。これはクラスタ化キー値が非クラスタ化インデックスに含まれるためです。クラスタ化インデックスに decimal 列が含まれていない場合は、非クラスタ化インデックスに decimal 列が含まれている場合に非クラスタ化インデックスを再構築します。クラスタ化インデックスのないヒープ テーブルの場合は、すべての非クラスタ化インデックスを再構築してヒープ内の新しい行位置を指すようにする必要があります。

テーブルを再構築して vardecimal ストレージ形式を有効または無効にするときには、元のテーブルの合計記憶領域の 2 倍以上が必要になる可能性があります。テーブルに decimal 列または numeric 列が含まれていない場合は、vardecimal ストレージ形式を有効にするとメタデータの操作だけが行われます。テーブルとインデックスが再構築されるときには、多くのログ アクティビティが予想されます。

vardecimal ストレージ形式は、読み取り専用テーブルと読み取り/書き込みテーブルの両方で使用できます。記憶領域を節約しようとするときには、CPU 利用の増加との間でバランスをとる必要があります。vardecimal ストレージ形式を使用すると、行にアクセスするたびに行のストレージ形式の変換で CPU を消費します。さらに、vardecimal ストレージ形式を使用しているテーブルに書き込みを行うと、ページ分割数の増加でパフォーマンスが低下する可能性があります。

vardecimal ストレージ形式の制限

次の制限があります。

  • SQL Server 2005 SP2 以降のバージョンが必要です。
  • システム データベース (master、model、msdb、tempdb、distribution) では、vardecimal ストレージ形式は有効にできません。vardecimal ストレージ形式で格納されているデータをクエリで並べ替えると、データは tempdb 内で固定の decimal データの状態で並べ替えられます。通常、tempdb でデータに必要な領域は、元のデータベース内で元の vardecimal ストレージ形式のテーブルが占めていた領域よりもかなり大きくなります。
  • vardecimal ストレージ形式は、ビュー、インデックス付きビュー、XML インデックス、フルテキスト インデックスには適用できません。ただし、これらのオブジェクトの基になるテーブルでは vardecimal ストレージ形式を使用できます。
  • メタデータ テーブルや通知テーブルなどの内部テーブルでは、vardecimal ストレージ形式は使用できません。
  • テーブル値関数では、vardecimal ストレージ形式は使用できません。
  • vardecimal ストレージ形式のテーブルに格納されている numeric 列は暗号化できません。
  • 異なる種類のパーティション (固定の decimal 形式のパーティションと vardecimal ストレージ形式のパーティション) の組み合わせはサポートされていません。
  • Transact-SQL SELECT … INTO… 構文を使用して vardecimal ストレージ形式のテーブルからテーブルを作成した場合、新しいテーブルに vardecimal ストレージ形式は継承されません。
  • データベース ミラーリングが有効になっているデータベースでは、vardecimal ストレージ形式の状態を変更できません。データベースの vardecimal ストレージ形式を有効にするにはデータベース ミラーリングを削除する必要があります。ただし、個々のテーブルで vardecimal ストレージ形式を有効または無効にする場合は、データベース ミラーリングを削除する必要はありません。
  • SQL Server では、すべての更新が問題なく実行されることと、テーブルをいつでも固定の decimal 形式に戻せることが保証されている必要があります。そのため、追加のオーバーヘッドにより既存の行が 8,060 バイトを超える場合、または既存のインデックスの値が 900 バイトを超える場合、テーブルを vardecimal ストレージ形式に変更することはできません。
    Bb326755.note(ja-jp,SQL.90).gifメモ :
    vardecimal ストレージ形式と可変長テキスト (varchar) ストレージで異なるのは、すべての可変長列が最大サイズに達している場合に 8,060 バイトを超える可能性のある行を作成するときの、SQL Server での扱いです。varchar の場合、テキスト データの挿入または更新時に 8,060 バイトの制限が適用されます。vardecimal の場合、decimal 列のセットを作成して行の 8,060 バイトの制限を超える可能性がある場合、作成は許可されません。テーブル形式を vardecimal ストレージに変更するときには、8,060 バイトの制限が適用されます。
  • データベース コピー ウィザードのデタッチおよびアタッチによる方法で転送を行う場合、コピー先のデータベース エンジンが SQL Server 2005 SP2 以降でなければアタッチ操作は失敗します。SQL Server 管理オブジェクトの方法では、新しいデータベースとテーブルは vardecimal ストレージ形式を使用しないで作成されます。データベース エンジンが SQL Server 2005 SP2 以降の場合、転送後にデータベースとテーブルを vardecimal 形式に変更できます。

バックアップと復旧、データベース ミラーリング、sp_attach_db、ログ配布

バックアップと復旧、データベース ミラーリング、sp_attach_db、ログ配布は、vardecimal ストレージ形式の場合も正しく動作します。ただし、vardecimal ストレージ形式を使用したデータベースを扱うには、SQL Server の各インスタンスを少なくとも SQL Server 2005 SP2 にアップグレードする必要があります。たとえば、vardecimal ストレージ形式が有効なデータベースのログ バックアップを、vardecimal ストレージ形式が無効なデータベースに復元することはできません。vardecimal ストレージ形式が有効なデータベースから vardecimal ストレージ形式が無効なデータベースにミラーリングしたり、SQL Server 2005 SP2 で vardecimal ストレージ形式を有効にしたデータベースを前のバージョンの SQL Server にアタッチすることもできません。vardecimal ストレージ形式が有効なデータベースの完全バックアップを、vardecimal ストレージ形式が無効なデータベースに復元した場合、データベースで vardecimal ストレージ形式は有効になります。

テーブルを vardecimal ストレージ形式に変更しても、ログ バックアップ チェーンは保持され、最新の完全バックアップと有効なログ チェーンを適用することによりデータベースを復元できます。バックアップが無駄になるのを避けるため、テーブルを修正して vardecimal ストレージ形式を削除する前には、データベースを単純復旧モデルに変更してください。テーブルの vardecimal ストレージ形式を削除した後は、データベースの完全バックアップを作成する必要があります。

データベース ミラーリングを行う場合の vardecimal ストレージ形式の使用

データベース ミラーリングを行う場合に vardecimal ストレージ形式を使用するには、次の手順を実行します。

データベース ミラーリングを行う場合に vardecimal ストレージ形式を使用するには

  1. プリンシパル インスタンスとミラーリング パートナー インスタンスの両方を、少なくとも SQL Server 2005 SP2 にします。
  2. 現在データベース ミラーリングを使用している場合は、データベース ミラーリングとミラーリング パートナーを削除します。詳細については、「データベース ミラーリングを削除する方法 (Transact-SQL)」を参照してください。
  3. プリンシパル データベースで vardecimal ストレージ形式を有効にし、プリンシパル データベースが完全復旧モデルであることを確認します。
  4. プリンシパル データベースの完全バックアップとログ バックアップを使用して、データベース ミラーリングを構築します。詳細については、「Windows 認証を使用してデータベース ミラーリング セッションを確立する方法 (Transact-SQL)」を参照してください。
  5. 個々のテーブルを修正し、vardecimal ストレージ形式を使用するようにします。
Bb326755.note(ja-jp,SQL.90).gifメモ :
個々のテーブルのストレージ形式を変更するために、データベース ミラーリングを削除する必要はありません。

vardecimal ストレージ形式を削除するには

  1. プリンシパル データベースのテーブルを修正し、vardecimal ストレージ形式を削除します。
  2. データベース ミラーリングを削除します。
  3. プリンシパル データベースを単純復旧モードに設定します。これにより、ログ チェーンが壊れます。
  4. プリンシパル データベースの vardecimal ストレージ形式を無効にします。
  5. ミラーリング パートナー データベースを削除します。
  6. プリンシパル データベースを完全復旧モデルに戻します。
  7. プリンシパル データベースをバックアップして、データベース ミラーリングを再構築します。

レプリケーション操作での vardecimal ストレージ形式の影響

テーブルで vardecimal ストレージ形式を使用している場合も、レプリケーションは通常どおり機能します。ただし、次の注意点があります。

  • レプリケーション中、vardecimal ストレージ形式で格納されている decimal データ型は、転送用に固定の decimal 形式に変換されます。ディストリビューション データベースでは、vardecimal ストレージ形式を有効にできません。したがって、ディストリビューション データベースのレプリケーション テーブルへのデータは、vardecimal ストレージ形式では格納されません。サブスクライバでは、ログ レコードが通常どおり適用されます。
  • vardecimal ストレージ形式のテーブルは固定の decimal 形式のテーブルにレプリケートでき、固定の decimal 形式のテーブルは vardecimal ストレージ形式のテーブルにレプリケートできます。
  • 新しいサブスクリプションに関連するテーブル作成処理では、vardecimal ストレージ形式を使用したテーブルは作成されません。これは、データベース エンジンの Service Pack のレベルや、サブスクライブするデータベースでの vardecimal ストレージ形式の有効化状態に関係なく、レプリケーションを正常に行うためです。サブスクライブするテーブルは、作成後にサブスクライバ側で vardecimal ストレージ形式を有効にできます。または、適用前に作成スクリプトを修正し、有効にすることもできます。

次の表に、さまざまなサブスクライバのスクリプト要件を示します。

サブスクライバ スクリプト

SQL Server 2000 または SQL Server Version 7.0

テーブル作成スクリプトは修正なしで使用できます。

SQL Server 2005 (データベースに vardecimal ストレージ形式の指定なし)

テーブル作成スクリプトは修正なしで使用できます。

SQL Server 2005 (データベースに vardecimal ストレージ形式の指定あり、ただしサブスクライバ テーブルで vardecimal ストレージ形式は有効にしない)

テーブル作成スクリプトは修正なしで使用できます。

SQL Server 2005 (データベースに vardecimal ストレージ形式の指定あり、SQL Server 2005 サブスクライバ テーブルで vardecimal ストレージ形式を有効にする)

テーブル作成スクリプトを修正し、データベースで vardecimal ストレージ形式を有効にするかテーブルの vardecimal ストレージ形式を有効にできます。または、後の「vardecimal ストレージ形式の有効化」に示すストアド プロシージャを使用して、サブスクライバ データベースおよびサブスクライバ テーブルを有効にできます。

その他の注意点

vardecimal ストレージ形式を使用する場合のその他の注意点は次のとおりです。

  • 一括インポートおよび一括エクスポート (bcp) 操作には、vardecimal ストレージ形式による影響はありません。
  • DATALENGTH 関数では、vardecimal ストレージ形式は検出されません。この関数では、固定の decimal 形式で格納された場合のバイト数が返されます。
  • まれに、vardecimal ストレージ形式を使用している場合に、固定の decimal データ用に最適化されたクエリ プランを SQL Server で使用できないことがあります。
  • vardecimal ストレージ形式は、どのデータベース互換性レベルでも使用できます。
  • decimal データ型または numeric データ型の列がないテーブルに対して sp_tableoption を実行した場合は、vardecimal ストレージ形式の使用を示すようテーブルのメタデータが変更されます。その後追加する新しい decimal 列は、vardecimal ストレージ形式で格納されます。vardecimal ストレージ形式を使用しているテーブルに列を追加したりこのテーブルから列を削除するときに、特別な手法は必要ありません。

vardecimal ストレージ形式の有効化

vardecimal ストレージ形式を有効にするには、次の権限が必要です。

  • データベースで vardecimal ストレージ形式を有効にするには、サーバーに対する ALTER DATABASE 権限が必要です。
  • テーブルの vardecimal ストレージ形式を変更するには、そのテーブルに対する ALTER 権限が必要です。

vardecimal ストレージ形式を有効にする前には、vardecimal ストレージ形式を有効にしたときにテーブルのサイズが削減されるかどうかを確認する必要があります。ほとんどの行で、列に定義された有効桁数が使用される場合は、vardecimal ストレージ形式のオーバーヘッドが削減量を上回り、元のテーブルよりサイズが大きくなる可能性もあります。テーブルの変更前に行の削減サイズを見積もるには、sp_estimated_rowsize_reduction_for_vardecimal ストアド プロシージャを使用します。テーブルのストレージ形式を変更することを決定した場合は、データベースで vardecimal ストレージ形式を有効にした後、個々のテーブルの vardecimal ストレージ形式を有効にします。

decimal データ型の vardecimal ストレージ形式を有効にするには、ストアド プロシージャまたは SQL Server Management Studio を使用します。

  • sp_db_vardecimal_storage_format を実行し、データベースで vardecimal ストレージ形式を有効にした後、sp_tableoption を実行し、適切なテーブルの vardecimal ストレージ形式を有効にします。
  • Management Studio で、[データベースのプロパティ] の [オプション] ページを使用して、データベースで vardecimal ストレージ形式を有効にします。テーブルを vardecimal ストレージ形式に変更するには、sp_tableoption を使用する必要があります。

vardecimal ストレージ形式のテーブルの特定

vardecimal ストレージ形式を使用しているテーブルを特定するには、OBJECTPROPERTY 関数を使用し、TableHasVarDecimalStorageFormat プロパティを探します。

次の例では、Production.WorkOrderRouting テーブルが vardecimal ストレージ形式を使用している場合は 1 を返し、そうでない場合は 0 を返します。

USE AdventureWorks ;
GO
SELECT OBJECTPROPERTY(OBJECT_ID('Production.WorkOrderRouting'), 
   'TableHasVarDecimalStorageFormat') ;
GO

次の例では、AdventureWorks データベースで、vardecimal ストレージ形式を使用しているすべてのテーブルを探します。

USE AdventureWorks ;
GO
SELECT name, object_id, type_desc
FROM sys.objects 
 WHERE OBJECTPROPERTY(object_id, 
   N'TableHasVarDecimalStorageFormat') = 1 ;
GO

vardecimal ストレージ形式を削除するときの問題点

テーブルから vardecimal ストレージ形式を削除するには、テーブルを固定の decimal 形式で再構築する必要があります。この操作によって、テーブルの使用ディスク領域は大幅に増加する可能性があり、十分なディスク領域がないと操作は失敗します。この場合、vardecimal ストレージ形式を無効にするには、SQL Server 用に十分なディスク領域を確保する必要があります。拡張操作を行う場合も、vardecimal 形式と通常の形式の両方でデータを格納するための一時領域が必要です。拡張後のテーブルがディスクの空き領域に納まるにもかかわらず、一時ディスク領域が不足しているという理由だけで拡張に失敗する場合は、テーブルの行をコピーして未拡張の新しいテーブルに移動することで、少しずつデータを拡張できます。

変更後すぐにデータベースの vardecimal ストレージ形式を削除するには、データベースを削除し、バックアップからデータベースを復元します。このバックアップは、データベースで vardecimal ストレージ形式を有効にする前に作成したバックアップです。

SQL Server 2005 の Enterprise Edition、Developer Edition、または Evaluation Edition から、別の Edition または以前のバージョンの SQL Server にデータベースを移行するときには、まず必要な Edition の 1 つを使用してデータベースを開き、vardecimal ストレージ形式を削除して、その後データベースを移行します。vardecimal ストレージ形式を含むデータベースを不適格なサーバーにアタッチしようとすると、操作は失敗します。

参照

概念

小数型データ、浮動小数型データ、および実数型データの使用

ヘルプおよび情報

SQL Server 2005 の参考資料の入手