sp_tableoption (Transact-SQL)

  • [アーティクル]

更新 : 2006 年 12 月 12 日

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

ms173530.note(ja-jp,SQL.90).gif重要 :
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_pattern のデータ型は nvarchar(776) です。既定値はありません。

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

    値 説明

    table lock on bulk load

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

    insert row lock

    SQL Server 2005 ではサポートされていません。

    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 列は、ルートを指す 16 バイトのポインタと共に、行外に格納されます。

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

    vardecimal storage format

    TRUE、ON、または 1 の場合、指定されたテーブルでは vardecimal ストレージ形式が有効です。FALSE、OFF、または 0 の場合、そのテーブルでは vardecimal ストレージ形式が無効です。vardecimal ストレージ形式を有効にできるのは、sp_db_vardecimal_storage_format を使用して、データベースで vardecimal ストレージ形式を有効にしている場合だけです。vardecimal ストレージ形式の詳細については、「decimal データの可変長での格納」を参照してください。このオプションには、SQL Server 2005 Service Pack 2 が必要です。vardecimal ストレージ形式は、SQL Server 2005 Enterprise Edition、Developer Edition、および Evaluation Edition でのみ使用できます。

  • [ @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 バイトです。

解説

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 オプションの制限値を大きくしても、それらの文字列が更新されない限り、新しい制限値に変換されません。

ms173530.note(ja-jp,SQL.90).gifメモ :
text in row オプションを無効にする、またはオプションの制限値を小さくした場合は、BLOB を変換する必要があります。したがって、変換される BLOB 文字列の数によっては、処理に時間がかかる場合があります。変換処理中は、テーブルがロックされます。

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

text in row は、TEXTPTR、WRITETEXT、UPDATETEXT、および READTEXT 関数をサポートします。ユーザーは、SUBSTRING() 関数を使用して BLOB の複数の部分を読み取ることができますが、行内テキスト ポインタの実行期間および数の制限値は、その他のテキスト ポインタとは異なることに注意する必要があります。詳細については、「ntext 型、text 型、および image 型のデータの管理」を参照してください。

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

権限

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

戻り値

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

例

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

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

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

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

次の例では、decimal データ型を vardecimal ストレージ形式で格納するように 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 'AdventureWorks', 'ON';
GO
USE AdventureWorks;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting', 
   'vardecimal storage format', 'ON';

参照

関連項目

sys.tables (Transact-SQL)
OBJECTPROPERTY (Transact-SQL)
システム ストアド プロシージャ (Transact-SQL)
データベース エンジンのストアド プロシージャ (Transact-SQL)

その他の技術情報

行内データ

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース 履歴

2006 年 12 月 12 日
新しい内容 :
  • SQL Server 2005 Service Pack 2 で使用できる vardecimal ストレージ形式オプションを追加しました。