SQL に関する Q&A非クラスタ化インデックスと権限の保持
Saleem Hakani and Dan Carollo
非クラスタ化インデックスの概要と用途
質問 - 非クラスタ化インデックスとは何ですか。また、非クラスタ化インデックスを使用した場合のメリットとデメリットについても教えてください。
回答 - 非クラスタ化インデックスは、一般的な書籍の巻末にある索引のようなものです。書籍に記載されているすべての情報は、索引でトピックごとに記載されています。また、書籍のどこに、その情報が記載されているのかを示す道しるべとしてページ番号が明記されています。また、書籍の索引に記載されているデータの順序は、書籍の本文に記載されている順序とは異なります。この点は非クラスタ化インデックスも同じです。テーブルにクラスタ化インデックスを作成すると、アイテムの順序を指定できます。クラスタ化インデックスがない場合、アイテムの順序を指定することはできません。
非クラスタ化インデックスには 2 つの制限事項があります。1 つ目はインデックスに含められる最大列数が 16 列であること、2 つ目はインデックス キーの最大サイズが 900 バイトであることです。これはどういうことでしょうか。MovieList サンプル データベースの Movie テーブルの列にインデックスを作成する場合について考えてみましょう。インデックスの作成対象となる列は、MovieTitle NVarchar(50)、DirectorName NVarchar(50)、および ShortStory NVarchar(400) です。
次のステートメントを発行してテーブルを作成したとします。
Use MovieList;
CREATE INDEX Movie_IDX ON Movie(MovieTitle, DirectorName, ShortStory);
このステートメントを実行すると、"警告: 最大キー長は 900 バイトです。インデックス 'Movie_IDX' の最大長は 1000 バイトです。大きな値の組み合わせの一部では挿入操作または更新操作が失敗します。" というエラー メッセージが表示されます。このメッセージは、nvarchar データ型が 1 文字あたり 2 バイトを消費し、上記の 3 列を含むインデックスが 900 バイトのサイズの制限を超えたことが原因で表示されます。
SQL Server® 2005 では、INCLUDE 句に列を追加することで、この問題を回避できるようになりました。これは、サイズと列の両方の制限を回避する必要がある場合に非常に有益です。非クラスタ化インデックスの制限を回避するには、次のステートメントを実行します。
CREATE INDEX Movie_IDX ON Movie(MovieTitle, DirectorName) INCLUDE (ShortStory);
インデックスの作成時に INCLUDE 句を使用する場合、データベース エンジンでは、インデックス キー列の数やインデックス キーのサイズを計算するときに、キー列以外が考慮されないことに注意する必要があります。
非クラスタ化インデックスに関する詳細情報については、「非クラスタ化インデックス」(msdn2.microsoft.com/aa174537) と「非クラスタ化インデックスを使用する」(msdn2.microsoft.com/aa933130) を参照してください。また、インデックスの最適化に関するヒントについては、sql-server-performance.com/optimizing_indexes.asp を参照してください。
権限に関するデータを保持する
質問 - サブスクリプションが初期化されたときに、権限が失われることを回避する方法を教えてください。スナップショットが再初期化されると、許可されたすべての権限が失われるという問題に何度も遭遇しています。
回答 - 既定では、サブスクリプションが初期化されると、サブスクリプション データベースにあるすべてのオブジェクトは削除され再作成されます。ただし、このシナリオに対応する方法が 2 つあります。
1 つ目の方法は、再初期化された後に、すべての権限を再度適用することです。権限を手動で設定する場合は、スクリプトを使用してオブジェクト レベルとステートメント レベルの権限を取り出して、個別に格納し、サブスクリプションの再初期化後に利用できるようにする必要があります。
2 つ目の方法は、サブスクリプションの再初期化時にオブジェクトが削除されないようにサブスクリプションを構成することです。これには、SP_CHANGEARTICLE システム ストアド プロシージャを使用して、PRE_CREATION_CMD の @PROPERTY パラメータの値と、NONE、DELETE、または TRUNCATE の @Value パラメータの値を構成します。
アーティクルのプロパティ ダイアログ ボックスの [対象オブジェクト] で、[既存のオブジェクトを変更せずに保持します]、[アーティクルに行フィルタがある場合は、フィルタに一致するデータのみを削除します]、および [既存のオブジェクト内にあるすべてのデータを切り捨てます] を選択します。この設定はテスト環境でお試しください。サポートや詳細情報が必要な場合は、最新版の SQL Server Books Online で最新情報を参照してください。
Saleem Hakani は、データベース システムに関して 14 年の経験を持つシニア データベース エンジニア兼シニア プロブレム エンジニアで、世界規模で展開している Microsoft SQL Server コミュニティで積極的に情報を発信しています。また、SQL Server に関する外部のコミュニティ Web サイト (sqlcommunity.com) でも同様に積極的に情報を発信しています。Saleem の連絡先は Saleem@sqlcommunity.com (英語のみ) です。
Dan Carollo は、マイクロソフトの Windows Anti-Malware Research and Response チームと協力して作業をしていて、オペレーション エンジニアと SQL Server DBA を兼任しています。また、SQL Server の MCT 認定資格を取得しています。
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; 許可なしに一部または全体を複製することは禁止されています.