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、または大規模なユーザー定義型の列を持つテーブルの行内動作を制御できます。

Transact-SQL 構文表記規則

構文

sp_tableoption
    [ @TableNamePattern = ] N'TableNamePattern'
    , [ @OptionName = ] 'OptionName'
    , [ @OptionValue = ] 'OptionValue'
[ ; ]

引数

[ @TableNamePattern = ] N'TableNamePattern'

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

[ @OptionName = ] 'OptionName'

テーブル オプション名。 @OptionName は varchar(35)であり、次のいずれかの値を指定できます。

[ @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 データ行内の文字列は、更新されるまで新しい制限に従うように変換されません。

テーブル変数 (テーブル変数を返す関数を含む) では、行内のテキスト オプションが自動的に有効になっており、既定のインライン制限は 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)