ALTER TABLE (Transact-SQL)

[アーティクル]
12/16/2008

更新 : 2007 年 9 月 15 日

列と制約の変更、追加、削除、パーティションの再割り当て、制約とトリガの無効化や有効化などの方法で、テーブル定義を変更します。

構文

 ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name  {      ALTER COLUMN column_name      {          [ type_schema_name. ] type_name [ ( { precision [ , scale ]              | max | xml_schema_collection } ) ]          [ COLLATE collation_name ]          [ NULL | NOT NULL ]      | {ADD | DROP } { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION}     }      | [ WITH { CHECK | NOCHECK } ] ADD      {          <column_definition>       | <computed_column_definition>       | <table_constraint>      } [ ,...n ]     | DROP      {          [ CONSTRAINT ] constraint_name          [ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]         | COLUMN column_name      } [ ,...n ]      | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT          { ALL | constraint_name [ ,...n ] }      | { ENABLE | DISABLE } TRIGGER          { ALL | trigger_name [ ,...n ] }     | SWITCH [ PARTITION source_partition_number_expression ]         TO target_table          [ PARTITION target_partition_number_expression ] } [ ; ]  <drop_clustered_constraint_option> ::=       {          MAXDOP = max_degree_of_parallelism       | ONLINE = {ON | OFF }       | MOVE TO { partition_scheme_name ( column_name ) | filegroup           | "default"}     }

引数

database_name
テーブルが作成されたデータベースの名前を指定します。

schema_name
テーブルが属しているスキーマの名前を指定します。

table_name
変更するテーブルの名前を指定します。テーブルが現在のデータベースにないか、現在のユーザーが所有するスキーマに含まれていない場合は、データベースとスキーマを明示的に指定する必要があります。

ALTER COLUMN
指定した列に変更を加えます。互換性レベルが 65 以下の場合、ALTER COLUMN は使用できません。詳細については、「sp_dbcmptlevel (Transact-SQL)」を参照してください。

変更する列に、次の列は指定できません。
- timestamp 型の列。
- テーブルの ROWGUIDCOL。
- 計算列、または計算列の中で使用される列。
- インデックスの中で使用されている列。ただし、列が varchar 型、nvarchar 型、または varbinary 型で、データ型が変更されず、新しいサイズが前のサイズ以上になる場合、およびインデックスが PRIMARY KEY 制約の結果ではない場合を除きます。
- CREATE STATISTICS ステートメントによって生成された統計内の列。最初に、DROP STATISTICS ステートメントで統計を削除します。クエリオプティマイザによって自動的に生成された統計は、ALTER COLUMN によって自動的に削除されます。
- PRIMARY KEY 制約、または [FOREIGN KEY] REFERENCES 制約で使用されている列。
- CHECK 制約、または UNIQUE 制約で使用されている列。ただし、CHECK 制約または UNIQUE 制約の中で使用されている可変長列の長さを変更することはできます。
- 既定の定義に関連付けられている列。ただし、データ型を変更しなければ、列の長さ、有効桁数、または小数点以下桁数は変更できます。
  text 型、ntext 型、および image 型の列は、次の場合にのみ変更できます。
  - text 型から varchar(max) 型、nvarchar(max) 型、または xml 型への変更
  - ntext 型から varchar(max) 型、nvarchar(max) 型、または xml 型への変更
  - image tovarbinary(max)
    データ型を変更すると、データが変更される可能性があります。たとえば、nchar 型または nvarchar 型の列を char 型または varchar 型に変更すると、拡張文字が変換される場合があります。詳細については、「CAST および CONVERT (Transact-SQL)」を参照してください。列の有効桁数または小数点以下桁数を減らすと、データが切り捨てられる可能性があります。
    パーティションテーブルの列のデータ型は変更できません。

column_name
変更、追加、または削除する列の名前を指定します。column_name には、最大 128 文字まで指定できます。timestamp データ型で作成される新しい列の場合は、 column_name を省略できます。timestamp 型の列に対して column_name の指定を省略すると、timestamp という名前が使用されます。

[ type_schema_name**.** ] type_name
変更する列の新しいデータ型、または追加する列のデータ型を指定します。type_name は、パーティションテーブルの既存の列には指定できません。type_name には次のいずれかを指定できます。
- SQL Server 2005 システムデータ型。
- SQL Server システムデータ型に基づく別名データ型。別名データ型は、CREATE TYPE ステートメントで作成した後、テーブル定義で使用できます。
- .NET Framework ユーザー定義型とそれが属するスキーマ。.NET Framework ユーザー定義型は、CREATE TYPE ステートメントで作成した後、テーブル定義で使用できます。
変更する列に対して type_name を指定する場合の条件を次に示します。
- 以前のデータ型は、新しいデータ型に自動的に変換される必要があります。
- type_name に timestamp 型を指定することはできません。
- ANSI_NULL の既定値は ALTER COLUMN に対して常にオンです。指定しない場合、列では NULL 値が許容されます。
- ANSI_PADDING の埋め込みは ALTER COLUMN に対して常にオンです。
- 変更する列が ID 列の場合、new_data_type は、ID プロパティをサポートするデータ型であることが必要です。
- SET ARITHABORT の現在の設定値は無視されます。ALTER TABLE は、ARITHABORT がオンに設定されている場合と同様に動作します。
メモ :

COLLATE 句を指定しないで列のデータ型を変更すると、照合順序が、データベースの既定値とは異なってしまう場合があります。

メモ :
COLLATE 句を指定しないで列のデータ型を変更すると、照合順序が、データベースの既定値とは異なってしまう場合があります。

precision
指定するデータ型の有効桁数を指定します。有効桁数値の詳細については、「有効桁数、小数点以下桁数、および長さ (Transact-SQL)」を参照してください。

scale
指定するデータ型の小数点以下桁数を指定します。小数点以下桁数値の詳細については、「有効桁数、小数点以下桁数、および長さ (Transact-SQL)」を参照してください。

max
varchar 型、nvarchar 型、および varbinary 型にのみ適用されます。2^31-1 バイトの文字、バイナリデータ、Unicode データを格納する場合に使用します。

xml_schema_collection
xml 型にのみ適用されます。XML スキーマを関連付ける場合に使用します。xml 列をスキーマコレクションに入力するには、先に CREATE XML SCHEMA COLLECTION を使ってスキーマコレクションをデータベース内に作成する必要があります。

COLLATE < collation_name >
変更する列の新しい照合順序を指定します。照合順序を指定しない場合は、データベースの既定の照合順序が列に割り当てられます。照合順序名には、Windows 照合順序名または SQL 照合順序名を指定できます。照合順序名の一覧と詳細については、「Windows 照合順序名 (Transact-SQL)」および「SQL 照合順序名 (Transact-SQL)」を参照してください。

COLLATE 句は、char 型、varchar 型、nchar 型、および nvarchar 型の列の照合順序のみを変更する場合に使用できます。ユーザー定義の別名データ型列の照合順序を変更するには、別の ALTER TABLE ステートメントを実行して列を SQL Server システムデータ型に変更し、その照合順序を変更した後、その列を別名データ型に戻します。

次の条件の 1 つ以上に該当する場合、ALTER COLUMN で照合順序を変更することはできません。
- CHECK 制約、FOREIGN KEY 制約、またはその列を参照する計算列が変更された場合。
- 列にインデックス、統計、またはフルテキストインデックスが作成された場合。変更する列に対して自動的に作成された統計は、列の照合順序を変更すると削除されます。
- スキーマバインドビューまたは関数で列が参照されている場合。
詳細については、「COLLATE (Transact-SQL)」を参照してください。

NULL | NOT NULL
列で NULL 値が許容されるかどうかを指定します。NULL 値が許容されない列は、既定値が列に指定されているか、テーブルが空の場合にのみ、ALTER TABLE で追加できます。NOT NULL を計算列に対して指定するには、PERSISTED も指定されている必要があります。新しい列で NULL 値が許容され、既定値を指定しない場合、テーブル内の各行の新しい列には NULL 値が格納されます。新しい列で NULL 値が許容され、新しい列と共に既定の定義を追加した場合は、WITH VALUES を使用して、テーブル内にある各行の新しい列に既定値を格納できます。

新しい列で NULL 値が許容されず、テーブルが空でない場合は、新しい列と共に DEFAULT 定義を追加する必要があります。これによって、既存の各行の新しい列には既定値が自動的に読み込まれます。

ALTER COLUMN に NULL を指定することによって、NOT NULL 列で NULL 値が許容されるように設定できます。ただし、PRIMARY KEY 制約の列は除きます。ALTER COLUMN に NOT NULL を指定できるのは、列に NULL 値が格納されていないときだけです。ALTER COLUMN NOT NULL を指定する前に、NULL 値を別の値に更新しておく必要があります。たとえば次のように行います。

UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL

CREATE TABLE ステートメントまたは ALTER TABLE ステートメントでテーブルを作成または変更すると、データベースとセッションの設定によって、列定義で使われているデータ型の NULL 許容属性が影響を受けたり、場合によっては無効になることがあります。計算列でない場合は、常に列を明示的に NULL または NOT NULL として定義することをお勧めします。ユーザー定義データ型を使用する場合は、列でデータ型の既定の NULL 許容属性を使用できるように設定することをお勧めします。詳細については、「CREATE TABLE (Transact-SQL)」を参照してください。

メモ :
ALTER COLUMN に NULL または NOT NULL を指定した場合は、new_data_type [(precision [, scale ])] も指定する必要があります。データ型、有効桁数、小数点以下桁数を変更しない場合は、その列の現在の値を指定します。

[ {ADD | DROP} ROWGUIDCOL ]
指定した列に対して ROWGUIDCOL プロパティが追加または削除されます。ROWGUIDCOL は、列が行の GUID 列であることを示します。各テーブルでは 1 つの uniqueidentifier 列のみを ROWGUIDCOL 列として指定できます。ROWGUIDCOL プロパティは、uniqueidentifier 列にのみ割り当てることができます。ROWGUIDCOL は、ユーザー定義データ型の列に割り当てることはできません。

ROWGUIDCOL では列に一意な値が格納されるわけではなく、またテーブルに挿入される新しい行に対して値は自動的に生成されません。各列に一意な値を生成するには、INSERT ステートメントで NEWID 関数を使用するか、列の既定値として NEWID 関数を指定します。

[ {ADD | DROP} PERSISTED ]
指定した列に対して PERSISTED プロパティが追加または削除されます。列は、決定的な式で定義される計算列であることが必要です。PERSISTED として指定した列では、SQL Server 2005 データベースエンジンによって物理的にテーブルに計算値が格納され、計算列が依存している他の列が更新されるとその計算値も更新されます。計算列を PERSISTED とマークすることにより、決定的であるが不正確な式によって定義されている計算列にインデックスを作成できます。詳細については、「計算列に対するインデックスの作成」を参照してください。

パーティションテーブルのパーティション分割列として使用される計算列は、明示的に PERSISTED とマークする必要があります。

NOT FOR REPLICATION
レプリケーションエージェントで挿入操作が実行されるときに、ID 列の値を増分しないことを指定します。この句は column_name が ID 列の場合にのみ指定できます。詳細については、「NOT FOR REPLICATION を使用した制約、ID、およびトリガの制御」を参照してください。

WITH CHECK | WITH NOCHECK
新しく追加または再有効化された FOREIGN KEY 制約や CHECK 制約に対して、テーブル内のデータを検証するかどうかを指定します。指定しない場合、新しい制約には WITH CHECK が使用され、再有効化された制約には WITH NOCHECK が使用されます。

既存のデータに対して新しい CHECK 制約または FOREIGN KEY 制約を検証しない場合は、WITH NOCHECK を使用します。ごくわずかな例外を除き、このオプションは推奨されません。新しい制約は、その後データが更新されるたびに評価されます。制約の追加時、制約違反があっても WITH NOCHECK が指定されていたために検出されなかった場合、その後の更新で制約に従わないデータが使用されると行の更新が失敗する可能性があります。

クエリオプティマイザでは、WITH NOCHECK が定義されている制約は考慮されません。このような制約は、ALTER TABLE table CHECK CONSTRAINT ALL を使用して再び有効にするまで無視されます。

ADD
1 つ以上の列定義、計算列定義、またはテーブル制約を追加します。

DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name }
テーブルから constraint_name または column_name を削除します。複数の列と制約を指定できます。互換性レベルが 65 以下の場合、DROP COLUMN は使用できません。詳細については、「sp_dbcmptlevel (Transact-SQL)」を参照してください。

ユーザー定義またはシステム提供の制約名は、sys.check_constraint、sys.default_constraints、sys.key_constraints、および sys.foreign_keys カタログビューにクエリを実行して確認できます。

XML インデックスがテーブルに存在する場合、PRIMARY KEY 制約は削除できません。

次の条件に該当する列は削除できません。

インデックスで使用されている列
CHECK、FOREIGN KEY、UNIQUE、または PRIMARY KEY 制約で使用されている列
DEFAULT キーワードで定義されたデフォルトに関連付けられている列、またはデフォルトオブジェクトにバインドしている列
ルールにバインドしている列

メモ :
列を削除しても、列のディスク領域は再確保されません。テーブルの行サイズが制限に近いか制限を超えている場合は、必要に応じて、削除した列のディスク領域を再確保してください。領域を再確保するには、テーブルでクラスタ化インデックスを作成するか、ALTER INDEX を使って既存のクラスタ化インデックスを再構築します。

列を削除しても、列のディスク領域は再確保されません。テーブルの行サイズが制限に近いか制限を超えている場合は、必要に応じて、削除した列のディスク領域を再確保してください。領域を再確保するには、テーブルでクラスタ化インデックスを作成するか、ALTER INDEX を使って既存のクラスタ化インデックスを再構築します。

WITH <drop_clustered_constraint_option>
1 つ以上の削除クラスタ化制約オプションを設定します。

MAXDOP = max_degree_of_parallelism
操作中は、max degree of parallelism 構成オプションを無効にします。詳細については、「max degree of parallelism オプション」を参照してください。

並列プランの実行で使用されるプロセッサ数を制限するには、MAXDOP オプションを使用します。最大数は 64 プロセッサです。

max_degree_of_parallelism は次のいずれかの値をとります。
- 1
  並列プラン生成を抑制します。
- >1
  並列インデックス操作で使用される最大プロセッサ数を、指定数に制限します。
- 0 (既定値)
  現在のシステムワークロードに基づいて、実際の数以下のプロセッサを使用します。
詳細については、「並列インデックス操作の構成」を参照してください。

メモ :

並列インデックス操作は、SQL Server 2005 Enterprise Edition でのみ実行できます。

メモ :
並列インデックス操作は、SQL Server 2005 Enterprise Edition でのみ実行できます。

ONLINE = { ON | OFF }
インデックス操作時のクエリとデータ変更で、基になるテーブルと関連するインデックスを使用できるかどうかを指定します。既定値は OFF です。
- ON
  長期のテーブルロックは、インデックス操作の間は保持されません。インデックス操作の主要フェーズの間、ソーステーブルではインテント共有 (IS) ロックのみが保持されます。このことにより、基になるテーブルとインデックスに対するクエリや更新は続行できます。操作の開始時、非常に短い時間ですが、ソースオブジェクトでは共有 (S) ロックが保持されます。また操作の終了時は、短い時間ですが、非クラクタ化インデックスが作成される場合は、ソースオブジェクト上で共有 (S) ロックの取得が行われます。また、クラスタ化インデックスがオンラインで作成または削除され、クラスタ化または非クラスタ化インデックスが再構築される場合は、SCH-M (スキーマ修正) ロックが取得されます。インデックスがローカル一時テーブルで作成される場合は、ONLINE は ON にできません。
- OFF
  テーブルロックは、インデックス操作の間適用されます。クラスタ化インデックスを作成、再構築、または削除するオフラインインデックス操作や、非クラスタ化インデックスを再構築または削除するオフラインインデックス操作では、テーブルのスキーマ修正 (Sch-M) ロックが取得されます。このため、操作中は、すべてのユーザーは基になるテーブルにアクセスできません。非クラスタ化インデックスを作成するオフラインインデックス操作では、テーブルの共有 (S) ロックが取得されます。この場合は、基になるテーブルに対して更新は許可されませんが、SELECT ステートメントなどの読み取り操作は許可されます。
詳細については、「オンラインインデックス操作の動作原理」を参照してください。ロックの詳細については、「ロックモード」を参照してください。

メモ :

オンラインインデックス操作は、SQL Server 2005 Enterprise Edition でのみ実行できます。

メモ :
オンラインインデックス操作は、SQL Server 2005 Enterprise Edition でのみ実行できます。

MOVE TO ( partition_scheme_name ( column_name [ 1**,** ... n] ) | filegroup | "default"}
クラスタ化インデックスのリーフレベルに格納されているデータ行を移動する位置を指定します。テーブルは新しい位置に移動します。

メモ :
ここでは、default はキーワードではありません。これは、既定のファイルグループの識別子で、MOVE TO "default" または OMOVE TO [default] のように区切り記号で区切る必要があります。"default" を指定する場合は、現在のセッションに対して QUOTED_IDENTIFIER オプションが ON になっている必要があります。これは既定の設定です。詳細については、「SET QUOTED_IDENTIFIER (Transact-SQL)」を参照してください。

ここでは、default はキーワードではありません。これは、既定のファイルグループの識別子で、MOVE TO "default" または OMOVE TO [default] のように区切り記号で区切る必要があります。"default" を指定する場合は、現在のセッションに対して QUOTED_IDENTIFIER オプションが ON になっている必要があります。これは既定の設定です。詳細については、「SET QUOTED_IDENTIFIER (Transact-SQL)」を参照してください。

{ CHECK | NOCHECK} CONSTRAINT
constraint_name を有効または無効にします。このオプションは、FOREIGN KEY 制約と CHECK 制約でのみ使用できます。NOCHECK を指定すると、制約は無効になり、今後列に行われる挿入または更新は、制約条件に対して検証されません。DEFAULT、PRIMARY KEY、および UNIQUE 制約は無効にできません。

ALL
すべての制約を、NOCHECK オプションで無効にするか CHECK オプションで有効にします。

{ENABLE | DISABLE} TRIGGER
trigger_name を有効または無効にします。トリガを無効にしてもテーブルのトリガ定義は引き続き有効ですが、テーブルに対して INSERT、UPDATE、または DELETE ステートメントを実行しても、トリガを再び有効にするまではトリガ内の動作が実行されることはありません。

ALL
テーブル内のすべてのトリガを有効または無効にします。

trigger_name
無効または有効にするトリガの名前を指定します。

SWITCH [ PARTITION source_partition_number_expression ] TO target_table [ PARTITION target_ partition_number_expression ]
次のいずれかの方法でデータのブロックを切り替えます。
- テーブルのすべてのデータを、既に存在するパーティションテーブルにパーティションとして再度割り当てます。
- 1 つのパーティションテーブルから別のパーティションテーブルに、パーティションを切り替えます。
- パーティションテーブルの 1 つのパーティションにあるすべてのデータを、既存の非パーティションテーブルに再度割り当てます。
table がパーティションテーブルの場合は、source_partition_number_expression を指定する必要があります。target_table がパーティション分割されている場合は、target_partition_number_expression を指定する必要があります。テーブルのデータを既に存在するパーティションテーブルにパーティションとして再度割り当てる場合、または 1 つのパーティションテーブルから別のパーティションテーブルにパーティションを切り替える場合は、対象のパーティションが存在し、空になっている必要があります。

1 つのパーティションのデータを再度割り当てて単一のテーブルを作成する場合は、対象テーブルが既に作成されており、空になっている必要があります。ソーステーブルまたはパーティションと、対象テーブルまたはパーティションは、両方とも同じファイルグループに存在する必要があります。また、対応するインデックスまたはインデックスパーティションも、同じファイルグループに存在する必要があります。切り替えるパーティションには、多くの追加の制限が適用されます。詳細については、「パーティションの切り替えを使用した効率的なデータの転送」を参照してください。table と target_table を同じにはできません。target_table には複数の要素で構成される識別子を指定できます。

source_partition_number_expression および target_partition_number_expression は、変数や関数を参照できる定数式です。これらの定数式には、ユーザー定義型変数とユーザー定義関数が含まれます。これらの定数式で Transact-SQL 式を参照することはできません。

メモ :

レプリケートされたテーブルで SWITCH ステートメントを使用することはできません。

メモ :
レプリケートされたテーブルで SWITCH ステートメントを使用することはできません。

解説

新しいデータ行を追加するには、INSERT を使用します。データ行を削除するには、DELETE または TRUNCATE TABLE を使用します。既存の行の値を変更するには、UPDATE を使用します。

プロシージャキャッシュにテーブルを参照する実行プランがある場合、ALTER TABLE ではこれらの実行プランに対して、次の実行時に再コンパイルするというマークが付けられます。

列のサイズの変更

ALTER COLUMN 句で列のデータ型の新しいサイズを指定して、列の長さ、有効桁数、または小数点以下桁数を変更できます。列内にデータが存在する場合、新しいサイズはデータの最大長よりも小さいサイズにすることはできません。また、列が varchar 型、nvarchar 型、または varbinary 型で、インデックスが PRIMARY KEY 制約の結果ではない場合を除き、列をインデックスで定義することはできません。例 P を参照してください。

ロックと ALTER TABLE

ALTER TABLE で指定した変更は、直ちに実装されます。変更でテーブル内の行の修正が必要になる場合、ALTER TABLE では行が更新されます。ALTER TABLE では、変更中にテーブルのメタデータが他の接続で参照されないように、テーブルに対してスキーマ修正ロックが取得されます。ただし、オンラインインデックス操作の終了時に、非常に短い時間 SCH-M ロックを必要とする場合は、このロックの取得は行われません。ALTER TABLE...SWITCH 操作では、ロックはソーステーブルと対象テーブルの両方に対して取得されます。テーブルに加えられた変更はログに記録され、完全に復旧できます。列の削除や既定値を伴う NOT NULL 列の追加など、きわめて大きなテーブル内のすべての行に影響する変更は、その実行終了までに長い時間がかかり、多くのログレコードが生成されます。このような ALTER TABLE ステートメントは、多くの行に影響する INSERT、UPDATE、DELETE の各ステートメントと同じように、十分な注意を払って実行する必要があります。

並列プランの実行

SQL Server 2005 Enterprise Edition では、単一の ALTER TABLE ADD (インデックスベース) CONSTRAINT または DROP (クラスタ化インデックス) CONSTRAINT ステートメントの実行に使用されるプロセッサ数は、max degree of parallelism 構成オプションと現在のワークロードによって決定されます。データベースエンジンでシステムがビジー状態であることが検出されると、ステートメントの実行前に自動的に操作の並列処理の次数が減らされます。MAXDOP オプションを指定すると、ステートメントの実行に使用されるプロセッサ数を手動で構成できます。

パーティションテーブル

ALTER TABLE は、パーティションテーブルを対象にした SWITCH 操作の実行に使用できるほか、非パーティションテーブルと同様に、パーティションテーブルの列、制約、トリガの状態を変更する場合にも使用できます。ただし、このステートメントを使用して、テーブル自体のパーティション分割方法を変更することはできません。パーティションテーブルを再びパーティション分割するには、ALTER PARTITION SCHEME および ALTER PARTITION FUNCTION を使用します。また、パーティションテーブルの列のデータ型を変更することもできません。

スキーマバインドビューによるテーブルへの制限

スキーマバインドビューのあるテーブルに ALTER TABLE ステートメントを実行するときに適用される制約は、単純なインデックスのあるテーブルを変更する場合に現在適用されている制約と同じです。列の追加は許可されますが、任意のスキーマバインドビューに含まれる列を削除または変更することはできません。ALTER TABLE ステートメントによって、スキーマバインドビューで使用されている列の変更が要求された場合、ALTER TABLE は失敗し、データベースエンジンではエラーメッセージが表示されます。スキーマバインドビューとインデックス付きビューの詳細については、「CREATE VIEW (Transact-SQL)」を参照してください。

ベーステーブルを参照するスキーマバインドビューを作成しても、そのテーブルに対するトリガの追加や削除には影響はありません。

インデックスと ALTER TABLE

制約が削除されると、制約の要素として作成されたインデックスも削除されます。CREATE INDEX で作成されたインデックスは、DROP INDEX で削除する必要があります。ALTER INDEX ステートメントを使用すると、制約定義の要素であるインデックスを再構築できます。ALTER TABLE で制約を削除して再び追加する必要はありません。

列に基づくすべてのインデックスと制約を削除してからでないと、列は削除できません。

クラスタ化インデックスを作成した制約が削除されると、クラスタ化インデックスのリーフレベルに格納されていたデータ行は、非クラスタ化テーブルに格納されるようになります。SQL Server 2005 では、単一のトランザクションで MOVE TO オプションを指定することにより、クラスタ化インデックスを削除し、結果テーブルを別のファイルグループやパーティション構成に移動できます。MOVE TO オプションには次の制限があります。

MOVE TO は、インデックス付きビューまたは非クラスタ化インデックスに対しては有効ではありません。
パーティション構成またはファイルグループは、既に存在している必要があります。
MOVE TO を指定しない場合、テーブルは、クラスタインデックスに定義されたものと同じパーティション構成またはファイルグループに配置されます。

クラスタ化インデックスを削除すると、ONLINE = ON オプションを指定できます。このため、DROP INDEX トランザクションによって、基になるデータや関連する非クラスタ化インデックスに対するクエリおよび変更がブロックされることはありません。

ONLINE = ON には次の制限があります。

ONLINE = ON は、無効化されたクラスタ化インデックスに対しては有効ではありません。無効化されたインデックスは、ONLINE = OFF を使って削除する必要があります。
一度に 1 つのインデックスのみを削除できます。
ONLINE = ON は、インデックス付きビュー、非クラスタ化インデックス、またはローカル一時テーブル上のインデックスに対しては有効ではありません。

既存のクラスタ化インデックスを削除するには、クラスタ化インデックスの大きさと同じ一時ディスク領域が必要です。この追加領域は、操作が完了するとすぐに解放されます。

メモ :
<drop_clustered_constraint_option> に示されているオプションは、テーブル上のクラスタ化インデックスに適用され、ビュー上のクラスタ化インデックスまたは非クラスタ化インデックスには適用できません。

スキーマ変更のレプリケート

既定では、SQL Server パブリッシャでパブリッシュされたテーブルに ALTER TABLE を実行すると、すべての SQL Server サブスクライバに変更が反映されます。この機能にはいくつか制限事項があります。この機能は無効にできます。詳細については、「パブリケーションデータベースでのスキーマの変更」を参照してください。

権限

テーブルに対する ALTER 権限が必要です。

ALTER TABLE 権限は、ALTER TABLE SWITCH ステートメントに含まれる 2 つのテーブルに適用されます。切り替えられるデータには、対象テーブルのセキュリティが継承されます。

ALTER TABLE ステートメント内の列が、共通言語ランタイム (CLR) ユーザー定義型または別名データ型として定義されている場合は、そのデータ型に対する REFERENCES 権限が必要です。

例

A. 新しい列を追加する

次の例では、NULL 値を許容し、DEFAULT 定義で値が提供されない列を追加します。新しい列では、各行に NULL が設定されます。

CREATE TABLE dbo.doc_exa (column_a INT) ;
GO
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
GO
EXEC sp_help doc_exa ;
GO
DROP TABLE dbo.doc_exa ;
GO

B. 列を削除する

次の例では、テーブルを変更して列を削除します。

CREATE TABLE dbo.doc_exb (column_a INT, column_b VARCHAR(20) NULL) ;
GO
ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;
GO
EXEC sp_help doc_exb ;
GO
DROP TABLE dbo.doc_exb ;
GO

C. 列のデータ型を変更する

次の例では、テーブルの列を INT 型から DECIMAL 型に変更します。

CREATE TABLE dbo.doc_exy (column_a INT ) ;
GO
INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;
GO
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;
GO
DROP TABLE dbo.doc_exy ;
GO

D. 制約を含む列を追加する

次の例では、UNIQUE 制約を含む新しい列を追加します。

CREATE TABLE dbo.doc_exc (column_a INT) ;
GO
ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL 
    CONSTRAINT exb_unique UNIQUE ;
GO
EXEC sp_help doc_exc ;
GO
DROP TABLE dbo.doc_exc ;
GO

E. 既存の列に検証なしの CHECK 制約を追加する

次の例では、テーブル内の既存の列に制約を追加します。列には制約に違反する値があります。このため、制約が既存の行に対して検証されないよう、また制約を追加できるよう、WITH NOCHECK を使用します。

CREATE TABLE dbo.doc_exd ( column_a INT) ;
GO
INSERT INTO dbo.doc_exd VALUES (-1) ;
GO
ALTER TABLE dbo.doc_exd WITH NOCHECK 
ADD CONSTRAINT exd_check CHECK (column_a > 1) ;
GO
EXEC sp_help doc_exd ;
GO
DROP TABLE dbo.doc_exd ;
GO

F. 既存の行に DEFAULT 制約を追加する

次の例では、2 つの列を含んだテーブルを作成し、最初の列には値を挿入し、もう 1 つの列は NULL のままにします。2 番目の列には DEFAULT 制約を追加します。既定値が適用されていることを確認するには、最初の列にさらに値を挿入し、テーブルに対してクエリを実行します。

CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO dbo.doc_exz (column_a) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.doc_exz ;
GO
DROP TABLE dbo.doc_exz ;
GO

G. 制約を含む列を複数追加する

次の例では、制約を含む列を複数追加します。制約は新しい列ごとに定義されます。先頭の新しい列には IDENTITY プロパティが設定され、テーブル内の各行では、ID 列に新しい増分値が挿入されます。

CREATE TABLE dbo.doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE dbo.doc_exe ADD 

-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY, 

-- Add a column that references another column in the same table.
column_c INT NULL  
CONSTRAINT column_c_fk 
REFERENCES doc_exe(column_a),

-- Add a column with a constraint to enforce that 
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL 
CONSTRAINT column_d_chk
CHECK 
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE dbo.doc_exe ;
GO

H. NULL 値を許容する列を既定値と共に追加する

次の例では、NULL 値を許容する列を DEFAULT 定義と共に追加し、WITH VALUES を使用して、テーブル内の既存の各行に値を格納します。WITH VALUES を使用しない場合、新しい列には NULL 値が格納されます。

USE AdventureWorks ; 
GO
CREATE TABLE dbo.doc_exf ( column_a INT) ;
GO
INSERT INTO dbo.doc_exf VALUES (1) ;
GO
ALTER TABLE dbo.doc_exf 
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO

I. 制約を無効化および再有効化する

次の例では、データに対して許可される給料を制限する制約を無効にします。ここでは ALTER TABLE と共に NOCHECK CONSTRAINT を使用して制約を無効にし、通常は制約違反となる挿入を許可します。次に CHECK CONSTRAINT を使用して制約を再び有効にします。

CREATE TABLE dbo.cnst_example 
(id INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 salary MONEY NOT NULL
    CONSTRAINT salary_cap CHECK (salary < 100000)
);

-- Valid inserts
INSERT INTO dbo.cnst_example VALUES (1,'Joe Brown',65000);
INSERT INTO dbo.cnst_example VALUES (2,'Mary Smith',75000);

-- This insert violates the constraint.
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Disable the constraint and try again.
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Re-enable the constraint and try another insert; this will fail.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4,'Eric James',110000) ;

J. 制約を削除する

次の例では、テーブルから UNIQUE 制約を削除します。

CREATE TABLE dbo.doc_exc ( column_a INT
CONSTRAINT my_constraint UNIQUE) ;
GO
ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint ;
GO
DROP TABLE dbo.doc_exc ;
GO

K. テーブル間でパーティションを切り替える

次の例では、パーティションテーブルを作成します。ここでは、データベースにパーティション構成 myRangePS1 が既に作成されていることが前提となります。次に、パーティションテーブルと同じ構造で、PartitionTable テーブルの PARTITION 2 と同じファイルグループに、非パーティションテーブルを作成し、PartitionTable テーブルの PARTITION 2 のデータを、NonPartitionTable テーブルに切り替えます。

CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
CREATE TABLE NonPartitionTable (col1 int, col2 char(10))
ON test2fg ;
GO
ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ;
GO

L. トリガを無効化および再有効化する

次の例では、ALTER TABLE の DISABLE TRIGGER オプションを使ってトリガを無効にし、通常はトリガ違反となる挿入を許可します。次に、ENABLE TRIGGER を使ってトリガを再び有効にします。

CREATE TABLE dbo.trig_example 
(id INT, 
name VARCHAR(12),
salary MONEY) ;
GO
-- Create the trigger.
CREATE TRIGGER dbo.trig1 ON dbo.trig_example FOR INSERT
AS
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
    print 'TRIG1 Error: you attempted to insert a salary > $100,000'
    ROLLBACK TRANSACTION
END ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (1,'Pat Smith',100001) ;
GO
-- Disable the trigger.
ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1 ;
GO
-- Try an insert that would typically violate the trigger.
INSERT INTO dbo.trig_example VALUES (2,'Chuck Jones',100001) ;
GO
-- Re-enable the trigger.
ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1 ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (3,'Mary Booth',100001) ;
GO

M. PRIMARY KEY 制約をインデックスオプションと共に作成する

次の例では、PRIMARY KEY 制約 PK_TransactionHistoryArchive_TransactionID を作成し、オプション FILLFACTOR、ONLINE、および PAD_INDEX を設定します。結果のクラスタ化インデックスは制約と同じ名前になります。

USE AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK 
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON)
GO

N. ONLINE モードで PRIMARY KEY 制約を削除する

次の例では、ONLINE オプションを ON に設定して PRIMARY KEY 制約を削除します。

USE AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);
GO

O. FOREIGN KEY 制約を追加および削除する

次の例では、ContactBackup テーブルを作成し、Contact テーブルを参照する FOREIGN KEY 制約を追加した後、FOREIGN KEY 制約を削除して、テーブルを変更します。

USE AdventureWorks ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
    REFERENCES Person.Contact (ContactID) ;
ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO
DROP TABLE Person.ContactBackup ;

P. 列のサイズを変更する

次の例では、varchar 列のサイズを拡張し、decimal 列の有効桁数と小数点以下桁数を増やします。列にデータが含まれているため、列のサイズだけ拡張できます。また、col_a は、一意なインデックスで定義されています。データ型が varchar で、インデックスが PRIMARY KEY 制約の結果ではないため、col_a のサイズも拡張できます。

IF OBJECT_ID ( 'dbo.doc_exy', 'U' ) IS NOT NULL 
    DROP TABLE dbo.doc_exy;
GO
-- Create a two-column table with a unique index on the varchar column.
CREATE TABLE dbo.doc_exy ( col_a varchar(5) UNIQUE NOT NULL, col_b decimal (4,2));
GO
INSERT INTO dbo.doc_exy VALUES ('Test', 99.99);
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');
GO
-- Increase the size of the varchar column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_a varchar(25);
GO
-- Increase the scale and precision of the decimal column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_b decimal (10,4);
GO
-- Insert a new row.
INSERT INTO dbo.doc_exy VALUES ('MyNewColumnSize', 99999.9999) ;
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');

参照

その他の技術情報

テーブルの作成と変更
 パブリケーションデータベースでのスキーマの変更

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース	履歴
2007 年 9 月 15 日	変更内容 : 「列のサイズを変更する」のセクションと例 P を追加しました。
2006 年 12 月 12 日	変更内容 : 「構文」と「引数」に NOT FOR REPLICATION 句の位置と意味を明記。 SWITCH 句の対象テーブルを複数の要素で構成される識別子として表現できることを明記。
2006 年 4 月 14 日	新しい内容 : レプリケートされたテーブルで SWITCH ステートメントを使用できないことを説明。
2005 年 12 月 5 日	新しい内容 : DROP NOT FOR REPLICATION 句を構文ダイアグラムと引数の定義一覧に追加。変更内容 : COLLATE 句を構文ダイアグラムの正しい場所に移動。例 M と N を修正。

ALTER TABLE (Transact-SQL)

構文

引数

解説

列のサイズの変更

ロックと ALTER TABLE

並列プランの実行

パーティションテーブル

スキーマバインドビューによるテーブルへの制限

インデックスと ALTER TABLE

スキーマ変更のレプリケート

権限

例

A. 新しい列を追加する

B. 列を削除する

C. 列のデータ型を変更する

D. 制約を含む列を追加する

E. 既存の列に検証なしの CHECK 制約を追加する

F. 既存の行に DEFAULT 制約を追加する

G. 制約を含む列を複数追加する

H. NULL 値を許容する列を既定値と共に追加する

I. 制約を無効化および再有効化する

J. 制約を削除する

K. テーブル間でパーティションを切り替える

L. トリガを無効化および再有効化する

M. PRIMARY KEY 制約をインデックスオプションと共に作成する

N. ONLINE モードで PRIMARY KEY 制約を削除する

O. FOREIGN KEY 制約を追加および削除する

P. 列のサイズを変更する

参照

関連項目

その他の技術情報

ヘルプおよび情報

変更履歴

その他のリソース

ALTER TABLE (Transact-SQL)

構文

引数

解説

列のサイズの変更

ロックと ALTER TABLE

並列プランの実行

パーティション テーブル

スキーマ バインド ビューによるテーブルへの制限

インデックスと ALTER TABLE

スキーマ変更のレプリケート

権限

例

A. 新しい列を追加する

B. 列を削除する

C. 列のデータ型を変更する

D. 制約を含む列を追加する

E. 既存の列に検証なしの CHECK 制約を追加する

F. 既存の行に DEFAULT 制約を追加する

G. 制約を含む列を複数追加する

H. NULL 値を許容する列を既定値と共に追加する

I. 制約を無効化および再有効化する

J. 制約を削除する

K. テーブル間でパーティションを切り替える

L. トリガを無効化および再有効化する

M. PRIMARY KEY 制約をインデックス オプションと共に作成する

N. ONLINE モードで PRIMARY KEY 制約を削除する

O. FOREIGN KEY 制約を追加および削除する

P. 列のサイズを変更する

参照

関連項目

その他の技術情報

ヘルプおよび情報

変更履歴

その他のリソース

パーティションテーブル

スキーマバインドビューによるテーブルへの制限

M. PRIMARY KEY 制約をインデックスオプションと共に作成する