sp_tableoption (Transact-SQL)
適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
ユーザー定義テーブルのオプション値を設定します。 sp_tableoptionは、varchar(max)、nvarchar(max)、varbinary(max)、xml、text、ntext、image、または大きなユーザー定義型の列を持つテーブルの行内動作を制御するために使用できます。
重要
行内のテキスト機能は、今後のバージョンの SQL Server で削除される予定です。 大きな値のデータを格納するには、varchar(max)、nvarchar(max)、varbinary(max) のデータ型を使用することをお勧めします。
構文
sp_tableoption
[ @TableNamePattern = ] N'TableNamePattern'
, [ @OptionName = ] 'OptionName'
, [ @OptionValue = ] 'OptionValue'
[ ; ]
引数
[ @TableNamePattern = ] N'TableNamePattern'
ユーザー定義データベース テーブルの修飾名または非修飾名。 @TableNamePatternは nvarchar(776)で、既定値はありません。 データベース名も含めてフル パスで指定した場合は、そのデータベース名は現在のデータベース名である必要があります。 複数のテーブルのテーブル オプションを同時に設定することはできません。
[ @OptionName = ] 'OptionName'
テーブル オプション名。 @OptionNameは varchar(35) で、次のいずれかの値を指定できます。
| Value | 説明 |
|---|---|
table lock on bulk load |
無効である場合 (既定)、ユーザー定義テーブル上で行ロックを取得するための一括読み込み処理が行われます。 有効にすると、ユーザー定義テーブルの一括読み込みプロセスが一括更新ロックを取得します。 |
insert row lock |
サポート対象から除外されました。 このオプションは SQL Server のロック動作には影響せず、既存のスクリプトとプロシージャの互換性のためにのみ含まれます。 |
text in row |
0 または OFF (既定では無効)、現在の動作は変更されません。BLOB は行にありません。指定され、 @OptionValue が有効になっている ON か、または整数値が 24 使用されると 7000、新しい テキスト、 n テキスト、または イメージ 文字列がデータ行に直接格納されます。 BLOB 値が更新されると、既存のすべての BLOB (バイナリ ラージ オブジェクト: text、 ntext、または image) データが行形式のテキストに変更されます。 詳細については、「解説」を参照してください。 |
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 が既定値です。 大規模なユーザー定義型 (UDT) は、SQL Server 2008 (10.0.x) 以降に適用されます。 CREATE TABLE のオプションを TEXTIMAGE_ON使用して、大きなデータ型のストレージの場所を指定します。 |
| vardecimal ストレージ形式 | 適用対象: SQL Server 2008 (10.0.x) 以降。 指定されたテーブルが vardecimal ストレージ形式に対して有効になっている場合 TRUE。 ON1 または、OFF0テーブルが vardecimal ストレージ形式に対して有効になっていない場合FALSE。 vardecimal ストレージ形式は、データベースが vardecimal ストレージ形式に対して有効になっている場合にのみ、sp_db_vardecimal_storage_formatを使用して有効にすることができます。 SQL Server 2008 (10.0.x) 以降では、 vardecimal ストレージ形式は非推奨です。 代わりに圧縮を使用 ROW してください。 詳細については、「データ圧縮」を参照してください。 0 が既定値です。 |
[ @OptionValue = ] 'OptionValue'
@OptionNameを有効 (、またはON) または1無効 (TRUEFALSE、またはOFF) にするかどうかを指定します0。 @OptionValueは varchar(12) で、既定値はありません。 @OptionValueでは大文字と小文字が区別されません。
行内のテキスト オプションの場合、有効なオプション値は 0、、、OFFまたは整数から 24 .7000ON @OptionValueの場合、制限の既定値は ON256 バイトです。
リターン コードの値
0 (成功) またはエラー番号 (失敗)。
解説
sp_tableoption は、ユーザー定義テーブルのオプション値を設定する場合にのみ使用できます。 テーブルのプロパティを表示するには、OBJECTPROPERTY またはクエリを使用します sys.tables。
行内のテキスト オプション sp_tableoption は、テキスト列を含むテーブルでのみ有効または無効にすることができます。 テーブルにテキスト列がない場合、SQL Server はエラーを発生させます。
行内テキスト オプションが有効になっている場合、@OptionValue パラメーターを使用すると、ユーザーは BLOB の行に格納する最大サイズを指定できます。 既定値は 256 バイトですが、値の範囲は 24 ~ 7,000 バイトです。
テキスト、 ntext、または イメージ 文字列は、次の条件が適用される場合、データ行に格納されます。
- 行内のテキストが有効になっています。
- 文字列の長さは、@OptionValueで指定された制限よりも短くなります。
- データ行には十分な空き領域があります。
BLOB 文字列がデータ行に格納されている場合、テキスト、ntext、またはイメージ文字列の読み取りと書き込みは、文字文字列とバイナリ文字列の読み取りまたは書き込みと同じ速度で実行できます。 SQL Server は、BLOB 文字列の読み取りまたは書き込みに個別のページにアクセスする必要はありません。
テキスト、n テキスト、または画像の文字列が、指定された制限値または行内の使用可能な領域を超える場合は、代わりにポインターが行に格納されます。 ただし、BLOB 文字列を行に格納する場合の条件は引き続き適用されます。ただし、データ行にはポインターを格納するのに十分な領域が必要です。
テーブルの行に格納されている BLOB 文字列とポインターは、可変長文字列と同様に扱われます。 SQL Server では、文字列またはポインターの格納に必要なバイト数のみが使用されます。
行内のテキストが最初に有効になると、既存の BLOB 文字列はすぐに変換されません。 文字列は、それらが更新されたときに初めて変換されます。 同様に、行内のテキスト オプションの制限が引き上げられると、 データ行に既に含まれているテキスト、 ntext、または イメージ 文字列は、更新されるまで新しい制限に準拠するように変換されません。
Note
text in row オプションを無効にする、またはオプションの制限値を小さくした場合は、BLOB を変換する必要があります。したがって、変換される BLOB 文字列の数によっては、処理に時間がかかる場合があります。 テーブルは変換プロセス中にロックされます。
テーブル変数 (テーブル変数を返す関数を含む) では、行内のテキスト オプションが自動的に有効になっており、既定のインライン制限は 256 です。 このオプションは変更できません。
行内のテキスト オプションは、TEXTPTR、WRITETEXT、UPDATETEXT、および READTEXT 関数をサポートしています。 ユーザーは SUBSTRING() 関数を使用して BLOB の一部を読み取ることができますが、行内テキスト ポインターには他のテキスト ポインターとは異なる期間と数の制限があることを覚えておく必要があります。
テーブルを vardecimal ストレージ形式から通常の 10 進ストレージ形式に変更するには、データベースが SIMPLE 復旧モデルにある必要があります。 復旧モードを変更すると、バックアップの目的でログ チェーンが破損するため、テーブルから vardecimal ストレージ形式を削除した後にデータベースの完全バックアップを作成する必要があります。
既存の LOB データ型列 (テキスト、n テキスト、またはイメージ) を中小の大きな値型 (varchar(max)、nvarchar(max)、または varbinary(max)) に変換する場合、ほとんどのステートメントで環境内の大きな値型の列が参照されない場合は、最適なパフォーマンスを得るために1large_value_types_out_of_rowを変更することを検討してください。 large_value_types_out_of_row オプション値が変更されると、既存の varchar(max)、nvarchar(max)、varbinary(max)、および xml 値はすぐに変換されません。 文字列のストレージは、後で更新されると変更されます。 テーブルに挿入される新しい値は、有効なテーブル オプションに従って格納されます。 すぐに結果を得るには、データのコピーを作成し、large_value_types_out_of_row設定を変更した後にテーブルを再作成するか、または文字列のストレージが有効なテーブル オプションを使用して変更されるように、小から中規模の各大きな値の型の列をそれ自体に更新します。 更新または再作成の後でテーブルのインデックスを再構築し、テーブルを圧縮することを検討します。
アクセス許可
実行 sp_tableoption するには、テーブルに対するアクセス許可が必要 ALTER です。
例
A. XML データを行の外に格納する
次の例では、テーブル内の xml データを HumanResources.JobCandidate 行外に格納することを指定します。
USE AdventureWorks2022;
GO
EXEC sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;
B. テーブルで vardecimal ストレージ形式を有効にする
次の例では、Production.WorkOrderRouting10 進データ型を vardecimal ストレージ形式で格納するようにテーブルを変更します。
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 'AdventureWorks2022', 'ON';
GO
USE AdventureWorks2022;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting',
'vardecimal storage format', 'ON';
関連するコンテンツ
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示