ALTER TABLE (Transact-SQL)

[アーティクル]
12/13/2023

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)Microsoft Fabric のウェアハウス

列と制約を変更、追加、または削除して、テーブルの定義を変更します。また、ALTER TABLE では、パーティションを再割り当ておよび再構築したり、制約とトリガーを無効化および有効化したりもします。

重要

ALTER TABLE の構文は、ディスクベーステーブルとメモリ最適化テーブルで異なります。次のリンクから、テーブル型の適切な構文ブロックと、適切な構文の例に直接移動することができます。

ディスクベーステーブル:
構文
使用例
メモリ最適化テーブル
構文
使用例

構文表記規則の詳細については、「Transact-SQL 構文表記規則」を参照してください。

ディスクベーステーブルの構文

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
    ALTER COLUMN column_name
    {
        [ type_schema_name. ] type_name
            [ (
                {
                   precision [ , scale ]
                 | max
                 | xml_schema_collection
                }
            ) ]
        [ COLLATE collation_name ]
        [ NULL | NOT NULL ] [ SPARSE ]
      | { ADD | DROP }
          { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE | HIDDEN }
      | { ADD | DROP } MASKED [ WITH ( FUNCTION = ' mask_function ') ]
    }
    [ WITH ( ONLINE = ON | OFF ) ]
    | [ WITH { CHECK | NOCHECK } ]
  
    | ADD
    {
        <column_definition>
      | <computed_column_definition>
      | <table_constraint>
      | <column_set_definition>
    } [ ,...n ]
      | [ system_start_time_column_name datetime2 GENERATED ALWAYS AS ROW START
                [ HIDDEN ] [ NOT NULL ] [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,
                system_end_time_column_name datetime2 GENERATED ALWAYS AS ROW END
                   [ HIDDEN ] [ NOT NULL ][ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,
                start_transaction_id_column_name bigint GENERATED ALWAYS AS TRANSACTION_ID START
                   [ HIDDEN ] NOT NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  end_transaction_id_column_name bigint GENERATED ALWAYS AS TRANSACTION_ID END
                   [ HIDDEN ] NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  start_sequence_number_column_name bigint GENERATED ALWAYS AS SEQUENCE_NUMBER START
                   [ HIDDEN ] NOT NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  end_sequence_number_column_name bigint GENERATED ALWAYS AS SEQUENCE_NUMBER END
                   [ HIDDEN ] NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES]
        ]
       PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )
    | DROP
     [ {
         [ CONSTRAINT ][ IF EXISTS ]
         {
              constraint_name
              [ WITH
               ( <drop_clustered_constraint_option> [ ,...n ] )
              ]
          } [ ,...n ]
          | COLUMN [ IF EXISTS ]
          {
              column_name
          } [ ,...n ]
          | PERIOD FOR SYSTEM_TIME
     } [ ,...n ]
    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT
        { ALL | constraint_name [ ,...n ] }
  
    | { ENABLE | DISABLE } TRIGGER
        { ALL | trigger_name [ ,...n ] }
  
    | { ENABLE | DISABLE } CHANGE_TRACKING
        [ WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) ]
  
    | SWITCH [ PARTITION source_partition_number_expression ]
        TO target_table
        [ PARTITION target_partition_number_expression ]
        [ WITH ( <low_priority_lock_wait> ) ]

    | SET
        (
            [ FILESTREAM_ON =
                { partition_scheme_name | filegroup | "default" | "NULL" } ]
            | SYSTEM_VERSIONING =
                  {
                    OFF
                  | ON
                      [ ( HISTORY_TABLE = schema_name . history_table_name
                          [, DATA_CONSISTENCY_CHECK = { ON | OFF } ]
                          [, HISTORY_RETENTION_PERIOD =
                          {
                              INFINITE | number {DAY | DAYS | WEEK | WEEKS
                  | MONTH | MONTHS | YEAR | YEARS }
                          }
                          ]
                        )
                      ]
                  }
            | DATA_DELETION =
                {
                      OFF
                    | ON
                        [(  [ FILTER_COLUMN = column_name ]
                            [, RETENTION_PERIOD = { INFINITE | number {DAY | DAYS | WEEK | WEEKS
                                    | MONTH | MONTHS | YEAR | YEARS }}]
                        )]
                    }
    | REBUILD
      [ [PARTITION = ALL]
        [ WITH ( <rebuild_option> [ ,...n ] ) ]
      | [ PARTITION = partition_number
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]
        ]
      ]
  
    | <table_option>
    | <filetable_option>
    | <stretch_configuration>
}
[ ; ]
  
-- ALTER TABLE options
  
<column_set_definition> ::=
    column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

<drop_clustered_constraint_option> ::=
    {
        MAXDOP = max_degree_of_parallelism
      | ONLINE = { ON | OFF }
      | MOVE TO
         { partition_scheme_name ( column_name ) | filegroup | "default" }
    }
<table_option> ::=
    {
        SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
    }
  
<filetable_option> ::=
    {
       [ { ENABLE | DISABLE } FILETABLE_NAMESPACE ]
       [ SET ( FILETABLE_DIRECTORY = directory_name ) ]
    }
  
<stretch_configuration> ::=
    {
      SET (
        REMOTE_DATA_ARCHIVE
        {
            = ON (<table_stretch_options>)
          | = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED )
          | ( <table_stretch_options> [, ...n] )
        }
            )
    }
  
<table_stretch_options> ::=
    {
     [ FILTER_PREDICATE = { null | table_predicate_function } , ]
       MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }
    }
  
<single_partition_rebuild__option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE} }
    | ONLINE = { ON [( <low_priority_lock_wait> ) ] | OFF }
}
  
<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ],
        ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

Note

詳細については、次を参照してください。

メモリ最適化テーブルの構文

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
    ALTER COLUMN column_name
    {
        [ type_schema_name. ] type_name
            [ (
                {
                   precision [ , scale ]
                }
            ) ]
        [ COLLATE collation_name ]
        [ NULL | NOT NULL ]
    }

    | ALTER INDEX index_name
    {
        [ type_schema_name. ] type_name
        REBUILD
        [ [ NONCLUSTERED ] WITH ( BUCKET_COUNT = bucket_count )
        ]
    }

    | ADD
    {
        <column_definition>
      | <computed_column_definition>
      | <table_constraint>
      | <table_index>
      | <column_index>
    } [ ,...n ]
  
    | DROP
     [ {
         CONSTRAINT [ IF EXISTS ]
         {
              constraint_name
          } [ ,...n ]
        | INDEX [ IF EXISTS ]
      {
         index_name
       } [ ,...n ]
          | COLUMN [ IF EXISTS ]
          {
              column_name
          } [ ,...n ]
          | PERIOD FOR SYSTEM_TIME
     } [ ,...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 ]
        [ WITH ( <low_priority_lock_wait> ) ]
    
}
[ ; ]

-- ALTER TABLE options

< table_constraint > ::=
 [ CONSTRAINT constraint_name ]
{
   {PRIMARY KEY | UNIQUE }
     {
       NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])
       | NONCLUSTERED HASH (column [ ,... n ] ) WITH ( BUCKET_COUNT = bucket_count )
     }
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
    | CHECK ( logical_expression )
}

<column_index> ::=
  INDEX index_name
{ [ NONCLUSTERED ] | [ NONCLUSTERED ] HASH WITH (BUCKET_COUNT = bucket_count)}

<table_index> ::=
  INDEX index_name
{[ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count)
  | [ NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] )
      [ ON filegroup_name | default ]
  | CLUSTERED COLUMNSTORE [WITH ( COMPRESSION_DELAY = {0 | delay [Minutes]})]
      [ ON filegroup_name | default ]
}

Azure Synapse Analytics と Parallel Data Warehouse の構文

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse

ALTER TABLE { database_name.schema_name.source_table_name | schema_name.source_table_name | source_table_name }
{
    ALTER COLUMN column_name
        {
            type_name [ ( precision [ , scale ] ) ]
            [ COLLATE Windows_collation_name ]
            [ NULL | NOT NULL ]
        }
    | ADD { <column_definition> | <column_constraint> FOR column_name} [ ,...n ]
    | DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]
    | REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_option> ] ]
      }
    | { SPLIT | MERGE } RANGE (boundary_value)
    | SWITCH [ PARTITION source_partition_number
        TO target_table_name [ PARTITION target_partition_number ] [ WITH ( TRUNCATE_TARGET = ON | OFF )
}
[;]

<column_definition>::=
{
    column_name
    type_name [ ( precision [ , scale ] ) ]
    [ <column_constraint> ]
    [ COLLATE Windows_collation_name ]
    [ NULL | NOT NULL ]
}

<column_constraint>::=
    [ CONSTRAINT constraint_name ]
    {
        DEFAULT constant_expression
        | PRIMARY KEY NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED -- Applies to Azure Synapse Analytics only
        | UNIQUE (column_name [ ,... n ]) NOT ENFORCED -- Applies to Azure Synapse Analytics only
    }
<rebuild_option > ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
}

<single_partition_rebuild_option > ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
}

Note

Azure Synapse Analytics のサーバーレス SQL プールでは、外部および一時テーブルのみがサポートされます。

Microsoft Fabric のウェアハウスの構文

-- Syntax for Warehouse in Microsoft Fabric

ALTER TABLE { database_name.schema_name.source_table_name | schema_name.source_table_name | source_table_name }
{
    ADD { <column_constraint> FOR column_name} [ ,...n ]
    | DROP { [CONSTRAINT] constraint_name } [ ,...n ]
    
}
[;]

<column_constraint>::=
    [ CONSTRAINT constraint_name ]
    {
       PRIMARY KEY NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED
        | UNIQUE NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] NOT ENFORCED
    }

Note

SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

引数

database_name

テーブルが作成されたデータベースの名前です。

schema_name

テーブルが属しているスキーマの名前です。

table_name

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

ALTER COLUMN

名前指定された列に変更を加えるように指定します。

変更された列を次にすることはできません。

timestamp データ型の列。
テーブルの ROWGUIDCOL。
計算列、または計算列の中で使用される列。
CREATE STATISTICS ステートメントで作成した統計内で使用する。 ALTER COLUMN を成功させるには、ユーザーは DROP STATISTICS を実行して統計を削除する必要があります。このクエリを実行して、ユーザーがテーブルに対して作成したすべての統計と統計の列を取得します。
```
SELECT s.name AS statistics_name
       ,c.name AS column_name
       ,sc.stats_column_id
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
     ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c
     ON sc.object_id = c.object_id AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID('<table_name>');
```
Note

クエリオプティマイザーによって自動的に生成された統計は、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 を varbinary(max) へ

一部のデータ型を変更すると、データが変更される可能性があります。たとえば、nchar または nvarchar の列を、char または varchar に変更すると、拡張文字の変換が発生する場合があります。詳しくは、「CAST および CONVERT」をご覧ください。列の有効桁数または小数点以下桁数を減らすと、データが切り捨てられる可能性があります。

Note

パーティションテーブルの列のデータ型は変更できません。

列が varchar、nvarchar、または varbinary データ型で、かつ新しいサイズが前のサイズと同じかそれよりも小さい場合を除いて、インデックスに含まれている列のデータ型は変更できません。

主キー制約に含まれている列を、NOT NULL から NULL に変更することはできません。

(セキュアエンクレーブなしの) Always Encrypted を使用している場合、変更する列が 'ENCRYPTED WITH' を使用して暗号化されている場合は、データ型を互換性のあるデータ型に変更すること (INT から BIGINT など) はできますが、暗号化設定を変更することはできません。

セキュアエンクレーブを使用する Always Encrypted を使用しているとき、列を保護する列暗号化キー (および、キーを変更している場合は新しい列暗号化キー) によってエンクレーブ計算がサポートされている (エンクレーブ対応の列マスターキーで暗号化される) 場合は、すべての暗号化設定を変更できます。詳細については、「Always Encrypted with secure enclaves」(セキュリティで保護されたエンクレーブが設定された Always Encrypted) を参照してください。

列を変更すると、データベースエンジンはシステムテーブルに行を追加し、前の列の変更を削除された列としてマークすることにより、各変更を追跡します。列を何度も変更する場合はまれに、データベースエンジンがレコードサイズの制限に達する可能性があります。この場合、エラー 511 または 1708 が発生します。これらのエラーを回避するには、テーブルのクラスター化インデックスを定期的に再構築するか、列の変更の数を減らします。

column_name

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

Note

テーブルの既存の列がすべて変更された後、新しい列が追加されます。

[ type_schema_name. ] type_name

変更する列の新しいデータ型、または追加する列のデータ型です。パーティションテーブルの既存の列に type_name を指定することはできません。 type_name には次の型のいずれかを指定できます。

SQL Server システムデータ型。
SQL Server システムデータ型に基づく別名データ型。 CREATE TYPE ステートメントを使って別名データ型を作成した後、それらをテーブル定義で使用できます。
.NET Framework ユーザー定義型とそれが属するスキーマ。 CREATE TYPE ステートメントを使ってユーザー定義型を作成した後、それらをテーブル定義で使用できます。

変更する列に対して type_name を指定する場合の条件を次に示します。

以前のデータ型は、新しいデータ型に自動的に変換される必要があります。
type_name を timestamp にすることはできません。
ANSI_NULL の既定値は ALTER COLUMN に対して常にオンです。指定しなかった場合、列では NULL 値が許容されます。
ANSI_PADDING の埋め込みは、ALTER COLUMN に対して常に ON になっています。
変更する列が ID 列の場合、new_data_type は、ID プロパティをサポートするデータ型であることが必要です。
SET ARITHABORT の現在の設定は無視されます。 ALTER TABLE は、ARITHABORT が ON に設定されている場合と同様に動作します。

Note

COLLATE 句を指定しないで列のデータ型を変更すると、照合順序がデータベースの既定の照合順序に変更されます。

有効桁数 (precision)

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

scale

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

最大

データ型 varchar、nvarchar、varbinary だけに適用され、2^31-1 バイトの文字データ、バイナリデータ、および Unicode データが格納されます。

xml_schema_collection

適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降) と Azure SQL データベース。

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

COLLATE <collation_name>

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

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

次の条件の 1 つ以上に該当する場合、ALTER COLUMN で照合順序を変更することはできません。

CHECK 制約、FOREIGN KEY 制約、またはその列を参照する計算列が変更された場合。
列にインデックス、統計、またはフルテキストインデックスが作成された場合。変更する列に対して自動的に作成された統計は、列の照合順序を変更すると削除されます。
スキーマバインドビューまたは関数で列が参照されている場合。

詳しくは、「COLLATE」をご覧ください。

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」を参照してください。

Note

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

[ {ADD | DROP} ROWGUIDCOL ]

適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降) と Azure SQL データベース。

指定した列に対する ROWGUIDCOL プロパティの追加または削除を指定します。 ROWGUIDCOL は、列が行の GUID 列であることを示します。 1 つのテーブルにつき、1 つの uniqueidentifier 列だけを ROWGUIDCOL 列として設定できます。また、ROWGUIDCOL プロパティは uniqueidentifier 列にだけ割り当てることができます。 ROWGUIDCOL をユーザー定義データ型の列に割り当てることはできません。

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

[ {ADD | DROP} PERSISTED ]

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

計算列やインデックス付きビューのインデックスを作成または変更するときには、SET QUOTED_IDENTIFIER を ON に設定する必要があります。詳細については、「SET QUOTED_IDENTIFIER (Transact-SQL)」をご覧ください。

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

DROP NOT FOR REPLICATION

適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降) と Azure SQL データベース。

レプリケーションエージェントで挿入操作が実行されるときに、ID 列の値をインクリメントすることを指定します。この句は column_name が ID 列の場合にのみ指定できます。

SPARSE

列がスパース列であることを示します。スパース列のストレージは NULL 値用に最適化されます。スパース列を NOT NULL として設定することはできません。列をスパースから非スパースに変換したり、非スパースからスパースに変換したりすると、このオプションではコマンドの実行中にテーブルがロックされます。 REBUILD 句を使用して、領域の節約を再要求することが必要になる場合があります。スパース列のその他の制限事項と詳細については、「スパース列の使用」を参照してください。

ADD MASKED WITH ( FUNCTION = ' mask_function ')

適用対象: SQL Server (SQL Server 2016 (13.x) 以降) と Azure SQL データベース。

動的なデータマスクを指定します。 mask_function マスキング関数は、適切なパラメーターの名前を指定します。 3 つの関数を使用できます。

default()
email()
partial()
random()

ALTER ANY MASK 権限が必要です。

マスクを削除するには、DROP MASKED を使用します。関数のパラメーターについては、「動的なデータマスキング」を参照してください。

マスクを追加および削除するには、ALTER ANY MASK 権限が必要です。

WITH ( ONLINE = ON | OFF) (<列の変更の適用対象として>)

適用対象: SQL Server (SQL Server 2016 (13.x) 以降) と Azure SQL データベース。

テーブルを使用可能な状態に維持したまま、列の変更操作の多くを実行できるようにします。既定値は OFF です。データ型、列の長さまたは有効桁数、NULL 値の許容、スパースかどうか、および照合順序に関連する列の変更のために、列の変更をオンラインで実行できます。

オンラインでの列の変更では、ユーザー作成の統計と自動統計で ALTER COLUMN 操作の実行中に変更された列を参照することができます。これにより、通常どおりクエリを実行できます。操作の最後に、列を参照する自動統計は削除され、ユーザー作成の統計は無効になります。ユーザーは、操作が完了したら、ユーザー生成の統計を手動で更新する必要があります。統計やインデックス用のフィルター式の中でその列が使われている場合は、列の変更操作は実行できません。

オンラインでの列の変更操作の実行中、列への依存関係のあるすべての操作 (インデックス、ビューなど) はブロックされるか、該当するエラーにより失敗します。この動作により、操作の実行中に依存関係が導入されたためにオンラインでの列の変更が失敗しないようになります。
変更された列が非クラスター化インデックスによって参照される場合、NOT NULL から NULL への列の変更は、オンライン操作としてサポートされません。
列がチェック制約によって参照されていて、かつ ALTER 操作が列の精度 (数値または datetime) を制限している場合、オンライン ALTER はサポートされません。
オンラインでの列の変更と共に WAIT_AT_LOW_PRIORITY オプションを使うことはできません。
オンラインでの列の変更では、ALTER COLUMN ... ADD/DROP PERSISTED はサポートされていません。
ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION は、オンラインでの列の変更の影響を受けません。
変更の追跡が有効になっているか、テーブルがマージレプリケーションのパブリッシャーである場合、オンラインでの列の変更ではテーブルの変更がサポートされません。
オンラインでの列の変更は、CLR データ型との変更をサポートしていません。
オンラインでの列の変更では、現在のスキーマコレクションとは異なるスキーマコレクションを持つ XML データ型への変更はサポートされていません。
オンラインでの列の変更で、列を変更できるタイミングに関する制限が軽減されるわけではありません。インデックスまたは統計などでの参照は、変更が失敗する原因となる可能性があります。
オンラインでの列の変更では、複数の列を同時に変更することはできません。
システムでバージョン管理されたテンポラルテーブルの場合、オンラインでの列の変更による影響はありません。 ONLINE オプションに指定された値に関係なく、ALTER 列はオンラインとしては実行されません。

オンラインでの列の変更には、オンラインインデックスの再構築と同様の、次のような要件、制限、および機能があります。

オンラインでのインデックスの再構築は、従来の LOB または filestream 列がテーブルに含まれている場合、またはテーブルに列ストアインデックスがある場合はサポートされません。同じ制限が、オンラインでの列の変更に適用されます。
変更される既存の列には、元の列と新しく作成される非表示の列のために、2 倍の領域の割り当てが必要です。
オンラインでの列の変更の操作中のロック方法は、オンラインインデックスの構築に使用するのと同じロックパターンに従っています。

WITH CHECK | WITH NOCHECK

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

既存のデータに対して新しい CHECK または FOREIGN KEY 制約を検証しない場合は、WITH NOCHECK を使用します。ごくわずかな例外を除き、これの実行は推奨されません。新しい制約は、それ以降にデータが更新されるたびに評価されます。制約の追加時、制約違反があっても WITH NOCHECK が指定されていたために検出されなかった場合、その後の更新で制約に従わないデータが使用されると行の更新が失敗する可能性があります。クエリオプティマイザーでは、WITH NOCHECK が定義されている制約は考慮されません。このような制約は、ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL を使用して再び有効にするまで無視されます。詳細については、「INSERT ステートメントまたは UPDATE ステートメントに対して外部キー制約を無効にする方法」を参照してください。

ALTER INDEX index_name

index_name のバケット数が変更されることを指定します。

構文 ALTER TABLE ...ADD/DROP/ALTER INDEX は、メモリ最適化テーブルでのみサポートされます。

重要

ALTER TABLE ステートメントを使用しない場合、メモリ最適化テーブルのインデックスに対してステートメント CREATE INDEX、DROP INDEX、ALTER INDEX、PAD_INDEX を使用することはできません。

ADD

1 つ以上の列定義、計算列定義、またはテーブル制約を追加します。または、システムがシステムのバージョン管理用に使う列が追加されます。メモリ最適化テーブルでは、インデックスを追加することができます。

Note

テーブルの既存の列がすべて変更された後、新しい列が追加されます。

重要

ALTER TABLE ステートメントを使用しない場合、メモリ最適化テーブル上のインデックスに対してステートメント CREATE INDEX、DROP INDEX、ALTER INDEX、PAD_INDEX を使用することはできません。

PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )

適用対象: SQL Server (SQL Server 2017 (14.x) 以降) と Azure SQL データベース。

レコードが有効になる時間の長さを記録するためにシステムによって使用される列の名前を指定します。既存の列を指定したり、ADD PERIOD FOR SYSTEM_TIME 引数の一部として新しい列を作成したりできます。データ型 datetime2 を使って列を設定し、それらを NOT NULL として定義します。期間列を NULL として定義した場合、エラーが発生します。 system_start_time 列と system_end_time 列の column_constraint を定義し、列の既定値を指定することができます。 system_end_time 列の既定値の使用方法については、後述する「システムのバージョン管理」の例 A を参照してください。

SET SYSTEM_VERSIONING 引数と共にこの引数を使用して、既存のテーブルをテンポラルテーブルにします。詳細については、「テンポラルテーブル」と「Azure SQL Database のテンポラルテーブルの概要」を参照してください。

SQL Server 2017 (14.x) の時点では、ユーザーは一方または両方の期間列を HIDDEN フラグを使ってマークし、これらの列を暗黙的に非表示にすることで、SELECT * FROM からその列の値が返されないようにすることが可能です。既定では、期間の列は非表示ではありません。非表示の列を使用するためには、テンポラルテーブルを直接参照するすべてのクエリで明示的に含める必要があります。

DROP

1 つ以上の列の定義、計算列の定義、またはテーブルの制約の削除、または、システムがシステムのバージョン管理用に使う列の仕様の削除を指定します。

注意

台帳テーブルで削除された列は、論理的に削除されただけです。削除された列はレジャーテーブルに残りますが、sys.tables にある dropped_ledger_table 列を 1 に設定すると、削除済み列としてマークされます。 sys.tables にある dropped_ledger_view 列を 1 に設定すると、削除済みレジャーテーブルのレジャービューも削除済みとしてマークされます。削除済みレジャーテーブル、その履歴テーブル、およびそのレジャービューは、プレフィックス (MSSQL_DroppedLedgerTable、MSSQL_DroppedLedgerHistory、MSSQL_DroppedLedgerView) を追加し、元の名前に GUID をアペンドすることで名称変更されます。

CONSTRAINT constraint_name

テーブルから constraint_name が削除されることを指定します。複数の制約をリストで指定できます。

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

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

INDEX index_name

index_name がテーブルから削除されることを指定します。

構文 ALTER TABLE ...ADD/DROP/ALTER INDEX は、メモリ最適化テーブルでのみサポートされます。

重要

COLUMN column_name

テーブルから constraint_name または column_name が削除されることを指定します。複数の列をリストで指定できます。

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

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

Note

列を削除しても、列のディスク領域は回収されません。テーブルの行サイズが制限に近いか制限を超えている場合は、必要に応じて、削除した列のディスク領域を再要求します。領域を再確保するには、テーブルにクラスター化インデックスを作成するか、ALTER INDEX を使用して既存のクラスター化インデックスを再構築します。 LOB データ型の削除による影響の詳細については、この CSS ブログエントリを参照してください。

FOR SYSTEM_TIME の期間

適用対象: SQL Server (SQL Server 2016 (13.x) 以降) と Azure SQL データベース。

システムのバージョン管理のために、システムが使用する列の仕様を削除します。

WITH <drop_clustered_constraint_option>

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

MAXDOP = max_degree_of_parallelism

適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降) と Azure SQL データベース。

操作中は、max degree of parallelism 構成オプションをオーバーライドします。詳細については、「 max degree of parallelism サーバー構成オプションの構成」を参照してください。

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

max_degree_of_parallelism には次のいずれかの値を指定できます。

1

並列プラン生成を抑制します。
>1

並列インデックス操作で使用される最大プロセッサ数を、指定した数に制限します。
0 (既定): 実際のプロセッサの数を使用または現在のシステムワークロードに基づいて少なくします。

詳細については、「並列インデックス操作の構成」を参照してください。

Note

並列インデックス操作は、SQL Server のすべてのエディションで使用できるわけではありません。詳しくは、「SQL Server 2022 の各エディションとサポートされている機能」を参照してください。

ONLINE = { ON | OFF } drop_clustered_constraint_option > に適用する < の場合

インデックス操作時に、基になるテーブルや関連するインデックスをクエリやデータ変更で使用できるかどうかを指定します。既定値は OFF です。 REBUILD は ONLINE 操作として実行できます。

ON
長期のテーブルロックは、インデックス操作の間は保持されません。インデックス操作の主なフェーズの間は、基になるテーブル上に、インテント共有 (IS) ロックのみが保持されます。この動作により、基になるテーブルとインデックスに対するクエリや更新を続行することが可能になります。操作の開始時、短時間ソースオブジェクトで共有 (S) ロックが保持されます。操作の最後に、短時間、非クラスター化インデックスを作成する場合はソース上で S (共有) ロックが取得されます。または、クラスター化インデックスをオンラインで作成または削除する場合、およびクラスター化または非クラスター化インデックスを再構築する場合、SCH-M (スキーマ修正) ロックが取得されます。インデックスがローカルの一時テーブルに作成される場合、ONLINE を ON に設定することはできません。シングルスレッドのヒープの再構築操作だけが許可されます。

SWITCH またはオンラインインデックス再構築の DDL を実行するには、特定のテーブルで実行されているすべてのアクティブなブロックトランザクションが完了している必要があります。実行時には、新しいトランザクションの開始が SWITCH または再構築操作によって阻止され、ワークロードのスループットに大きな影響が出たり、基になるテーブルへのアクセスが一時的に遅くなったりする場合があります。

OFF
テーブルロックは、インデックス操作の期間に適用されます。クラスター化インデックスを作成、再構築、または削除するオフラインインデックス操作や、非クラスター化インデックスを再構築または削除するオフラインインデックス操作では、テーブル上のスキーマ修正 (Sch-M) ロックが取得されます。このロックにより、操作中すべてのユーザーは基になるテーブルにアクセスできません。非クラスター化インデックスを作成するオフラインインデックス操作では、テーブルの共有 (S) ロックが取得されます。このロックでは、基になるテーブルへの更新が阻止されますが、SELECT ステートメントなどの読み取り操作は許可されます。マルチスレッドのヒープの再構築操作が許可されます。

詳細については、「オンラインインデックス操作の動作原理」を参照してください。

Note

オンラインでのインデックス操作は、 SQL Serverのすべてのエディションで使用できるわけではありません。詳しくは、「SQL Server 2022 の各エディションとサポートされている機能」を参照してください。

MOVE TO { partition_scheme_name(column_name [ ,...n ] ) | filegroup | "default" }

適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降) と Azure SQL データベース。

クラスター化インデックスのリーフレベルに現在あるデータ行を移動する場所を指定します。テーブルは、新しい場所に移動されます。このオプションは、クラスター化インデックスを作成する制約のみに適用されます。

Note

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

{ 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
無効または有効にするトリガーの名前を指定します。

{ ENABLE | DISABLE } CHANGE_TRACKING

適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降) と Azure SQL データベース。

テーブルに対して変更の追跡を有効にするかどうかを指定します。既定では、変更の追跡が無効になっています。

このオプションは、データベースに対して変更の追跡が有効になっている場合にのみ使用できます。詳しくは、「ALTER DATABASE SET オプション」をご覧ください。

変更の追跡を有効にするには、テーブルに主キーが必要です。

WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )

適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降) と Azure SQL データベース。

データベースエンジンで追跡するかどうか、どの追跡対象列の変更が更新されたかを指定します。既定値は OFF です。

SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name. ] target_table [ PARTITION target_partition_number_expression ]

適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降) と Azure SQL データベース。

次のいずれかの方法で、データのブロックを切り替えます。

テーブルのすべてのデータを、既に存在するパーティションテーブルにパーティションとして再度割り当てます。
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 の制限については、「パーティションテーブルとパーティションインデックスのレプリケート」を参照してください。

SQL Server 2016 (13.x) およびバージョン V12 より前の SQL Database では、非クラスター化列ストアインデックスは読み取り専用形式で構築されていました。あらゆる PARTITION 操作を実行する前に、非クラスター化列ストアインデックスを現在の形式に再構築する必要があります (これは更新可能です)。

制限事項

非クラスター化インデックスを含め、両方のテーブルが同じようにパーティション分割されていて、ターゲットテーブルに非クラスター化インデックスがない場合は、4907 エラーが発生する可能性があります。

出力例:

Msg 4907, Level 16, State 1, Line 38
'ALTER TABLE SWITCH' statement failed. The table 'MyDB.dbo.PrtTable1' has 4 partitions while index 'MS1' has 6 partitions.

SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default" | "NULL" })

適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降)。 Azure SQL データベースは FILESTREAM をサポートしていません。

FILESTREAM データの格納場所を指定します。

SET FILESTREAM_ON 句を指定した ALTER TABLE は、テーブルに FILESTREAM 列がない場合にのみ成功します。 FILESTREAM 列は、2 番目の ALTER TABLE ステートメントを使うことで追加できます。

partition_scheme_name を指定すると、CREATE TABLE のルールが適用されます。テーブルが行データ用に既にパーティション分割されていることを確認します。また、そのパーティション構成では、FILESTREAM パーティション構成と同じパーティション関数とパーティション列を使用する必要があります。

filestream_filegroup_name には、FILESTREAM ファイルグループの名前を指定します。ファイルグループには、CREATE DATABASE または ALTER DATABASE ステートメントを使って、ファイルグループ用に定義されたファイルが 1 つ含まれている必要があります。それ以外の場合はエラーが発生します。

"default" には、DEFAULT プロパティセットを含む FILESTREAM ファイルグループを指定します。 FILESTREAM ファイルグループがない場合は、エラーが発生します。

"NULL" を指定すると、テーブルの FILESTREAM ファイルグループへの参照がすべて削除されます。最初にすべての FILESTREAM 列を削除する必要があります。テーブルに関連付けられている FILESTREAM データをすべて削除するには、SET FILESTREAM_ON = "NULL" を使用します。

SET ( SYSTEM_VERSIONING = { OFF | ON [ ( HISTORY_TABLE = schema_name . history_table_name [ , DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ] } )

適用対象: SQL Server (SQL Server 2016 (13.x) 以降) と Azure SQL データベース。

テーブルに関するシステムのバージョン管理を無効または有効にします。テーブルのシステムバージョン管理を有効にするために、システムでは、システムバージョン管理のためのデータ型、null 値許容制約、および主キー制約の要件が満たされていることを確認します。このシステムによって、システムバージョン管理されたテーブル内の各レコードの履歴が個別の履歴テーブルに記録されます。 HISTORY_TABLE 引数が使用されていない場合、この履歴テーブルの名前は MSSQL_TemporalHistoryFor<primary_table_object_id> になります。履歴テーブルが存在しない場合、システムによって現在のテーブルのスキーマに一致する新しい履歴テーブルが生成され、2 つのテーブル間のリンクが作成され、システムが現在のテーブルにある各レコードの履歴を履歴テーブルに記録できるようになります。 HISTORY_TABLE 引数を使ってリンクを作成し、既存の履歴テーブルを使用する場合、システムにより現在のテーブルと、指定したテーブルの間のリンクが作成されます。既存の履歴テーブルへのリンクを作成する場合は、データの整合性チェックを行うよう選択できます。このデータの整合性チェックにより、既存のレコードが重複しないようになります。既定ではデータの整合性チェックを実行します。 PERIOD FOR SYSTEM_TIME 句で定義されているテーブルで SYSTEM_VERSIONING = ON 引数を使用して、既存のテーブルをテンポラルテーブルにします。詳細については、「 Temporal Tables」を参照してください。

HISTORY_RETENTION_PERIOD = { INFINITE | number {DAY | DAYS | WEEK | WEEKS | MONTH | MONTHS | YEAR | YEARS} }

適用対象: SQL Server 2017 (14.x) および Azure SQL データベース。

テンポラルテーブルに履歴データ用の有限または無限のリテンション期間を指定します。省略すると、無限のリテンション期間が使用されます。

DATA_DELETION

適用対象: Azure SQL Edge "のみ"

データベース内のテーブルの古いデータまたは期限切れのデータに対し、アイテム保持ポリシーを使用したクリーンアップを有効にします。詳細については、データ保持の有効化と無効化に関するページを参照してください。データ保持を有効にするには、次のパラメーターを指定します。

FILTER_COLUMN = { column_name }
列を指定します。これは、テーブル内の行が古いかどうかを判断するために使用します。フィルター列に使用できるデータ型は次のとおりです。

Date
DateTime
DateTime2
SmallDateTime
DateTimeOffset

RETENTION_PERIOD = { INFINITE | number {DAY | DAYS | WEEK | WEEKS | MONTH | MONTHS | YEAR | YEARS }}
テーブルの保持期間のポリシーを指定します。保持期間は、正の整数値と日付部分の単位を組み合わせて指定します。

SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降) と Azure SQL データベース。

許可されるテーブルロックのエスカレーション方法を指定します。

AUTO
このオプションを指定すると、SQL Server データベースエンジンで、テーブルスキーマに適したロックのエスカレーションの細分性を選択できます。

テーブルがパーティション分割されている場合は、ロックエスカレーションをヒープまたは B ツリー (HoBT) 粒度に設定できます。つまり、エスカレーションはパーティションレベルで許可されます。ロックが HoBT レベルにエスカレートされたら、ロックが後で TABLE 細分性にエスカレートされることはありません。
テーブルがパーティション分割されていない場合、TABLE 細分性に対してロックのエスカレーションが実行されます。

TABLE
テーブルがパーティション分割されているかパーティション分割されていないかに関係なく、ロックのエスカレーションはテーブルレベルの細分性で実行されます。 TABLE は既定値です。

DISABLE
ほとんどの場合でロックエスカレーションを禁止します。テーブルレベルのロックは完全には禁止されません。たとえば、SERIALIZABLE 分離レベルでクラスター化インデックスがないテーブルをスキャンしている場合、データベースエンジンではテーブルロックを実行して、データ整合性を保護する必要があります。

REBUILD

パーティションテーブル内のすべてのパーティションを含めたテーブル全体を再構築するには、REBUILD WITH 構文を使用します。テーブルにクラスター化インデックスが含まれている場合、REBUILD オプションを指定すると、クラスター化インデックスが再構築されます。 REBUILD は ONLINE 操作として実行できます。

パーティションテーブル内の 1 つのパーティションを再構築するには、REBUILD PARTITION 構文を使用します。

PARTITION = ALL

適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降) と Azure SQL データベース。

パーティションの圧縮設定の変更時に、すべてのパーティションを再構築します。

REBUILD WITH ( <rebuild_option> )

クラスター化インデックスを含むテーブルには、すべてのオプションが適用されます。テーブルにクラスター化インデックスが含まれていない場合、ヒープ構造に影響を与えるのは一部のオプションだけです。

REBUILD 操作で特定の圧縮設定を指定しないと、パーティション用の現在の圧縮設定が使用されます。現在の設定を取得するには、sys.partitions カタログビューで data_compression 列に対するクエリを実行します。

再構築オプションについて詳しくは、ALTER TABLE index_option に関するページを参照してください。

DATA_COMPRESSION

適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降) と Azure SQL データベース。

指定したテーブル、パーティション番号、またはパーティション範囲に、データ圧縮オプションを指定します。次のようなオプションがあります。

NONE: テーブルまたは指定したパーティションが圧縮されません。このオプションは列ストアテーブルには適用されません。

ROW: 行の圧縮を使用して、テーブルまたは指定したパーティションが圧縮されます。このオプションは列ストアテーブルには適用されません。

PAGE: ページの圧縮を使用して、テーブルまたは指定したパーティションが圧縮されます。このオプションは列ストアテーブルには適用されません。

COLUMNSTORE
適用対象: SQL Server (SQL Server 2014 (12.x) 以降) と Azure SQL データベース。

列ストアテーブルにのみ適用されます。 COLUMNSTORE は、COLUMNSTORE_ARCHIVE オプションで圧縮されたパーティションを解凍するように指定します。復元されるデータは、すべての列ストアテーブルに使用される列ストア圧縮を使用して引き続き圧縮されます。

COLUMNSTORE_ARCHIVE
適用対象: SQL Server (SQL Server 2014 (12.x) 以降) と Azure SQL データベース。

クラスター化列ストアインデックスを使用して格納されているテーブルである、列ストアテーブルのみに適用されます。 COLUMNSTORE_ARCHIVE は、指定したパーティションをより小さなサイズにさらに圧縮します。このオプションは、保存用や、ストレージの使用量を減らす必要があり、しかも保存と取得により時間をかける余裕があるその他の状況用に使用します。

複数のパーティションを同時に再構築するには、「index_option」をご覧ください。テーブルにクラスター化インデックスが含まれていない場合、データ圧縮を変更するとヒープと非クラスター化インデックスが再構築されます。圧縮の詳細については、「データ圧縮」を参照してください。

XML_COMPRESSION

適用対象: SQL Server 2022 (16.x) 以降のバージョン、Azure SQL データベース、および Azure SQL Managed Instance。

テーブル内のすべての xml データ型列に XML 圧縮オプションを指定します。次のようなオプションがあります。

ON
xml データ型を使用した列が圧縮されます。

OFF
xml データ型を使用した列は圧縮されません。

ONLINE = { ON | OFF } single_partition_rebuild_option > に適用する < の場合

基となるテーブルの 1 つのパーティションと関連するインデックスを、インデックス操作中のクエリとデータ変更用に利用できるかどうかを指定します。既定値は OFF です。 REBUILD は ONLINE 操作として実行できます。

ON
長期のテーブルロックは、インデックス操作の間は保持されません。インデックスの再構築を開始するときにテーブル上の S ロックが必要であり、オンラインインデックス再構築を終了するときにテーブルの Sch-M ロックが必要です。どちらのロックも短いメタデータロックですが、Sch-M ロックは、すべてのブロックしているトランザクションの完了を待機する必要があります。待機中、Sch-M ロックによって、同じテーブルにアクセスするためにこのロックの後に待機している他のすべてのトランザクションがブロックされます。

Note

オンラインインデックス再構築では、このセクションの後の方で説明されている low_priority_lock_wait オプションを設定できます。

OFF
テーブルロックは、インデックス操作の間適用されます。このため、操作中は、すべてのユーザーは基になるテーブルにアクセスできません。

column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降) と Azure SQL データベース。

列セットの名前です。列セットは、型指定されていない XML 表記であり、テーブルのすべてのスパース列を 1 つにまとめて構造化した出力です。スパース列を含むテーブルには列セットを追加できません。列セットの詳細については、「列セットの使用」を参照してください。

{ ENABLE | DISABLE } FILETABLE_NAMESPACE

適用対象: SQL Server (SQL Server 2012 (11.x) 以降)。

FileTable に対するシステム定義の制約を有効または無効にします。 FileTable のみで使用できます。

SET ( FILETABLE_DIRECTORY = directory_name )

適用対象: SQL Server (SQL Server 2012 (11.x) 以降)。 Azure SQL データベースは FILETABLE をサポートしていません。

Windows と互換性のある FileTable ディレクトリ名を指定します。この名前は、データベース内のすべての FileTable ディレクトリ名の中で一意である必要があります。一意性の比較では、SQL 照合順序の設定とは関係なく、大文字と小文字は区別されません。 FileTable のみで使用できます。

REMOTE_DATA_ARCHIVE

適用対象: SQL Server (SQL Server 2017 (14.x) 以降)。

テーブルに対して Stretch Database を有効または無効にします。詳細については、「Stretch Database」を参照してください。

重要

拡張データベースは、SQL Server 2022 (16.x) および Azure SQL Database では非推奨になります。この機能は、データベースエンジンの将来のバージョンで削除される予定です。新規の開発作業ではこの機能を使用しないようにし、現在この機能を使用しているアプリケーションは修正することを検討してください。

テーブルに対して Stretch Database を有効にする

ON を指定してテーブルに対して Stretch を有効にする場合は、MIGRATION_STATE = OUTBOUND を指定してデータの移行を即時に開始するか、MIGRATION_STATE = PAUSED を指定してデータの移行を延期するかを指定する必要があります。既定値は MIGRATION_STATE = OUTBOUND です。テーブルに対して Stretch を有効にする方法については、「Enable Stretch Database for a table」を参照してください。

前提条件。テーブルに対して Stretch を有効にする前に、サーバーおよびデータベースで Stretch を有効にする必要があります。詳細については、「 Enable Stretch Database for a database」を参照してください。

権限: データベースまたはテーブルの Stretch を有効にするには、db_owner アクセス許可が必要です。テーブルの Stretch を有効にする場合、テーブルに対する ALTER 権限も必要です。

テーブルに対して Stretch Database を無効にする

テーブルの Stretch を無効にすると、既に Azure に移行されているリモートデータには 2 つの選択肢があります。詳細については、「Stretch Database を無効にして、リモートデータを戻す」を参照してください。

テーブルの Stretch を無効にして Azure から SQL Server にテーブルのリモートデータをコピーするには、次のコマンドを実行します。このコマンドは取り消すことができません。
```
ALTER TABLE <table_name>
   SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) ) ;
```

この操作にはデータ転送コストが発生し、キャンセルできません。詳細については、データ転送の価格の詳細に関するページをご覧ください。

すべてのリモートデータが Azure から SQL Server にコピーして戻されると、テーブルに対する Stretch は無効になります。

テーブルの Stretch を無効にしてリモートデータを破棄するには、次のコマンドを実行します。
```
ALTER TABLE <table_name>
   SET ( REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED ) ) ;
```

テーブルの Stretch Database を無効にすると、データ移行が停止し、クエリ結果にリモートテーブルからの結果が含まれなくなります。

Stretch を無効にしても、リモートテーブルは削除されません。リモートテーブルを削除する場合は、Azure portal を使用して削除します。

[ FILTER_PREDICATE = { null | predicate } ]

適用対象: SQL Server (SQL Server 2017 (14.x) 以降)。

必要に応じて、履歴データと現在のデータの両方を含むテーブルから移行する行を選択するフィルター述語を指定します。この述語で決定論的インラインテーブル値関数を呼び出す必要があります。詳しくは、「テーブルに対して Stretch Database を有効にする」および「フィルター関数を使用して移行する行を選択する」をご覧ください。

重要

指定したフィルター述語のパフォーマンスが低いと、データ移行のパフォーマンスも低くなります。 Stretch Database では、CROSS APPLY 演算子を使用してテーブルにフィルター述語を適用します。

フィルター述語を指定しない場合、テーブル全体が移行されます。

フィルター述語を指定する場合は、MIGRATION_STATE も指定する必要があります。

MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }

適用対象: SQL Server (SQL Server 2017 (14.x) 以降)。

SQL Server から Azure にデータを移行するには OUTBOUND を指定します。
Azure から SQL Server にテーブルのリモートデータをコピーして戻し、テーブルに対する Stretch を無効にするには、INBOUND を指定します。詳細については、「Stretch Database を無効にして、リモートデータを戻す」を参照してください。

この操作にはデータ転送コストが発生し、キャンセルできません。
データの移行を一時停止または延期するには PAUSED を指定します。詳しくは、「データ移行の一時停止と再開 - Stretch Database」をご覧ください。

WAIT_AT_LOW_PRIORITY

適用対象: SQL Server (SQL Server 2014 (12.x) 以降) と Azure SQL データベース。

オンラインインデックス再構築では、このテーブルに対する操作がブロックされるまで待機する必要があります。 WAIT_AT_LOW_PRIORITY は、オンラインインデックス再構築操作が低優先度のロックを待機して、オンラインインデックス構築操作が待機している間、他の操作を実行可能にすることを示します。 WAIT AT LOW PRIORITY オプションを省略することは、WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE) と同じです。

MAX_DURATION = time[MINUTES]
適用対象: SQL Server (SQL Server 2014 (12.x) 以降) と Azure SQL データベース。

SWITCH またはオンラインインデックス再構築のロックが、DDL コマンドの実行時に低優先度で待機する時間 (分単位で指定した整数値) です。操作が MAX_DURATION の期間ブロックされると、ABORT_AFTER_WAIT アクションのいずれかが実行されます。 MAX_DURATION の期間は常に分単位で、MINUTES という単語は省略できます。

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

適用対象: SQL Server (SQL Server 2014 (12.x) 以降) と Azure SQL データベース。

NONE
通常の (標準) 優先度のロックを待機し続けます。

SELF
いずれのアクションも行わずに、現在実行中の SWITCH またはオンラインインデックス再構築の DDL 操作を終了します。

BLOCKERS
現在 SWITCH またはオンラインインデックス再構築の DDL 操作をブロックしているすべてのユーザートランザクションを中止して、操作を続行できるようにします。

ALTER ANY CONNECTION 権限が必要です。

IF EXISTS

適用対象: SQL Server (SQL Server 2016 (13.x) 以降) と Azure SQL データベース。

既に存在する場合にのみ、列または制約を条件付きで削除します。

RESUMABLE = { ON | OFF}

適用対象: SQL Server 2022 (16.x) 以降。

ALTER TABLE ADD CONSTRAINT 操作が再開可能かどうかを指定します。 ON の場合はテーブル制約追加操作を再開できます。 OFF の場合、テーブル制約追加操作を再開できません。既定値は OFF です。 RESUMABLE オプションは、ALTER TABLE table_constraint の ALTER TABLE index_option の一部として使用できます。

MAX_DURATION を RESUMABLE = ON (ONLINE = ON にする必要あり) で使用した場合、再開可能なオンラインでの制約の追加操作が、一時停止されるまでに実行される時間 (分単位で指定される整数値) を示します。指定されていない場合、操作は完了するまで続行されます。

再開可能な ALTER TABLE ADD CONSTRAINT 操作の有効化と使用の詳細については、再開可能なテーブル制約の追加に関する記事を参照してください。

解説

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

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

列のファイルサイズを変更する

列のデータ型の新しいサイズを指定すると、列の長さ、有効桁数、または小数点以下桁数を変更できます。 ALTER COLUMN 句を使います。列内にデータが存在する場合は、新しいサイズをデータの最大サイズより小さくすることはできません。また、インデックス内で列を定義することはできません。ただし、その列のデータ型が varchar、nvarchar、または varbinary であり、インデックスが PRIMARY KEY 制約の結果として生じたものでない場合は除きます。「列定義を変更する」というタイトルの短いセクションにある例をご覧ください。

ロックと ALTER TABLE

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

Microsoft Fabric の Warehouse に 適用されます。

ALTER TABLE は、重複しないトランザクションの一部にすることはできません。

パーティションスイッチの XEvents

次の XEvent は、ALTER TABLE ... SWITCH PARTITION とオンラインインデックスの再構築に関連しています。

lock_request_priority_state
process_killed_by_abort_blockers
ddl_with_wait_at_low_priority

オンライン操作としての NOT NULL 列を追加する

SQL Server 2012 (11.x) Enterprise Edition 以降では、既定値を持つ NOT NULL 列の追加は、既定値がランタイム定数である場合、オンライン操作です。これは、操作中にテーブル内の既存の行が更新されないため、テーブル内の行数にもかかわらず、操作がほぼ瞬時に完了することを意味します。その代わりに、既定値はテーブルのメタデータだけに格納され、これらの列にアクセスするクエリで、必要に応じて、値が検索されます。この動作は自動的に行われます。 ADD COLUMN 構文以外に、オンライン操作を実装するための追加の構文は必要ありません。ランタイム定数は、その決定性に関係なく、テーブルの各行に対して実行時に同じ値を生成する式です。たとえば、定数式 "My temporary data" やシステム関数 GETUTCDATETIME() は、ランタイム定数です。一方、関数 NEWID() や NEWSEQUENTIALID() は、テーブルの各行で一意の値が生成されるため、ランタイム定数ではありません。ランタイム定数ではない既定値を持つ NOT NULL 列の追加は、常にオフラインで実行され、操作中は排他 (SCH-M) ロックが取得されます。

既存の行はメタデータに格納された値を参照しますが、新しい行が挿入され、列に別の値が指定されない場合、既定値はその行に格納されます。メタデータに格納された既定値は、行が更新されるか (UPDATE ステートメントで実際の列が指定されなくても)、テーブルまたはクラスター化インデックスが再構築された場合、既存の行に移動されます。

型 varchar(max) 、nvarchar(max) 、varbinary(max) 、xml、text、ntext、image、hierarchyid、geometry、geography、または CLR UDTS の列をオンライン操作で追加することはできません。行の最大サイズが 8,060 バイトの制限を超える場合は、列をオンラインで追加することはできません。この場合、列はオフライン操作として追加されます。

並列プランの実行

SQL Server 2012 (11.x) Enterprise エディション以降のバージョンでは、1 つの ALTER TABLE ADD (インデックスベース) CONSTRAINT ステートメントまたは DROP (クラスター化インデックス) CONSTRAINT ステートメントを実行するために使用されるプロセッサの数は、並列処理の最大次数構成オプションと現在のワークロードによって決まります。データベースエンジンでシステムがビジー状態であることが検出されると、ステートメントの実行前に自動的に操作の並列処理の次数が減らされます。 MAXDOP オプションを指定すると、ステートメントの実行に使用されるプロセッサ数を手動で構成できます。詳細については、「 max degree of parallelism サーバー構成オプションの構成」を参照してください。

パーティションテーブル

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

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

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

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

インデックスと ALTER TABLE

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

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

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

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

クラスター化インデックスを削除する場合、ONLINE **=** ON オプションを指定して、基になるデータや関連する非クラスター化インデックスに対するクエリおよび変更を DROP INDEX トランザクションがブロックしないようにします。

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

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

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

Note

の下にリストされているオプションは、テーブル上のクラスター化インデックスに適用され、ビュー上のクラスター化インデックスまたは非クラスター化インデックスには適用できません。

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

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

データ圧縮

システムテーブルで圧縮を有効にすることはできません。テーブルがヒープの場合、ONLINE モードでは、再構築操作がシングルスレッドになります。マルチスレッドのヒープの再構築操作では、OFFLINE モードを使用してください。データ圧縮の詳細については、「データの圧縮」を参照してください。

圧縮状態の変更による、テーブル、インデックス、またはパーティションへの影響を評価するには、 sp_estimate_data_compression_savings システムストアドプロシージャを使用します。

パーティションテーブルには次の制限が適用されます。

固定されていないインデックスがテーブルにある場合、その 1 つのパーティションの圧縮設定を変更できません。
ALTER TABLE <table> REBUILD PARTITION ... 構文は、指定されたパーティションを再構築します。
ALTER TABLE <table> REBUILD WITH ... 構文は、すべてのパーティションを再構築します。

NTEXT 列を削除する

非推奨の NTEXT データ型を使用して列を削除すると、削除されたデータのクリーンアップが、シリアル化された操作としてすべての行で実行されます。クリーンアップは多大な時間を必要とする場合があります。多数の行を含むテーブルの NTEXT 列を削除する場合は、最初に NTEXT 列を NULL 値に更新し、次に列を削除します。このオプションを並列操作で実行し、より高速にすることができます。

オンラインでのインデックス再構築

オンラインインデックス再構築の DDL ステートメントを実行するには、特定のテーブルで実行されているすべてのアクティブなブロックトランザクションが完了する必要があります。オンラインインデックス再構築を起動すると、このテーブルに対する実行の開始が準備できているすべての新しいトランザクションがブロックされます。オンラインインデックス再構築のロックの期間は短いですが、特定のテーブル上の開いているトランザクションがすべて完了するまで待機し、新しいトランザクションの開始をブロックすることで、スループットに大きな影響を与える場合があります。これは、ワークロードの低速化やタイムアウトの原因となり、基になるテーブルへのアクセスを大幅に制限する可能性があります。 WAIT_AT_LOW_PRIORITY オプションを使うと、オンラインインデックス再構築に必要な S ロックおよび Sch-M ロックを DBA が管理できるようにします。 NONE、SELF、BLOCKERS の 3 つのいずれのケースでも、待機時間 ((MAX_DURATION =n [minutes])) 中にブロックするアクティビティがない場合は、オンラインインデックス再構築が待機なしですぐに実行され、DDL ステートメントが完了します。

互換性サポート

ALTER TABLE ステートメントでは、2 部構成 (schema.object) のテーブル名だけを使用できます。 SQL Server では、次の形式を使用してテーブル名を指定すると、コンパイル時にエラー 117 で失敗します。

server.database.schema.table
.database.schema.table
..schema.table

以前のバージョンでは、server.database.schema.table という形式を指定すると、エラー 4902 が返されました。形式 .database.schema.table または形式 ..schema.table を指定すると、成功しました。

この問題を解決するには、4 部構成のプレフィックスの使用を削除します。

アクセス許可

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

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

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

テーブルの行を更新する列を追加または変更するには、テーブルの UPDATE 権限が必要です。たとえば、既定値を持つ NOT NULL 列を追加する場合や、テーブルが空でないときに ID 列を追加する場合です。

例

カテゴリ	主な構文要素
列と制約を追加する	ADD * PRIMARY KEY とインデックスオプション * スパース列と列セット *
列と制約を削除する	DROP
列定義を変更する	データ型の変更 * 列のサイズの変更 * 照合順序
テーブル定義を変更する	DATA_COMPRESSION * SWITCH PARTITION * LOCK ESCALATION * 変更の追跡
制約およびトリガーを無効および有効にする	CHECK * NO CHECK * ENABLE TRIGGER * DISABLE TRIGGER
オンライン操作	ONLINE
システムのバージョン管理	SYSTEM_VERSIONING

列と制約を追加する

このセクションの例では、テーブルに列と制約を追加する方法を示します。

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

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

次の例では、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

C: 既存の列に検証なしの 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

D. 既存の列に 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

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

次の例では、新しい列ごとに定義された制約を含む列を、複数追加します。先頭の新しい列には 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

F. null 許容列を既定値と共に追加する

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

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

G. PRIMARY KEY 制約をインデックスオプションまたはデータ圧縮オプションと共に作成する

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

適用対象: SQL Server 2008 (10.0.x) 以降と Azure SQL Database。

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

このような例では、クラスター化された主キーの適用中にページの圧縮が適用されます。

USE AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (DATA_COMPRESSION = PAGE);
GO

H. スパース列を追加する

次の例では、テーブル T1 にスパース列を追加する方法と、T1 のスパース列を変更する方法を示します。テーブル T1 を作成するコードは次のとおりです。

CREATE TABLE T1 (
  C1 INT PRIMARY KEY,
  C2 VARCHAR(50) SPARSE NULL,
  C3 INT SPARSE NULL,
  C4 INT) ;
GO

スパース列 C5 を追加するには、次のステートメントを実行します。

ALTER TABLE T1
ADD C5 CHAR(100) SPARSE NULL ;
GO

非スパース列 C4 をスパース列に変換するには、次のステートメントを実行します。

ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO

スパース列 C4を非スパース列に変換するには、次のステートメントを実行します。

ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE ;
GO

I. 列セットを追加する

次の例では、テーブル T2 に列を追加する方法を示します。既にスパース列が含まれるテーブルには列セットを追加できません。テーブル T2 を作成するコードは次のとおりです。

CREATE TABLE T2 (
  C1 INT PRIMARY KEY,
  C2 VARCHAR(50) NULL,
  C3 INT NULL,
  C4 INT) ;
GO

次の 3 つのステートメントでは、CS という列セットが追加され、列 C2 および C3 が SPARSE に変更されます。

ALTER TABLE T2
ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ;
GO

ALTER TABLE T2
ALTER COLUMN C2 ADD SPARSE ;
GO

ALTER TABLE T2
ALTER COLUMN C3 ADD SPARSE ;
GO

J. 暗号化された列を追加する

次の文は、PromotionCode という名前の暗号化された列を追加します。

ALTER TABLE Customers ADD
    PromotionCode nvarchar(100)
    ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,
    ENCRYPTION_TYPE = RANDOMIZED,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') ;

K. 再開可能な操作による主キーを追加する

MAX_DURATION が 240 分の列 (a) にクラスター化された主キーを追加するための再開可能な ALTER TABLE 操作。

ALTER TABLE table1
ADD CONSTRAINT PK_Constrain PRIMARY KEY CLUSTERED (a)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

列と制約を削除する

このセクションの例では、列と制約を削除する方法を示します。

A. 1 つまたは複数の列を削除する

最初の例では、テーブルを変更して列を削除します。 2 番目の例では、複数の列を削除します。

CREATE TABLE dbo.doc_exb (
     column_a INT,
     column_b VARCHAR(20) NULL,
     column_c DATETIME,
     column_d INT) ;
GO
-- Remove a single column.
ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;
GO
-- Remove multiple columns.
ALTER TABLE dbo.doc_exb DROP COLUMN column_c, column_d;

B. 制約と列を削除する

最初の例では、テーブルから UNIQUE 制約を削除します。 2 番目の例では、2 つの制約と 1 つの列を削除します。

CREATE TABLE dbo.doc_exc (column_a INT NOT NULL CONSTRAINT my_constraint UNIQUE) ;
GO

-- Example 1. Remove a single constraint.
ALTER TABLE dbo.doc_exc DROP my_constraint ;
GO

DROP TABLE dbo.doc_exc;
GO

CREATE TABLE dbo.doc_exc ( column_a INT
                          NOT NULL CONSTRAINT my_constraint UNIQUE
                          ,column_b INT
                          NOT NULL CONSTRAINT my_pk_constraint PRIMARY KEY) ;
GO

-- Example 2. Remove two constraints and one column
-- The keyword CONSTRAINT is optional. The keyword COLUMN is required.
ALTER TABLE dbo.doc_exc
DROP CONSTRAINT my_constraint, my_pk_constraint, COLUMN column_b ;
GO

C: ONLINE モードで PRIMARY KEY 制約を削除する

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

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

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

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

CREATE TABLE Person.ContactBackup
    (ContactID INT) ;
GO

ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBackup_Contact FOREIGN KEY (ContactID)
    REFERENCES Person.Person (BusinessEntityID) ;
GO

ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBackup_Contact ;
GO

DROP TABLE Person.ContactBackup ;

列定義を変更する

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

次の例では、テーブルの列を 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

B. 列のファイルサイズを変更する

次の例では、varchar 列のサイズと、decimal 列の有効桁数および小数点以下桁数を拡張します。列にデータが含まれているため、列のサイズは拡張しかできません。また、col_a が一意インデックスで定義されていることにも注意してください。 col_a のサイズは引き続き拡張できます。データ型が varchar 型であり、インデックスが PRIMARY KEY 制約の結果ではないためです。

-- 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') ;

C: 列の照合順序を変更する

次の例では、列の照合順序を変更する方法を示します。まず、既定のユーザー照合順序でテーブルを作成します。

CREATE TABLE T3 (
  C1 INT PRIMARY KEY,
  C2 VARCHAR(50) NULL,
  C3 INT NULL,
  C4 INT) ;
GO

次に、列 C2 の照合順序を Latin1_General_BIN に変更します。データ型は変更されませんが、必須です。

ALTER TABLE T3
ALTER COLUMN C2 varchar(50) COLLATE Latin1_General_BIN ;
GO

D. 列を暗号化する

次の例では、セキュアエンクレーブを使用する Always Encrypted を使用して列を暗号化する方法を示します。

最初に、暗号化された列のないテーブルが作成されます。

CREATE TABLE T3 (
  C1 INT PRIMARY KEY,
  C2 VARCHAR(50) NULL,
  C3 INT NULL,
  C4 INT) ;
GO

次に、CEK1 という名前の列暗号化キーとランダム化された暗号化を使用して列 'C2' が暗号化されます。次のステートメントが成功するには:

列暗号化キーがエンクレーブ対応である必要があります。つまり、これはエンクレーブ計算を許可する列マスターキーを使用して暗号化する必要があります。
ターゲットの SQL Server インスタンスでは、セキュアエンクレーブを使用する Always Encrypted がサポートされている必要があります。
ステートメントは、セキュアエンクレーブを使用する Always Encrypted 用に設定された接続経由で、サポートされているクライアントドライバーを使用して発行される必要があります。
呼び出し元のアプリケーションでは、CEK1 を保護する列マスターキーへのアクセスが必要です。

ALTER TABLE T3
ALTER COLUMN C2 VARCHAR(50) ENCRYPTED
WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL;
GO

テーブル定義を変更する

このセクションの例では、テーブルの定義を変更する方法を示します。

A. テーブルを修正して圧縮を変更する

次の例では、非パーティションテーブルの圧縮を変更します。ヒープまたはクラスター化インデックスが再構築されます。テーブルがヒープの場合、すべての非クラスター化インデックスが再構築されます。

ALTER TABLE T1
REBUILD WITH (DATA_COMPRESSION = PAGE) ;

次の例では、パーティションテーブルの圧縮を変更します。 REBUILD PARTITION = 1 構文を使用すると、パーティション番号 1 のみが再構築されます。

適用対象: SQL Server 2008 (10.0.x) 以降と Azure SQL Database。

ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =NONE) ;
GO

次の代替構文を使用して同じ操作を行うと、テーブル内のすべてのパーティションが再構築されます。

適用対象: SQL Server 2008 (10.0.x) 以降と Azure SQL Database。

ALTER TABLE PartitionTable1
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1)) ;

その他のデータ圧縮の例については、「データ圧縮」を参照してください。

B. 列ストアテーブルを修正してアーカイブ圧縮を変更する

次の例では、追加の圧縮アルゴリズムを適用することで、列ストアテーブルパーティションをさらに圧縮します。この圧縮によってテーブルのサイズは小さくなりますが、保存と取得に必要な時間が増加します。これは、アーカイブや、ストレージの容量を減らす必要があり、しかも保存と取得に時間をかける余裕がある状況には便利です。

適用対象: SQL Server 2014 (12.x) 以降と Azure SQL データベース。

ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE) ;
GO

次の例では、COLUMNSTORE_ARCHIVE オプションを使って圧縮された列ストアテーブルパーティションを解凍します。復元されるデータは、すべての列ストアテーブルに使用された列ストア圧縮を使用して引き続き圧縮されます。

適用対象: SQL Server 2014 (12.x) 以降と Azure SQL データベース。

ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = COLUMNSTORE) ;
GO

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

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

D. パーティションテーブルでのロックのエスカレーションを許可する

次の例では、パーティションテーブル上で、パーティションレベルへのロックエスカレーションを有効にします。テーブルがパーティション分割されていない場合は、ロックのエスカレーションは TABLE レベルに設定されます。

適用対象: SQL Server 2008 (10.0.x) 以降と Azure SQL Database。

ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO) ;
GO

E. テーブルの変更の追跡を構成する

次の例では、Person.Person テーブルの変更の追跡を有効にします。

適用対象: SQL Server 2008 (10.0.x) 以降と Azure SQL Database。

USE AdventureWorks;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING ;

次の例では、変更の追跡を有効にし、変更時に更新される列の追跡を有効にします。

適用対象: SQL Server 2008 (10.0.x) 以降。

USE AdventureWorks;
GO
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

次の例では、Person.Person テーブルの変更の追跡を無効にします。

適用対象: SQL Server 2008 (10.0.x) 以降と Azure SQL Database。

USE AdventureWorks;
GO
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING ;

制約とトリガーを無効および有効にする

A. 制約を無効および再度有効にする

次の例では、データ内で許容される給与を制限する制約を無効にします。ここでは、NOCHECK CONSTRAINT と共に ALTER TABLE を使用して制約を無効にし、通常は制約違反となるような挿入を許可します。次に、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) ;

B. トリガーを無効にして再度有効にする

次の例では、DISABLE TRIGGER の ALTER TABLE オプションを使用してトリガーを無効にし、通常はトリガー違反となるような挿入を許可します。次に、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

オンライン操作

A. 低優先度の待機オプションを使ったオンラインインデックス再構築

次の例では、低優先度の待機オプションを指定してオンラインインデックス再構築を実行する方法を示します。

適用対象: SQL Server 2014 (12.x) 以降と Azure SQL データベース。

ALTER TABLE T1
REBUILD WITH
(
    PAD_INDEX = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES,
                                         ABORT_AFTER_WAIT = BLOCKERS ) )
) ;

B. オンラインでの列の変更

次の例では、ONLINE オプションを使用して列の変更操作を実行する方法を示します。

適用対象: SQL Server 2016 (13.x) 以降と Azure SQL データベース。

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) WITH (ONLINE = ON) ;
GO
sp_help doc_exy;
DROP TABLE dbo.doc_exy ;
GO

システムのバージョン管理

システムのバージョン管理を使用する構文に慣れるには、次の 4 つの例を参照してください。詳細については、「システムバージョン管理されたテンポラルテーブルの概要」を参照してください。

適用対象: SQL Server 2016 (13.x) 以降と Azure SQL データベース。

A. システムのバージョン管理を既存のテーブルに追加する

次の例では、既存のテーブルにシステムバージョン管理を追加して、将来の履歴テーブルを作成する方法を示します。この例では、主キーが定義された InsurancePolicy という既存のテーブルがあることを前提としています。この例では、開始と終了時刻に規定値を使って、システムのバージョン管理用に新しく期間列を設定しています。これらの値は null 値にできないためです。この例では、HIDDEN 句を使用して、現在のテーブルと対話している既存のアプリケーションに影響を与えないようにしています。 SQL Database 上でのみ利用できる HISTORY_RETENTION_PERIOD も使用しています。

--Alter non-temporal table to define periods for system versioning
ALTER TABLE InsurancePolicy
ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo),
ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
    DEFAULT SYSUTCDATETIME(),
ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
    DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999') ;

--Enable system versioning with 1 year retention for historical data
ALTER TABLE InsurancePolicy
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 1 YEAR)) ;

B. システムバージョン管理を使用するように、既存のソリューションを移行する

次の例では、一時的なサポートを再現するトリガーを使用したソリューションから、システムバージョン管理に移行する方法を示します。この例では、既存のソリューション用に ProjectTask テーブルと ProjectTaskHistory テーブルを使い、その期間用に Changed Date と Revised Date 列を使い、これらの期間列で datetime2 データ型が使われず、ProjectTask テーブルに主キーが定義されているような既存のソリューションが存在することを前提としています。

-- Drop existing trigger
DROP TRIGGER ProjectTask_HistoryTrigger;

-- Adjust the schema for current and history table
-- Change data types for existing period columns
ALTER TABLE ProjectTask ALTER COLUMN [Changed Date] datetime2 NOT NULL ;
ALTER TABLE ProjectTask ALTER COLUMN [Revised Date] datetime2 NOT NULL ;
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Changed Date] datetime2 NOT NULL ;
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Revised Date] datetime2 NOT NULL ;

-- Add SYSTEM_TIME period and set system versioning with linking two existing tables
-- (a certain set of data checks happen in the background)
ALTER TABLE ProjectTask
ADD PERIOD FOR SYSTEM_TIME ([Changed Date], [Revised Date])

ALTER TABLE ProjectTask
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProjectTaskHistory, DATA_CONSISTENCY_CHECK = ON))

C: テーブルのスキーマを変更するために、システムバージョン管理を無効にしてからもう一度有効にする

この例は、Department テーブルでシステムのバージョン管理を無効にし、列を追加し、システムのバージョン管理を再度有効にする方法を示しています。テーブルのスキーマを変更するには、システムのバージョン管理を無効にする必要があります。トランザクション内で次の手順を実行し、テーブルのスキーマの更新中に両方のテーブルが更新されるのを回避します。これにより、DBA は、もう一度システムのバージョン管理を有効にするときにデータの整合性チェックをスキップでき、パフォーマンス上の利点を得ることができます。統計の作成、パーティションの切り替え、1 つまたは両方のテーブルに対する圧縮の適用などのタスクでは、システムのバージョン管理を無効にする必要はありません。

BEGIN TRAN
/* Takes schema lock on both tables */
ALTER TABLE Department
    SET (SYSTEM_VERSIONING = OFF) ;
/* expand table schema for temporal table */
ALTER TABLE Department
     ADD Col5 int NOT NULL DEFAULT 0 ;
/* Expand table schema for history table */
ALTER TABLE DepartmentHistory
    ADD Col5 int NOT NULL DEFAULT 0 ;
/* Re-establish versioning again*/
ALTER TABLE Department
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.DepartmentHistory,
                                 DATA_CONSISTENCY_CHECK = OFF)) ;
COMMIT

D. システムバージョン管理を削除する

この例は、Department テーブルからシステムのバージョン管理を完全に削除し、DepartmentHistory テーブルを削除する方法を示しています。必要に応じて、システムバージョン管理の情報を記録するために、システムによって使用される期間の列を削除することもできます。システムのバージョン管理が有効な場合は、Department、DepartmentHistory のいずれかのテーブルを削除できません。

ALTER TABLE Department
    SET (SYSTEM_VERSIONING = OFF) ;
ALTER TABLE Department
DROP PERIOD FOR SYSTEM_TIME ;
DROP TABLE DepartmentHistory ;

例: Azure Synapse Analytics、Analytics Platform System (PDW)

次の例 A から C では、AdventureWorksPDW2022 データベースの FactResellerSales テーブルを使用します。

A. テーブルがパーティション分割されているかどうかを決定する

次のクエリでは、テーブル FactResellerSales がパーティション分割されている場合、1 つ以上の行が返されます。テーブルがパーティション分割されていない場合は、行が返されません。

SELECT * FROM sys.partitions AS p
JOIN sys.tables AS t
    ON p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL
    AND t.name = 'FactResellerSales' ;

B. パーティションテーブルの境界値を決定する

次のクエリでは、 FactResellerSales テーブルの各パーティションの境界値を返します。

SELECT t.name AS TableName, i.name AS IndexName, p.partition_number,
    p.partition_id, i.data_space_id, f.function_id, f.type_desc,
    r.boundary_id, r.value AS BoundaryValue
FROM sys.tables AS t
JOIN sys.indexes AS i
    ON t.object_id = i.object_id
JOIN sys.partitions AS p
    ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN  sys.partition_schemes AS s
    ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
    ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r
    ON f.function_id = r.function_id and r.boundary_id = p.partition_number
WHERE t.name = 'FactResellerSales' AND i.type <= 1
ORDER BY p.partition_number ;

C: パーティションテーブルのパーティション列を決定する

次のクエリでは、テーブルのパーティション分割列の名前を返します。 FactResellerSales.

SELECT t.object_id AS Object_ID, t.name AS TableName,
    ic.column_id as PartitioningColumnID, c.name AS PartitioningColumnName
FROM sys.tables AS t
JOIN sys.indexes AS i
    ON t.object_id = i.object_id
JOIN sys.columns AS c
    ON t.object_id = c.object_id
JOIN sys.partition_schemes AS ps
    ON ps.data_space_id = i.data_space_id
JOIN sys.index_columns AS ic
    ON ic.object_id = i.object_id
    AND ic.index_id = i.index_id AND ic.partition_ordinal > 0
WHERE t.name = 'FactResellerSales'
AND i.type <= 1
AND c.column_id = ic.column_id ;

D. 2 つのパーティションをマージする

次の例では、テーブル上の 2 つのパーティションをマージします。

Customer テーブルの定義は次のとおりです。

CREATE TABLE Customer (
    id INT NOT NULL,
    lastName VARCHAR(20),
    orderCount INT,
    orderDate DATE)
WITH
    ( DISTRIBUTION = HASH(id),
    PARTITION ( orderCount RANGE LEFT
    FOR VALUES (1, 5, 10, 25, 50, 100))) ;

次のコマンドは、10 と 25 のパーティション境界を結合します。

ALTER TABLE Customer MERGE RANGE (10);

テーブルの新しい DDL は次のとおりです。

CREATE TABLE Customer (
    id INT NOT NULL,
    lastName VARCHAR(20),
    orderCount INT,
    orderDate DATE)
WITH
    ( DISTRIBUTION = HASH(id),
    PARTITION ( orderCount RANGE LEFT
    FOR VALUES (1, 5, 25, 50, 100))) ;

E. パーティションを分割する

次の例では、テーブル上のパーティションを分割します。

Customer テーブルには、次の DDL があります。

DROP TABLE Customer;

CREATE TABLE Customer (
    id INT NOT NULL,
    lastName VARCHAR(20),
    orderCount INT,
    orderDate DATE)
WITH
    ( DISTRIBUTION = HASH(id),
    PARTITION ( orderCount RANGE LEFT
    FOR VALUES (1, 5, 10, 25, 50, 100 ))) ;

次のコマンドは、50 と 100 の間に値 75 にバインドされた新しいパーティションを作成します。

ALTER TABLE Customer SPLIT RANGE (75);

テーブルの新しい DDL は次のとおりです。

CREATE TABLE Customer (
   id INT NOT NULL,
   lastName VARCHAR(20),
   orderCount INT,
   orderDate DATE)
   WITH DISTRIBUTION = HASH(id),
   PARTITION ( orderCount (RANGE LEFT
      FOR VALUES (1, 5, 10, 25, 50, 75, 100))) ;

F. SWITCH を使用してパーティションを履歴テーブルに移動する

次の例では、Orders テーブルのパーティション内のデータを OrdersHistory テーブル内のパーティションに移動します。

Orders テーブルには、次の DDL があります。

CREATE TABLE Orders (
    id INT,
    city VARCHAR (25),
    lastUpdateDate DATE,
    orderDate DATE)
WITH
    (DISTRIBUTION = HASH (id),
    PARTITION ( orderDate RANGE RIGHT
    FOR VALUES ('2004-01-01', '2005-01-01', '2006-01-01', '2007-01-01'))) ;

この例では、Orders テーブルに次のパーティションがあります。各パーティションにはデータがあります。

Partition	データがある	境界の範囲
1	はい	OrderDate < '2004-01-01'
2	はい	'2004-01-01' <= OrderDate < '2005-01-01'
3	はい	'2005-01-01' <= OrderDate< '2006-01-01'
4	はい	'2006-01-01'<= OrderDate < '2007-01-01'
5	はい	'2007-01-01' <= OrderDate

パーティション 1 (データがある): OrderDate < '2004-01-01'
パーティション 2 (データがある): '2004-01-01' <= OrderDate < '2005-01-01'
パーティション 3 (データがある): '2005-01-01' <= OrderDate< '2006-01-01'
パーティション 4 (データがある): '2006-01-01'<= OrderDate < '2007-01-01'
パーティション 5 (データがある): '2007-01-01' <= OrderDate

OrdersHistory テーブルには、Orders テーブルと列と列名が同じ次の DDL があります。どちらも id 列に対してハッシュ分散されています。

CREATE TABLE OrdersHistory (
   id INT,
   city VARCHAR (25),
   lastUpdateDate DATE,
   orderDate DATE)
WITH
    (DISTRIBUTION = HASH (id),
    PARTITION ( orderDate RANGE RIGHT
    FOR VALUES ('2004-01-01'))) ;

列と列名は同じである必要がありますが、パーティションの境界が同じである必要はありません。この例では、OrdersHistory テーブルに次の 2 つのパーティションがあり、両方のパーティションが空です。

パーティション 1 (データがない): OrderDate < '2004-01-01'
パーティション 2 (空): '2004-01-01' <= OrderDate

これら 2 つのテーブルの場合、次のコマンドで、OrderDate < '2004-01-01' があるすべての行は Orders テーブルから OrdersHistory テーブルに移動されます。

ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersHistory PARTITION 1;

その結果、Orders の最初のパーティションは空になり、OrdersHistory の最初のパーティションにはデータがある状態になります。テーブルは次のようになります。

Orders テーブル

パーティション 1 (空): OrderDate < '2004-01-01'
パーティション 2 (データがある): '2004-01-01' <= OrderDate < '2005-01-01'
パーティション 3 (データがある): '2005-01-01' <= OrderDate< '2006-01-01'
パーティション 4 (データがある): '2006-01-01'<= OrderDate < '2007-01-01'
パーティション 5 (データがある): '2007-01-01' <= OrderDate

OrdersHistory テーブル

パーティション 1 (データがある): OrderDate < '2004-01-01'
パーティション 2 (空): '2004-01-01' <= OrderDate

Orders テーブルをクリーンアップするには、次のようにパーティション 1 と 2 をマージして空のパーティションを削除します。

ALTER TABLE Orders MERGE RANGE ('2004-01-01');

マージ後、Orders テーブルには次のパーティションがあります。

Orders テーブル

パーティション 1 (データがある): OrderDate < '2005-01-01'
パーティション 2 (データがある): '2005-01-01' <= OrderDate< '2006-01-01'
パーティション 3 (データがある): '2006-01-01'<= OrderDate < '2007-01-01'
パーティション 4 (データがある): '2007-01-01' <= OrderDate

もう 1 年が経過し、2005 年をアーカイブする準備が整ったとします。空のパーティションを次のように分割して、2005 年の空のパーティションを OrdersHistory テーブルに割り当てることができます。

ALTER TABLE OrdersHistory SPLIT RANGE ('2005-01-01');

分割後、OrdersHistory テーブルには次のパーティションがあります。

OrdersHistory テーブル

パーティション 1 (データがある): OrderDate < '2004-01-01'
パーティション 2 (空): '2004-01-01' < '2005-01-01'
パーティション 3 (空): '2005-01-01' <= OrderDate

ALTER TABLE (Transact-SQL)

ディスク ベース テーブルの構文

メモリ最適化テーブルの構文

Azure Synapse Analytics と Parallel Data Warehouse の構文

Microsoft Fabric のウェアハウスの構文

引数

database_name

schema_name

table_name

ALTER COLUMN

column_name

[ type_schema_name. ] type_name

有効桁数 (precision)

scale

最大

xml_schema_collection

COLLATE <collation_name>

NULL | NOT NULL

[ {ADD | DROP} ROWGUIDCOL ]

[ {ADD | DROP} PERSISTED ]

DROP NOT FOR REPLICATION

SPARSE

ADD MASKED WITH ( FUNCTION = ' mask_function ')

WITH ( ONLINE = ON | OFF) (<列の変更の適用対象として>)

WITH CHECK | WITH NOCHECK

ALTER INDEX index_name

ADD

PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )

DROP

CONSTRAINT constraint_name

INDEX index_name

COLUMN column_name

FOR SYSTEM_TIME の期間

WITH <drop_clustered_constraint_option>

MAXDOP = max_degree_of_parallelism

ONLINE = { ON | OFF } drop_clustered_constraint_option > に適用する < の場合

MOVE TO { partition_scheme_name(column_name [ ,...n ] ) | filegroup | "default" }

{ CHECK | NOCHECK } CONSTRAINT

{ ENABLE | DISABLE } TRIGGER

{ ENABLE | DISABLE } CHANGE_TRACKING

WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )

SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name. ] target_table [ PARTITION target_partition_number_expression ]

SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default" | "NULL" })

SET ( SYSTEM_VERSIONING = { OFF | ON [ ( HISTORY_TABLE = schema_name . history_table_name [ , DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ] } )

HISTORY_RETENTION_PERIOD = { INFINITE | number {DAY | DAYS | WEEK | WEEKS | MONTH | MONTHS | YEAR | YEARS} }

DATA_DELETION

SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

REBUILD

PARTITION = ALL

REBUILD WITH ( <rebuild_option> )

DATA_COMPRESSION

XML_COMPRESSION

ONLINE = { ON | OFF } single_partition_rebuild_option > に適用する < の場合

column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

{ ENABLE | DISABLE } FILETABLE_NAMESPACE

SET ( FILETABLE_DIRECTORY = directory_name )

REMOTE_DATA_ARCHIVE

[ FILTER_PREDICATE = { null | predicate } ]

MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }

WAIT_AT_LOW_PRIORITY

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

IF EXISTS

RESUMABLE = { ON | OFF}

解説

列のファイル サイズを変更する

ロックと ALTER TABLE

パーティション スイッチの XEvents

オンライン操作としての NOT NULL 列を追加する

並列プランの実行

パーティション テーブル

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

インデックスと ALTER TABLE

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

データ圧縮

NTEXT 列を削除する

オンラインでのインデックス再構築

互換性サポート

アクセス許可

例

列と制約を追加する

ディスクベーステーブルの構文

列のファイルサイズを変更する

パーティションスイッチの XEvents

パーティションテーブル

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

G. PRIMARY KEY 制約をインデックスオプションまたはデータ圧縮オプションと共に作成する

B. 列のファイルサイズを変更する

B. 列ストアテーブルを修正してアーカイブ圧縮を変更する

D. パーティションテーブルでのロックのエスカレーションを許可する

A. 低優先度の待機オプションを使ったオンラインインデックス再構築

B. システムバージョン管理を使用するように、既存のソリューションを移行する

C: テーブルのスキーマを変更するために、システムバージョン管理を無効にしてからもう一度有効にする

D. システムバージョン管理を削除する

B. パーティションテーブルの境界値を決定する

C: パーティションテーブルのパーティション列を決定する