次の方法で共有

sp_tableoption (Transact-SQL)

  • [アーティクル]

ユーザー定義テーブルのオプション値を設定します。 sp_tableoption は、varchar(max)、nvarchar(max)、varbinary(max)、xml、text、ntext、image、または大きなユーザー定義型列を含むテーブルの行内での動作を制御する場合に使用できます。

重要な注意事項重要

text in row 機能は、将来のバージョンの SQL Server では削除される予定です。 値の大きなデータを格納する場合には、varchar(max)、nvarchar(max)、および varbinary(max) データ型を使用することをお勧めします。

トピック リンク アイコン Transact-SQL 構文表記規則

構文

sp_tableoption [ @TableNamePattern = ] 'table' 
     , [ @OptionName = ] 'option_name' 
     ,[ @OptionValue =] 'value'

引数

  • [ @TableNamePattern =] 'table'
    ユーザー定義データベース テーブルの修飾名または修飾されていない名前を指定します。 データベース名も含めてフル パスで指定した場合は、そのデータベース名は現在のデータベース名である必要があります。 複数のテーブルにはテーブル オプションを同時に設定できません。 table のデータ型は nvarchar(776) で、既定値はありません。

  • [ @OptionName = ] 'option_name'
    テーブル オプション名を指定します。 option_name のデータ型は varchar(35) で、NULL は既定値にはなりません。 option_name は次のいずれかの値をとります。

    値

    説明

    table lock on bulk load

    無効である場合 (既定)、ユーザー定義テーブル上で行ロックを取得するための一括読み込み処理が行われます。 有効である場合、ユーザー定義テーブル上で一括更新ロックを取得するための一括読み込み処理が行われます。

    insert row lock

    サポートされていません。

    SQL Server のロック方法は、ページまたはテーブル ロックに昇格可能な行ロックです。 このオプションは、SQL Server のロック動作には影響しません。また、このオプションは、既存のスクリプトおよびプロシージャとの互換性のためにのみ残されています。

    text in row

    OFF または 0 (無効、つまり既定値) である場合は、現在の動作を変更せず、行内 BLOB はありません。

    このオプションを指定し、かつ @OptionValue が ON (有効) または 24 ～ 7,000 の整数値に設定されている場合、新しい text 型、ntext 型、または image 型の文字列は、データ行に直接格納されます。 すべての既存の BLOB (バイナリ ラージ オブジェクト: text 型、ntext 型、または image 型のデータ) は、BLOB 値が更新されると、text in row 形式に変更されます。 詳細については、「説明」を参照してください。

    large value types out of row

    1 = テーブル内の varchar(max)、nvarchar(max)、varbinary(max)、xml および大きなユーザー定義型 (UDT) 列は、ルートを指す 16 バイトのポインターと共に、行外に格納されます。

    0 = varchar(max)、nvarchar(max)、varbinary(max)、xml および大きな UDT 値は、データ行に直接格納されます。レコードのサイズまで値を格納できますが、サイズの上限は 8,000 バイトです。 値がレコードに収まらない場合には、ポインターが行内に格納され、残りは行外の LOB ストレージ領域に格納されます。 既定値は 0 です。

    vardecimal ストレージ形式

    TRUE、ON、または 1 の場合、指定されたテーブルでは vardecimal ストレージ形式が有効です。 FALSE、OFF、または 0 の場合、そのテーブルでは vardecimal ストレージ形式が無効です。 vardecimal ストレージ形式を有効にできるのは、sp_db_vardecimal_storage_format を使用して、データベースで vardecimal ストレージ形式を有効にしている場合だけです。 SQL Server 2008 以降では、vardecimal ストレージ形式は推奨されません。 代わりに行の圧縮を使用してください。 詳細については、「データの圧縮」を参照してください。 既定値は 0 です。

  • [ @OptionValue =] 'value'
    option_name が有効 (TRUE、ON、または 1) か無効 (FALSE、OFF、または 0) かを指定します。 value のデータ型は varchar(12) で、既定値はありません。 value では大文字と小文字は区別されません。

    text in row オプションの有効値は、0、ON、OFF、または 24 ～ 7,000 の整数です。 value が ON の場合、既定の上限値は 256 バイトです。

リターン コード値

成功した場合は 0 を、失敗した場合はエラー番号をそれぞれ返します。

説明

sp_tableoption は、ユーザー定義テーブルのオプション値を設定するためにのみ使用できます。 テーブル プロパティを表示するには、OBJECTPROPERTY を使用します。

sp_tableoption の text in row オプションを有効または無効にできるのは、テーブルにテキスト列が含まれている場合だけです。 テーブルにテキスト列がない場合、SQL Server はエラーを生成します。

text in row オプションが有効な場合、ユーザーは @OptionValue パラメーターを使用して、BLOB 用の行に格納される最大サイズを指定できます。 既定値は 256 バイトですが、24 ～ 7,000 バイトの値を指定できます。

次の条件に適合する場合に、text 型、ntext 型、または image 型の文字列がデータ行に格納されます。

  • text in row オプションが有効。

  • 文字列の長さが @OptionValue で指定した制限値より短い。

  • データ行に十分な使用可能領域がある。

BLOB 文字列がデータ行に格納されている場合には、text 型、ntext 型、または image 型の文字列を読み書きする速度は、文字列やバイナリ文字列を読み書きする速度と変わりません。 SQL Server では、別のページにアクセスしなくても BLOB 文字列を読み書きできます。

text 型、ntext 型、または image 型の文字列が、指定された制限値より長い場合や、その行で使用できる容量を超えている場合は、代わりにポインターが行に格納されます。 ただし、BLOB 文字列を行に格納する場合の条件は引き続き適用されます。ただし、データ行にはポインターを格納するのに十分な領域が必要です。

テーブル内の行に格納されている BLOB 文字列やポインターは、可変長文字列と同じように扱われます SQL Server では、その文字列またはポインターを格納するために必要なバイト数だけが使用されます。

既存の BLOB 文字列は、text in row を有効にしても、直ちに変換されるわけではありません。 文字列は、それらが更新されたときに初めて変換されます。 同様に、既にデータ行に格納されている text 型、ntext 型、または image 型の文字列は、text in row オプションの制限値を大きくしても、それらの文字列が更新されない限り、新しい制限値に変換されません。

注意注

text in row オプションを無効にする、またはオプションの制限値を小さくした場合は、BLOB を変換する必要があります。したがって、変換される BLOB 文字列の数によっては、処理に時間がかかる場合があります。 変換処理中は、テーブルがロックされます。

テーブル変数を返す関数を含め、テーブル変数では、text in row オプションが、インラインの上限の既定値である 256 で自動的に有効になります。 このオプションは変更できません。

text in row オプションは、TEXTPTR 関数、WRITETEXT 関数、UPDATETEXT 関数、および READTEXT 関数をサポートします。 ユーザーは、SUBSTRING() 関数を使用して BLOB の複数の部分を読み取ることができますが、行内テキスト ポインターの実行期間および数の制限値は、その他のテキスト ポインターとは異なることに注意する必要があります。

テーブルを vardecimal ストレージ形式から通常の decimal ストレージ形式に戻すには、データベースを単純復旧モードにする必要があります。 復旧モードを変更すると、バックアップに必要なログ チェーンが途切れてしまいます。したがって、テーブルから vardecimal ストレージ形式を削除した後で、データベースの完全バックアップを作成する必要があります。

既存の LOB データ型列 (text、ntext、または image) を小から中程度までの大きさの値を持つ型 (varchar(max)、nvarchar(max)、または varbinary(max)) に変換し、使用している環境でほとんどのステートメントが大きな値型の列を参照しない場合、最適なパフォーマンスを得るために、large_value_types_out_of_row を 1 に変更することを検討します。 large_value_types_out_of_row オプションの値を変更する場合、既存の varchar(max)、nvarchar(max)、varbinary(max)、および xml の値はすぐに変換されません。 文字列の格納場所は、これ以降の更新時に変更されます。 テーブルに挿入される行は、テーブル オプションに従って格納されます。 すぐに結果を得る場合は、データをコピーし、large_value_types_out_of_row 設定を変更した後でテーブルを再作成するか、小から中程度までの大きさの値を持つ型の各列をそれ自体に更新して、保管されている文字列がテーブル オプションによって実質的に変更されるようにします。 更新または再作成後、テーブル上にインデックスを再構築して、テーブルを圧縮することを検討します。

権限

sp_tableoption を実行するには、テーブルに対する ALTER 権限が必要です。

使用例

A. XML データの行外への格納

次の例では、HumanResources.JobCandidate テーブルの xml データが行外に格納されるように指定しています。

USE AdventureWorks2012;
GO
EXEC sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;

B. テーブルでの vardecimal ストレージ形式の有効化

次の例では、decimal データ型を vardecimal storage format で格納するように Production.WorkOrderRouting テーブルを変更します。

USE master;
GO
-- The database must be enabled for vardecimal storage format
-- before a table can be enabled for vardecimal storage format
EXEC sp_db_vardecimal_storage_format 'AdventureWorks2012', 'ON';
GO
USE AdventureWorks2012;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting', 
   'vardecimal storage format', 'ON';

関連項目

参照

sys.tables (Transact-SQL)

OBJECTPROPERTY (Transact-SQL)

システム ストアド プロシージャ (Transact-SQL)

データベース エンジンのストアド プロシージャ (Transact-SQL)