ALTER TABLE (Transact-SQL)

2025-08-16

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)Microsoft Fabric のウェアハウス Microsoft Fabric プレビューの SQL データベース

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

Note

現在、Fabric Warehouse の ALTER TABLE は、制約と null 許容列の追加でのみサポートされています。 Microsoft Fabric の Warehouse の構文に関するページを参照してください。現在、メモリ最適化テーブルは、Microsoft Fabric プレビューの SQL データベースでは使用できません。

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

ディスクベースのテーブル:

Syntax
Examples

メモリ最適化テーブル:

構文表記規則の詳細については、「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 } )
}

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

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

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 の構文

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 プールでは、外部および一時テーブルのみがサポートされます。

Fabric の Warehouse の構文

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
  ADD  { column_name <data_type> [COLLATE collation_name] [ <column_options> ] } [ ,...n ]
| ADD { <column_constraint> FOR column_name} [ ,...n ]
| DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]
}
[ ; ]

<column_options> ::=
[ NULL ] -- default is NULL

<data type> ::=
datetime2 ( n )
| date
| time ( n )
| float [ ( n ) ]
| real [ ( n ) ]
| decimal [ ( precision [ , scale ] ) ]
| numeric [ ( precision [ , scale ] ) ]
| bigint
| int
| smallint
| bit
| varchar [ ( n ) ]
| char [ ( n ) ]
| varbinary [ ( n ) ]
| uniqueidentifier

<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
    }

Arguments

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 にすることはできません。
ALTER COLUMNの既定値ANSI_NULL常にオンになります。指定しない場合、列は null 許容になります。
ANSI_PADDING埋め込みは常にALTER COLUMNにON。
変更する列が ID 列の場合、new_data_type は、ID プロパティをサポートするデータ型であることが必要です。
SET ARITHABORTの現在の設定は無視されます。 ALTER TABLE は、 ARITHABORT が ONに設定されているかのように動作します。

Note

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

precision

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

scale

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

max

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

xml_schema_collection

適用対象: SQL Server と Azure SQL Database。

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

COLLATE <collation_name>

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

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

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

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

サポートされている照合順序の詳細については、「COLLATE 参照してください。

NULL |NULL でない

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

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

ALTER COLUMNでNULLを指定すると、PRIMARY KEY制約内の列を除き、NOT NULL列で null 値を許可するように強制できます。列に null 値が含ALTER COLUMN場合にのみ、NOT 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

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

[ {ADD |DROP} ROWGUIDCOL ]

適用対象: SQL Server と Azure SQL Database。

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

ROWGUIDCOL は、列に格納されている値の一意性を強制せず、テーブルに挿入された新しい行の値を自動的に生成しません。列ごとに一意の値を生成するには、NEWID() ステートメントで NEWSEQUENTIALID() または INSERT 関数を使用します。または、列の既定値として NEWID() または NEWSEQUENTIALID() 関数を指定します。

[ {ADD |DROP} PERSISTED ]

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

SET QUOTED_IDENTIFIER は、計算列またはインデックス付きビューでインデックスを作成または変更するときに ON する必要があります。詳しくは、「SET QUOTED_IDENTIFIER」をご覧ください。

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

Note

Fabric SQL データベースでは、計算列は許可されますが、現在、Fabric OneLake にはミラー化されていません。

レプリケーションに対して削除しない

適用対象: SQL Server と Azure SQL Database。

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

SPARSE

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

ADD MASKED WITH ( FUNCTION = 'mask_function')

適用対象: SQL Server 2016 (13.x) 以降のバージョン、および Azure SQL Database。

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

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

ALTER ANY MASKアクセス許可が必要です。

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

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

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

適用対象: SQL Server 2016 (13.x) 以降のバージョン、および Azure SQL Database。

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

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

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

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

オンラインでのインデックスの再構築は、従来の 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 は、メモリ最適化テーブルでのみサポートされます。

Important

ALTER TABLE ステートメントを使用しない場合、CREATE INDEX、DROP INDEX、ALTER INDEX、および PAD_INDEX ステートメントは、メモリ最適化テーブルのインデックスではサポートされません。

ADD

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

Note

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

Important

PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )

適用対象: SQL Server 2017 (14.x) 以降のバージョン、および Azure SQL Database。

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

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

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

DROP

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

Note

台帳テーブルで削除された列は、論理的に削除されただけです。削除された列は台帳テーブルに残りますが、sys.tablesのdropped_ledger_table列を1に設定することで、削除された列としてマークされます。 dropped_ledger_view にある sys.tables 列を 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 がテーブルから削除されることを指定します。

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

Important

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 2016 (13.x) 以降のバージョン、および Azure SQL Database。

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

WITH <drop_clustered_constraint_option>

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

MAXDOP = max_degree_of_parallelism

適用対象: SQL Server と Azure SQL Database。

操作中は、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 と Azure SQL Database。

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

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 と Azure SQL Database。

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

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

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

WITH ( TRACK_COLUMNS_UPDATED = { ON |OFF } )

適用対象: SQL Server と Azure SQL Database。

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

[パーティション source_partition_number_expression ] を [ schema_nameに切り替えます。 ] target_table [ PARTITION target_partition_number_expression ]

適用対象: SQL Server と Azure SQL Database。

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

テーブルのすべてのデータを、既に存在するパーティションテーブルにパーティションとして再度割り当てます。
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操作を実行する前に、非クラスター化列ストアインデックスを現在の形式 (更新可能) に再構築する必要があります。

Limitations

非クラスター化インデックスを含め、両方のテーブルが同じようにパーティション分割されていて、ターゲットテーブルに非クラスター化インデックスがない場合は、 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。 Azure SQL データベースは FILESTREAM をサポートしていません。

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

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

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

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

"default" は、 DEFAULT プロパティが設定された FILESTREAM ファイルグループを指定します。 FILESTREAM ファイルグループがない場合は、エラーが発生します。

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

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

適用対象: SQL Server 2016 (13.x) 以降のバージョン、および Azure SQL Database。

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

HISTORY_RETENTION_PERIOD = { INFINITE | number { DAY |DAYS |WEEK |週 |MONTH |MONTHS |YEAR |年 }

適用対象: 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 |週 |MONTH |MONTHS |YEAR |年 }

テーブルの保持期間のポリシーを指定します。保持期間は、正の整数値と日付部分の単位を組み合わせて指定します。

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

適用対象: SQL Server と Azure SQL Database。

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

AUTO

このオプションを指定すると、SQL Server データベースエンジンで、テーブルスキーマに適したロックのエスカレーションの細分性を選択できます。
- テーブルがパーティション分割されている場合、ヒープまたは B ツリー (HoBT) の細分性に対するロックエスカレーションが許可されます。言い換えると、パーティションレベルへのエスカレーションが許可されます。ロックが HoBT レベルにエスカレートされた後、ロックは後で TABLE 細分性にエスカレートされません。
- テーブルがパーティション分割されていない場合、ロックのエスカレーションは TABLE 細分性に対して行われます。
TABLE

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

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

REBUILD

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

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

PARTITION = ALL

適用対象: SQL Server と Azure SQL Database。

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

REBUILD WITH ( <rebuild_option> )

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

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

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

DATA_COMPRESSION

適用対象: SQL Server と Azure SQL Database。

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

NONE

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

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

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

適用対象: SQL Server 2014 (12.x) 以降のバージョン、および Azure SQL Database。

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

適用対象: SQL Server 2014 (12.x) 以降のバージョン、および Azure SQL Database。

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

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

ALTER TABLE REBUILD PARTITION WITH DATA COMPRESSION = ROW または PAGE は、Microsoft Fabric プレビューの SQL データベースでは許可されていません。

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 と Azure SQL Database。

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

{ ENABLE |DISABLE } FILETABLE_NAMESPACE

適用対象: SQL Server。

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

SET ( FILETABLE_DIRECTORY = directory_name )

適用対象: SQL Server。 Azure SQL Database では FileTable はサポートされていません。

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

REMOTE_DATA_ARCHIVE

適用対象: SQL Server 2017 (14.x) 以降のバージョン。

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

Important

拡張データベースは、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」を参照してください。

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

Permissions. データベースまたはテーブルの 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 2017 (14.x) 以降のバージョン。

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

Important

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

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

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

MIGRATION_STATE = { OUTBOUND |INBOUND |PAUSED }

適用対象: 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 2014 (12.x) 以降のバージョン、および Azure SQL Database。

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

MAX_DURATION = 時間 [ MINUTES ]

適用対象: SQL Server 2014 (12.x) 以降のバージョン、および Azure SQL Database。

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

ABORT_AFTER_WAIT = { NONE |SELF |BLOCKERS }

適用対象: SQL Server 2014 (12.x) 以降のバージョン、および Azure SQL Database。

NONE

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

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

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

ALTER ANY CONNECTIONアクセス許可が必要です。

IF EXISTS

適用対象: SQL Server 2016 (13.x) 以降のバージョン、および Azure SQL Database。

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

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 操作の有効化と使用の詳細については、「再開可能なテーブルの追加制約」を参照してください。

Remarks

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

プロシージャキャッシュ内にテーブルを参照する実行プランがある場合、 ALTER TABLE は次回の実行時に再コンパイルされるようにマークします。

Microsoft Fabric プレビューの SQL データベースでは、一部のテーブル機能を作成できますが、 Fabric OneLake にはミラー化されません。詳細については、「 Fabric SQL データベースミラーリング (プレビュー)の制限事項」を参照してください。

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

列のデータ型の新しいサイズを指定すると、列の長さ、有効桁数、または小数点以下桁数を変更できます。 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 列の追加など、大きなテーブルのすべての行に影響を与える変更は、完了して多数のログレコードを生成するのに時間がかかる場合があります。多数の行に影響を与えるINSERT、UPDATE、またはDELETEステートメントと同じ注意を払って、これらのALTER TABLEステートメントを実行します。

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 ユーザー定義型の列を追加できません。行の最大サイズが 8,060 バイトの制限を超える場合は、列をオンラインで追加することはできません。この場合、列はオフライン操作として追加されます。

並列プランの実行

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

パーティションテーブル

パーティションテーブルを含む 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が指定されていない場合、テーブルはクラスター化インデックスに対して定義されたのと同じパーティション構成またはファイルグループに配置されます。

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

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

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

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

Note

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

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

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 オプションを使用すると、DBA はオンラインインデックスの再構築に必要な S ロックと Sch-M ロックを管理できます。 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 部構成のプレフィックスの使用を削除します。

Permissions

テーブル ALTER アクセス許可が必要です。

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

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

テーブルの行を更新する列を追加または変更するには、テーブル UPDATE アクセス許可が必要です。たとえば、既定値を持つ NOT NULL 列を追加したり、テーブルが空でない場合に ID 列を追加したりします。

Examples

この記事のコードサンプルでは、microsoft AdventureWorks2022のホームページからダウンロードできるAdventureWorksDW2022またはサンプルデータベースを使用します。

Category	主な構文要素
列と制約を追加する	`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

EXECUTE 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

EXECUTE 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 a PRIMARY KEY identity column.
    ADD 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 FOREIGN KEY 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

EXECUTE 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
        CONSTRAINT AddDateDflt DEFAULT GETDATE() WITH VALUES NULL;
GO

DROP TABLE dbo.doc_exf;
GO

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

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

適用対象: SQL Server と Azure SQL Database。

USE AdventureWorks2022;
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 AdventureWorks2022;
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. 再開可能な操作による主キーを追加する

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

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 が一意インデックスで定義されていることにも注意してください。データ型が varchar であり、インデックスがPRIMARY KEY制約の結果ではないので、col_aのサイズを引き続き大きくできます。

-- 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' が暗号化されます。次のステートメントが成功するには:

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

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。

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

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

適用対象: SQL Server。

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

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

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

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

適用対象: SQL Server 2014 (12.x) 以降のバージョン、および Azure SQL Database。

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

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

適用対象: SQL Server 2014 (12.x) 以降のバージョン、および Azure SQL Database。

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 と Azure SQL Database。

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

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

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

適用対象: SQL Server と Azure SQL Database。

USE AdventureWorks2022;

ALTER TABLE Person.Person ENABLE CHANGE_TRACKING;

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

適用対象: SQL Server。

USE AdventureWorks2022;
GO

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

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

適用対象: SQL Server と Azure SQL Database。

USE AdventureWorks2022;
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 fails.
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;
           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 Database。

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 Database。

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

EXECUTE sp_help doc_exy;
DROP TABLE dbo.doc_exy;
GO

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

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

適用対象: SQL Server 2016 (13.x) 以降のバージョン、および Azure SQL Database。

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

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

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

--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 では、FactResellerSales データベースのテーブルを使用します。

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

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

SELECT *
FROM sys.partitions AS p
     INNER 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
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.partitions AS p
         ON i.object_id = p.object_id
        AND i.index_id = p.index_id
     INNER JOIN sys.partition_schemes AS s
         ON i.data_space_id = s.data_space_id
     INNER JOIN sys.partition_functions AS f
         ON s.function_id = f.function_id
     LEFT OUTER 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
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.columns AS c
         ON t.object_id = c.object_id
     INNER JOIN sys.partition_schemes AS ps
         ON ps.data_space_id = i.data_space_id
     INNER 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	Yes	`OrderDate < '2004-01-01'`
2	Yes	`'2004-01-01' <= OrderDate < '2005-01-01'`
3	Yes	`'2005-01-01' <= OrderDate< '2006-01-01'`
4	Yes	`'2006-01-01'<= OrderDate < '2007-01-01'`
5	Yes	`'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 の構文

Fabric の Warehouse の構文

Arguments

database_name

schema_name

table_name

ALTER COLUMN

column_name

[ type_schema_name。 ] type_name

precision

scale

max

xml_schema_collection

COLLATE <collation_name>

NULL |NULL でない

[ {ADD |DROP} ROWGUIDCOL ]

[ {ADD |DROP} PERSISTED ]

レプリケーションに対して削除しない

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 } )

[パーティション source_partition_number_expression ] を [ 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 |週 |MONTH |MONTHS |YEAR |年 }

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

MAX_DURATION = 時間 [ MINUTES ]

ABORT_AFTER_WAIT = { NONE |SELF |BLOCKERS }

IF EXISTS

RESUMABLE = { ON |OFF}

Remarks

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

ロックと ALTER TABLE

パーティション スイッチの拡張イベント (XEvents)

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

並列プランの実行

パーティション テーブル

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

インデックスと ALTER TABLE

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

データ圧縮

ntext 列を削除する

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

互換性のサポート

Permissions

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

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

パーティションスイッチの拡張イベント (XEvents)

パーティションテーブル

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

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

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

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

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

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

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

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

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

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

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

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