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 = ] '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のロック動作には影響を及ぼすので、既存のスクリプトとプロシージャの互換性のためにのみ含まれます。 |
| text in row | OFF または 0 (無効、つまり既定値) である場合は、現在の動作を変更せず、行内 BLOB はありません。 を指定し @OptionValue 、ON (有効) または 24 から 7000 までの整数値を指定すると、新しい テキスト、 ntext、または イメージ 文字列がデータ行に直接格納されます。 BLOB 値が更新されると、既存のすべての BLOB (バイナリ ラージ オブジェクト: text、 ntext、または image data) が行形式のテキストに変更されます。 詳細については、「解説」を参照してください。 |
| 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 値は、最大 8000 バイトの制限まで、レコードに収まる限り、データ行に直接格納されます。 値がレコードに収まらない場合には、ポインターが行内に格納され、残りは行外の LOB ストレージ領域に格納されます。 0 が既定値です。 大規模なユーザー定義型 (UDT) は、SQL Server 2008 (10.0.x) 以降に適用されます。 CREATE TABLE の TEXTIMAGE_ON オプションを使用して、大きなデータ型の格納場所を指定します。 |
| vardecimal storage format | 適用対象: SQL Server 2008 (10.0.x) 以降。 TRUE、ON、または 1 の場合、指定されたテーブルでは vardecimal ストレージ形式が有効です。 FALSE、OFF、または 0 の場合、テーブルは vardecimal ストレージ形式に対して有効になっていません。 Vardecimal ストレージ形式は、sp_db_vardecimal_storage_format を使用してデータベースが vardecimal ストレージ形式に対して有効になっている場合にのみ有効にすることができます。 SQL Server 2008 (10.0.x) 以降では、vardecimal ストレージ形式は非推奨となりました。 代わりに行の圧縮を使用してください。 詳細については、「 Data Compression」を参照してください。 0 が既定値です。 |
[ @OptionValue =] 'value'
option_nameが有効 (TRUE、ON、または 1) か無効 (FALSE、OFF、または 0) かを指定します。 value は varchar(12)で、既定値はありません。 value では大文字と小文字が区別されません。
text in row オプションの有効値は、0、ON、OFF、または 24 ～ 7,000 の整数です。 値が ON の場合、制限の既定値は 256 バイトです。
リターン コードの値
0 (成功) またはエラー番号 (失敗)
解説
sp_tableoption は、ユーザー定義テーブルのオプション値を設定するためにのみ使用できます。 テーブルのプロパティを表示するには、OBJECTPROPERTY を使用するか、sys.tables に対してクエリを実行します。
sp_tableoption の text in row オプションを有効または無効にできるのは、テーブルにテキスト列が含まれている場合だけです。 テーブルにテキスト列がない場合は、SQL Serverエラーが発生します。
行内テキスト オプションが有効になっている場合、 パラメーターを @OptionValue 使用すると、ユーザーは BLOB の行に格納する最大サイズを指定できます。 既定値は 256 バイトですが、値の範囲は 24 ~ 7000 バイトです。
text、 ntext、または イメージ 文字列は、次の条件が適用される場合、データ行に格納されます。
行内のテキストが有効になっています。
文字列の長さが で指定した制限値より短い。@OptionValue
データ行に十分な使用可能領域がある。
BLOB 文字列がデータ行に格納されている場合、 テキスト、 ntext、または イメージ の文字列の読み取りと書き込みは、文字文字列とバイナリ文字列の読み取りまたは書き込みと同じくらい速い場合があります。 SQL Serverは、BLOB 文字列の読み取りまたは書き込みに個別のページにアクセスする必要はありません。
テキスト、ntext、またはイメージ文字列が、指定された制限値または行の使用可能な領域を超える場合は、代わりにポインターが行に格納されます。 ただし、BLOB 文字列を行に格納する場合の条件は引き続き適用されます。ただし、データ行にはポインターを格納するのに十分な領域が必要です。
テーブルの行に格納されている BLOB 文字列とポインターは、可変長文字列と同様に扱われます。 SQL Serverでは、文字列またはポインターの格納に必要なバイト数のみが使用されます。
行内のテキストが最初に有効になると、既存の BLOB 文字列はすぐに変換されません。 文字列は、それらが更新されたときに初めて変換されます。 同様に、行内のテキスト オプションの制限を増やすと、データ行に既に含まれている テキスト、 ntext、または イメージ の文字列は、更新されるまで新しい制限に従うように変換されません。
注意
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 データの行外への格納
次の例では、テーブル内の xml データを HumanResources.JobCandidate 行外に格納することを指定します。
USE AdventureWorks2022;
GO
EXEC sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;
B. テーブルでの vardecimal ストレージ形式の有効化
次の例では、データ型を Production.WorkOrderRouting ストレージ形式で格納 decimal するようにテーブルを 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';
参照
sys.tables (Transact-SQL)
OBJECTPROPERTY (Transact-SQL)
システム ストアド プロシージャ (Transact-SQL)
データベース エンジン ストアド プロシージャ (Transact-SQL)