sp_tableoption (Transact-SQL)

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

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

重要

行内のテキスト機能は、今後のバージョンの SQL Server で削除される予定です。 大きな値のデータを格納するには、 varchar(max)、 nvarchar(max) 、および varbinary(max) データ型を使用することをお勧めします。

Transact-SQL 構文表記規則

構文

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	OFFまたは0 (既定では無効)、現在の動作は変更されません。BLOB は行にありません。 指定した @OptionValue が ON (有効) であるか、 24 から 7000までの整数値、新しい text、 ntext、または image 文字列がデータ行に直接格納されます。 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) 以降。 TRUE、ON、または1すると、指定されたテーブルが vardecimal ストレージ形式に対して有効になります。 FALSE、OFF、または0すると、テーブルは vardecimal ストレージ形式に対して有効になりません。 vardecimalストレージ形式は、データベースがsp_db_vardecimal_storage_formatを使用してvardecimalストレージ形式に対して有効になっている場合にのみ有効にすることができます。 SQL Server 2008 (10.0.x) 以降では、 vardecimal ストレージ形式は非推奨になりました。 代わりに ROW 圧縮を使用してください。 詳細については、「データ圧縮」を参照してください。 0 が既定値です。

[ @OptionValue = ] 'OptionValue'

@OptionNameが有効 (TRUE、ON、または1) か無効 (FALSE、OFF、または0) かを指定します。 @OptionValue は varchar(12)で、既定値はありません。 @OptionValue では大文字と小文字が区別されません。

行内のテキスト オプションの場合、有効なオプション値は、24から7000までの0、ON、OFF、または整数です。 @OptionValueがONされると、制限の既定値は 256 バイトになります。

リターン コードの値

0 (成功) またはエラー番号 (失敗)。

解説

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

sp_tableoptionの行内のテキスト オプションは、テキスト列を含むテーブルでのみ有効または無効にすることができます。 テーブルにテキスト列がない場合、SQL Server はエラーを発生させます。

行内テキスト オプションが有効になっている場合、 @OptionValue パラメーターを使用すると、ユーザーは BLOB の行に格納する最大サイズを指定できます。 既定値は 256 バイトですが、値の範囲は 24 ~ 7,000 バイトです。

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

• 行内のテキストが有効になっています。
• 文字列の長さは、 @OptionValueで指定された制限よりも短くなります。
• データ行には十分な空き領域があります。

BLOB 文字列がデータ行に格納されている場合、 text、 ntext、または image の読み取りと書き込み 文字列は、文字およびバイナリ文字列の読み取りまたは書き込みと同じ速度で実行できます。 SQL Server は、BLOB 文字列の読み取りまたは書き込みに個別のページにアクセスする必要はありません。

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

テーブルの行に格納されている BLOB 文字列とポインターは、可変長文字列と同様に扱われます。 SQL Server では、文字列またはポインターの格納に必要なバイト数のみが使用されます。

行内のテキストが最初に有効になると、既存の BLOB 文字列はすぐに変換されません。 文字列は、それらが更新されたときに初めて変換されます。 同様に、行内のテキスト オプションの制限が引き上げられると、 text、 ntext、または image データ行内の文字列は、更新されるまで新しい制限に従うように変換されません。

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)) に変換する場合、ほとんどのステートメントで環境内の大きな値型の列が参照されない場合は、最適なパフォーマンスを得るために 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 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)