ALTER TABLE (Transact-SQL)

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Memodifikasi definisi tabel dengan mengubah, menambahkan, atau menghilangkan kolom dan batasan. ALTER TABLE juga menetapkan ulang dan membangun ulang partisi, atau menonaktifkan dan mengaktifkan batasan dan pemicu.

Penting

Sintaks untuk ALTER TABLE berbeda untuk tabel berbasis disk dan tabel yang dioptimalkan memori. Gunakan tautan berikut untuk membawa Anda langsung ke blok sintaks yang sesuai untuk jenis tabel Anda dan ke contoh sintaks yang sesuai:

Untuk informasi selengkapnya tentang konvensi sintaks, lihat Konvensi Sintaks T-SQL.

Sintaks untuk tabel berbasis disk

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

Sintaks untuk tabel yang dioptimalkan memori

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

Sintaks untuk Analitik Azure Synapse dan Gudang Data Paralel

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

Catatan

Kumpulan SQL tanpa server di Azure Synapse Analytics hanya mendukung tabel eksternal dan sementara.

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

database_name

Nama database tempat tabel dibuat.

nama_skema

Nama skema tempat tabel berada.

table_name

Nama tabel yang akan diubah. Jika tabel tidak berada dalam database saat ini atau dimuat oleh skema yang dimiliki oleh pengguna saat ini, Anda harus secara eksplisit menentukan database dan skema.

KOLOM ALTER

Menentukan bahwa kolom bernama akan diubah atau diubah.

Kolom yang dimodifikasi tidak boleh:

  • Kolom dengan jenis data tanda waktu .

  • ROWGUIDCOL untuk tabel.

  • Kolom komputasi atau digunakan dalam kolom komputasi.

  • Digunakan dalam statistik yang dihasilkan oleh pernyataan CREATE STATISTICS. Pengguna perlu menjalankan DROP STATISTICS untuk menghilangkan statistik sebelum ALTER COLUMN dapat berhasil. Jalankan kueri ini untuk mendapatkan semua kolom statistik dan statistik yang dibuat pengguna untuk tabel.

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

    Catatan

    Statistik yang secara otomatis dihasilkan oleh pengoptimal kueri secara otomatis dihilangkan oleh ALTER COLUMN.

  • Digunakan dalam batasan PRIMARY KEY atau [FOREIGN KEY] REFERENCES.

  • Digunakan dalam batasan CHECK atau UNIQUE. Tetapi, mengubah panjang kolom panjang variabel yang digunakan dalam batasan CHECK atau UNIQUE diperbolehkan.

  • Terkait dengan definisi default. Namun, panjang, presisi, atau skala kolom dapat diubah jika jenis data tidak diubah.

Jenis data kolom teks, ntext, dan gambar hanya dapat diubah dengan cara berikut:

  • teks ke varchar(max), nvarchar(max), atau xml
  • ntext ke varchar(max), nvarchar(max), atau xml
  • gambar ke varbinary(max)

Beberapa perubahan jenis data dapat menyebabkan perubahan data. Misalnya, mengubah kolom nchar atau nvarchar , menjadi char atau varchar, dapat menyebabkan konversi karakter yang diperluas. Untuk informasi selengkapnya, lihat CAST dan CONVERT. Mengurangi presisi atau skala kolom dapat menyebabkan pemotokan data.

Catatan

Jenis data kolom tabel yang dipartisi tidak dapat diubah.

Jenis data kolom yang disertakan dalam indeks tidak dapat diubah kecuali kolom adalah jenis data varchar, nvarchar, atau varbinary , dan ukuran baru sama dengan atau lebih besar dari ukuran lama.

Kolom yang disertakan dalam batasan kunci primer, tidak dapat diubah dari NOT NULL ke NULL.

Saat menggunakan Always Encrypted (tanpa enklave aman), jika kolom yang sedang dimodifikasi dienkripsi dengan 'ENCRYPTED WITH', Anda dapat mengubah jenis data menjadi jenis data yang kompatibel (seperti INT ke BIGINT), tetapi Anda tidak dapat mengubah pengaturan enkripsi apa pun.

Saat menggunakan Always Encrypted dengan enklave aman, Anda dapat mengubah pengaturan enkripsi apa pun, jika kunci enkripsi kolom melindungi kolom (dan kunci enkripsi kolom baru, jika Anda mengubah kunci) mendukung komputasi enklave (dienkripsi dengan kunci master kolom yang mendukung enklave). Untuk detailnya, lihat Always Encrypted dengan enklave aman.

column_name

Nama kolom yang akan diubah, ditambahkan, atau dihilangkan. Maksimum column_name adalah 128 karakter. Untuk kolom baru, Anda dapat menghilangkan column_name untuk kolom yang dibuat dengan jenis data tanda waktu . Tanda waktu nama digunakan jika Anda tidak menentukan column_name untuk kolom jenis data tanda waktu .

Catatan

Kolom baru ditambahkan setelah semua kolom yang ada dalam tabel diubah.

[ type_schema_name.type_name

Jenis data baru untuk kolom yang diubah, atau jenis data untuk kolom yang ditambahkan. Anda tidak dapat menentukan type_name untuk kolom tabel yang dipartisi yang sudah ada. type_name bisa menjadi salah satu jenis berikut:

  • Jenis data sistem SQL Server.
  • Jenis data alias berdasarkan jenis data sistem SQL Server. Anda membuat jenis data alias dengan pernyataan CREATE TYPE sebelum dapat digunakan dalam definisi tabel.
  • Jenis .NET Framework yang ditentukan pengguna, dan skema tempatnya berada. Anda membuat jenis yang ditentukan pengguna dengan pernyataan CREATE TYPE sebelum dapat digunakan dalam definisi tabel.

Berikut ini adalah kriteria untuk type_name kolom yang diubah:

  • Jenis data sebelumnya harus secara implisit dapat dikonversi ke jenis data baru.
  • type_name tidak bisa menjadi tanda waktu.
  • ANSI_NULL default selalu aktif untuk UBAH KOLOM; jika tidak ditentukan, kolom dapat diubah ke null.
  • ANSI_PADDING padding selalu AKTIF untuk UBAH KOLOM.
  • Jika kolom yang dimodifikasi adalah kolom identitas, new_data_type harus merupakan jenis data yang mendukung properti identitas.
  • Pengaturan saat ini untuk SET ARITHABORT diabaikan. ALTER TABLE beroperasi seolah-olah ARITHABORT diatur ke AKTIF.

Catatan

Jika klausa COLLATE tidak ditentukan, mengubah jenis data kolom menyebabkan perubahan kolater ke kolater default database.

Presisi

Presisi untuk jenis data yang ditentukan. Untuk informasi selengkapnya tentang nilai presisi yang valid, lihat Presisi, Skala, dan Panjang.

Skala

Skala untuk jenis data yang ditentukan. Untuk informasi selengkapnya tentang nilai skala yang valid, lihat Presisi, Skala, dan Panjang.

max

Hanya berlaku untuk jenis data varchar, nvarchar, dan varbinary untuk menyimpan 2^31-1 byte karakter, data biner, dan data Unicode.

xml_schema_collection

Berlaku untuk: SQL Server (SQL Server 2008 dan yang lebih baru) dan Database Azure SQL.

Hanya berlaku untuk jenis data xml untuk mengaitkan skema XML dengan jenis . Sebelum mengetik kolom xml ke koleksi skema, Anda terlebih dahulu membuat koleksi skema dalam database dengan menggunakan CREATE XML SCHEMA COLLECTION.

MENYUSUN <collation_name>

Menentukan kolase baru untuk kolom yang diubah. Jika tidak ditentukan, kolom diberi kolase default database. Nama kolase dapat berupa nama kolase Windows atau nama kolase SQL. Untuk daftar dan informasi selengkapnya, lihat Nama Kolate Windows dan Nama Kolatasi SQL Server.

Klausa COLLATE mengubah kolab hanya kolom jenis data karakter, varchar, nchar, dan nvarchar . Untuk mengubah kolase kolom jenis data alias yang ditentukan pengguna, gunakan pernyataan ALTER TABLE terpisah untuk mengubah kolom menjadi jenis data sistem SQL Server. Kemudian, ubah kolabnya dan ubah kolom kembali ke jenis data alias.

UBAH KOLOM tidak dapat memiliki perubahan kolase jika ada satu atau beberapa kondisi berikut:

  • Jika batasan CHECK, batasan KUNCI ASING, atau kolom komputasi mereferensikan kolom berubah.
  • Jika ada indeks, statistik, atau indeks teks lengkap yang dibuat pada kolom. Statistik yang dibuat secara otomatis pada kolom yang diubah akan dihilangkan jika kolate kolom diubah.
  • Jika tampilan atau fungsi terikat skema mereferensikan kolom.

Untuk informasi selengkapnya, lihat MENYUSUN.

| NULL BUKAN NULL

Menentukan apakah kolom dapat menerima nilai null. Kolom yang tidak mengizinkan nilai null ditambahkan dengan ALTER TABLE hanya jika memiliki default yang ditentukan atau jika tabel kosong. Anda dapat menentukan NOT NULL untuk kolom komputasi hanya jika Anda juga telah menentukan PERSISTED. Jika kolom baru mengizinkan nilai null dan Anda tidak menentukan default, kolom baru berisi nilai null untuk setiap baris dalam tabel. Jika kolom baru mengizinkan nilai null dan Anda menambahkan definisi default dengan kolom baru, Anda bisa menggunakan WITH VALUES untuk menyimpan nilai default di kolom baru untuk setiap baris yang ada dalam tabel.

Jika kolom baru tidak mengizinkan nilai null dan tabel tidak kosong, Anda harus menambahkan definisi DEFAULT dengan kolom baru. Dan, kolom baru secara otomatis dimuat dengan nilai default di kolom baru di setiap baris yang ada.

Anda dapat menentukan NULL di ALTER COLUMN untuk memaksa kolom NOT NULL untuk mengizinkan nilai null, kecuali untuk kolom dalam batasan PRIMARY KEY. Anda dapat menentukan NOT NULL dalam ALTER COLUMN hanya jika kolom tidak berisi nilai null. Nilai null harus diperbarui ke beberapa nilai sebelum ALTER COLUMN NOT NULL diizinkan, misalnya:

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

Saat Anda membuat atau mengubah tabel dengan pernyataan CREATE TABLE atau ALTER TABLE, pengaturan database dan sesi memengaruhi dan mungkin mengambil alih nullabilitas jenis data yang digunakan dalam definisi kolom. Pastikan Anda selalu secara eksplisit menentukan kolom sebagai NULL atau NOT NULL untuk kolom yang tidak dikomputasi.

Jika Anda menambahkan kolom dengan jenis data yang ditentukan pengguna, pastikan untuk menentukan kolom dengan nullability yang sama dengan jenis data yang ditentukan pengguna. Dan, tentukan nilai default untuk kolom. Untuk informasi selengkapnya, lihat CREATE TABLE.

Catatan

Jika NULL atau NOT NULL ditentukan dengan ALTER COLUMN, new_data_type [(presisi [, scale ])] juga harus ditentukan. Jika jenis data, presisi, dan skala tidak diubah, tentukan nilai kolom saat ini.

[ {ADD | DROP} ROWGUIDCOL ]

Berlaku untuk: SQL Server (SQL Server 2008 dan yang lebih baru) dan Database Azure SQL.

Menentukan bahwa properti ROWGUIDCOL ditambahkan ke atau dihilangkan dari kolom yang ditentukan. ROWGUIDCOL menunjukkan bahwa kolom adalah kolom GUID baris. Anda hanya dapat mengatur satu kolom pengidentifikasi unik per tabel sebagai kolom ROWGUIDCOL. Dan, Anda hanya dapat menetapkan properti ROWGUIDCOL ke kolom pengidentifikasi unik . Anda tidak dapat menetapkan ROWGUIDCOL ke kolom jenis data yang ditentukan pengguna.

ROWGUIDCOL tidak memberlakukan keunikan nilai yang disimpan di kolom dan tidak secara otomatis menghasilkan nilai untuk baris baru yang disisipkan ke dalam tabel. Untuk menghasilkan nilai unik untuk setiap kolom, gunakan fungsi NEWID atau NEWSEQUENTIALID pada pernyataan INSERT. Atau, tentukan fungsi NEWID atau NEWSEQUENTIALID sebagai default untuk kolom.

[ {ADD | DROP} BERTAHAN ]

Menentukan bahwa properti PERSISTED ditambahkan ke atau dihilangkan dari kolom yang ditentukan. Kolom harus berupa kolom komputasi yang ditentukan dengan ekspresi deterministik. Untuk kolom yang ditentukan sebagai PERSISTED, Mesin Database secara fisik menyimpan nilai komputasi dalam tabel dan memperbarui nilai ketika kolom lain yang bergantung pada kolom komputasi diperbarui. Dengan menandai kolom komputasi sebagai PERSISTED, Anda dapat membuat indeks pada kolom komputasi yang ditentukan pada ekspresi yang deterministik, tetapi tidak tepat. Untuk informasi selengkapnya, lihat Indeks pada Kolom Komputasi.

SET QUOTED_IDENTIFIER harus AKTIF saat Anda membuat atau mengubah indeks pada kolom komputasi atau tampilan terindeks. Untuk informasi selengkapnya, lihat MENGATUR QUOTED_IDENTIFIER (Transact-SQL).

Setiap kolom komputasi yang digunakan sebagai kolom partisi dari tabel yang dipartisi harus ditandai secara eksplisit PERSISTED.

HILANGKAN BUKAN UNTUK REPLIKASI

Berlaku untuk: SQL Server (SQL Server 2008 dan yang lebih baru) dan Database Azure SQL.

Menentukan bahwa nilai bertahap dalam kolom identitas saat agen replikasi melakukan operasi penyisipan. Anda dapat menentukan klausa ini hanya jika column_name adalah kolom identitas.

JARANG

Menunjukkan bahwa kolom adalah kolom jarang. Penyimpanan kolom jarang dioptimalkan untuk nilai null. Anda tidak dapat mengatur kolom jarang sebagai NOT NULL. Mengonversi kolom dari jarang ke nonsparse, atau dari yang tidak jarang ke jarang, mengunci tabel selama durasi eksekusi perintah. Anda mungkin perlu menggunakan klausa PEMBANGUNAN ULANG untuk mengklaim kembali penghematan ruang apa pun. Untuk pembatasan tambahan dan informasi selengkapnya tentang kolom jarang, lihat Menggunakan Kolom Jarang.

TAMBAHKAN MASKED DENGAN ( FUNCTION = ' mask_function ')

Berlaku untuk: SQL Server (SQL Server 2016 (13.x) dan yang lebih baru) dan Database Azure SQL.

Menentukan masker data dinamis. mask_function adalah nama fungsi masking dengan parameter yang sesuai. Tiga fungsi tersedia:

  • default()
  • email()
  • parsial()
  • random()

Memerlukan izin UBAH MASK APA PUN.

Untuk menjatuhkan masker, gunakan DROP MASKED. Untuk parameter fungsi, lihat Masking Data Dinamis.

Menambahkan dan menghilangkan masker memerlukan izin UBAH MASK APA PUN .

WITH ( ONLINE = ON | OFF) <sebagaimana berlaku untuk mengubah kolom>

Berlaku untuk: SQL Server (SQL Server 2016 (13.x) dan yang lebih baru) dan Database Azure SQL.

Memungkinkan banyak tindakan ubah kolom dilakukan saat tabel tetap tersedia. Defaultnya adalah NONAKTIF. Anda dapat menjalankan perubahan kolom secara online untuk perubahan kolom yang terkait dengan jenis data, panjang atau presisi kolom, nullability, sparseness, dan kolase.

Kolom perubahan online memungkinkan pengguna membuat dan autostatistik untuk mereferensikan kolom yang diubah selama durasi operasi ALTER COLUMN, yang memungkinkan kueri berjalan seperti biasa. Di akhir operasi, autostats yang mereferensikan kolom dihilangkan dan statistik yang dibuat pengguna tidak valid. Pengguna harus memperbarui statistik yang dihasilkan pengguna secara manual setelah operasi selesai. Jika kolom adalah bagian dari ekspresi filter untuk statistik atau indeks apa pun, Anda tidak dapat melakukan operasi ubah kolom.

  • Saat operasi ubah kolom online berjalan, semua operasi yang dapat mengambil dependensi pada kolom (indeks, tampilan, dan sebagainya.) memblokir atau gagal dengan kesalahan yang sesuai. Perilaku ini menjamin bahwa kolom perubahan online tidak akan gagal karena dependensi yang diperkenalkan saat operasi sedang berjalan.
  • Mengubah kolom dari NOT NULL ke NULL tidak didukung sebagai operasi online saat kolom yang diubah direferensikan oleh indeks non-kluster.
  • ALTER Online tidak didukung saat kolom direferensikan oleh batasan pemeriksaan dan operasi ALTER membatasi presisi kolom (numerik atau tanggalwaktu).
  • Opsi WAIT_AT_LOW_PRIORITY tidak dapat digunakan dengan kolom ubah online.
  • ALTER COLUMN ... ADD/DROP PERSISTED tidak didukung untuk kolom perubahan online.
  • ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION tidak dipengaruhi oleh kolom perubahan online.
  • Kolom perubahan online tidak mendukung perubahan tabel di mana pelacakan perubahan diaktifkan atau itu adalah penerbit replikasi penggabungan.
  • Kolom perubahan online tidak mendukung perubahan dari atau ke jenis data CLR.
  • Kolom perubahan online tidak mendukung perubahan pada tipe data XML yang memiliki koleksi skema yang berbeda dari koleksi skema saat ini.
  • Kolom perubahan online tidak mengurangi pembatasan kapan kolom dapat diubah. Referensi menurut indeks/statistik, dan sebagainya, dapat menyebabkan perubahan gagal.
  • Kolom perubahan online tidak mendukung perubahan lebih dari satu kolom secara bersamaan.
  • Kolom perubahan online tidak berpengaruh dalam tabel temporal versi sistem. Kolom ALTER tidak dijalankan sebagai online terlepas dari nilai mana yang ditentukan untuk opsi ONLINE.

Kolom perubahan online memiliki persyaratan, pembatasan, dan fungsionalitas yang sama dengan pembangunan ulang indeks online, yang meliputi:

  • Pembangunan ulang indeks online tidak didukung saat tabel berisi kolom LOB atau filestream warisan atau saat tabel memiliki indeks penyimpan kolom. Batasan yang sama berlaku untuk kolom perubahan online.
  • Kolom yang sudah ada yang diubah memerlukan alokasi ruang dua kali lipat, untuk kolom asli dan untuk kolom tersembunyi yang baru dibuat.
  • Strategi penguncian selama operasi online kolom perubahan mengikuti pola penguncian yang sama yang digunakan untuk build indeks online.

DENGAN | PEMERIKSAAN DENGAN NOCHECK

Menentukan apakah data dalam tabel divalidasi atau tidak terhadap batasan FOREIGN KEY atau CHECK yang baru ditambahkan atau diaktifkan kembali. Jika Anda tidak menentukan, WITH CHECK diasumsikan untuk batasan baru, dan WITH NOCHECK diasumsikan untuk batasan yang diaktifkan kembali.

Jika Anda tidak ingin memverifikasi batasan CHECK atau FOREIGN KEY baru terhadap data yang ada, gunakan WITH NOCHECK. Kami tidak menyarankan untuk melakukan ini, kecuali dalam kasus yang jarang terjadi. Batasan baru dievaluasi di semua pembaruan data nanti. Setiap pelanggaran batasan yang ditekan oleh WITH NOCHECK ketika batasan ditambahkan dapat menyebabkan pembaruan di masa mendatang gagal jika mereka memperbarui baris dengan data yang tidak mengikuti batasan. Pengoptimal kueri tidak mempertimbangkan batasan yang ditentukan DENGAN NOCHECK. Batasan tersebut diabaikan sampai diaktifkan kembali dengan menggunakan ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL. Untuk informasi selengkapnya, lihat Menonaktifkan Batasan Kunci Asing dengan Pernyataan INSERT dan UPDATE.

UBAH index_name INDEKS

Menentukan bahwa jumlah wadah untuk index_name akan diubah atau diubah.

Sintaks ALTER TABLE ... ADD/DROP/ALTER INDEX hanya didukung untuk tabel yang dioptimalkan memori.

Penting

Tanpa menggunakan pernyataan ALTER TABLE, pernyataan CREATE INDEX, DROP INDEX, ALTER INDEX, dan PAD_INDEX tidak didukung untuk indeks pada tabel yang dioptimalkan memori.

ADD

Menentukan bahwa satu atau beberapa definisi kolom, definisi kolom komputasi, atau batasan tabel ditambahkan. Atau, kolom yang digunakan sistem untuk penerapan versi sistem ditambahkan. Untuk tabel yang dioptimalkan memori, Anda dapat menambahkan indeks.

Catatan

Kolom baru ditambahkan setelah semua kolom yang ada dalam tabel diubah.

Penting

Tanpa menggunakan pernyataan ALTER TABLE, pernyataan CREATE INDEX, DROP INDEX, ALTER INDEX, dan PAD_INDEX tidak didukung untuk indeks pada tabel yang dioptimalkan memori.

PERIODE UNTUK SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )

Berlaku untuk: SQL Server (SQL Server 2017 (14.x) dan yang lebih baru) dan database Azure SQL.

Menentukan nama kolom yang digunakan sistem untuk merekam periode waktu rekaman valid. Anda dapat menentukan kolom yang sudah ada atau membuat kolom baru sebagai bagian dari argumen TAMBAHKAN PERIODE UNTUK SYSTEM_TIME. Siapkan kolom dengan jenis data datetime2 dan tentukan sebagai NOT NULL. Jika Anda menentukan kolom titik sebagai NULL, hasil kesalahan. Anda dapat menentukan column_constraint dan/atau Menentukan Nilai Default untuk Kolom untuk kolom system_start_time dan system_end_time. Lihat Contoh A dalam contoh Penerapan Versi Sistem berikut yang menunjukkan penggunaan nilai default untuk kolom system_end_time.

Gunakan argumen ini dengan argumen SET SYSTEM_VERSIONING untuk menjadikan tabel yang ada sebagai tabel temporal. Untuk informasi selengkapnya, lihat Tabel Temporal dan Memulai Tabel Temporal di database Azure SQL.

Pada SQL Server 2017 (14.x), pengguna dapat menandai satu atau kedua kolom periode dengan bendera TERSEMBUNYI untuk menyembunyikan kolom ini secara implisit sehingga SELECT * FROM <table_name> tidak mengembalikan nilai untuk kolom. Secara default, kolom titik tidak disembunyikan. Agar dapat digunakan, kolom tersembunyi harus secara eksplisit disertakan dalam semua kueri yang secara langsung mereferensikan tabel temporal.

DROP

Menentukan bahwa satu atau beberapa definisi kolom, definisi kolom komputasi, atau batasan tabel dihilangkan, atau untuk menghilangkan spesifikasi untuk kolom yang digunakan sistem untuk penerapan versi sistem.

Catatan

Kolom yang dihilangkan dalam tabel ledger hanya dihapus sementara. Kolom yang dijatuhkan tetap berada dalam tabel ledger, tetapi ditandai sebagai kolom yang dijatuhkan dengan mengatur sys.tables.dropped_ledger_table ke 1. Tampilan ledger tabel ledger yang dihilangkan juga ditandai sebagai dihilangkan dengan mengatur sys.tables.dropped_ledger_view_column ke 1. Tabel ledger yang dihilangkan, tabel riwayatnya, dan tampilan ledgernya diganti namanya dengan menambahkan awalan (MSSQL_DroppedLedgerTable, MSSQL_DropedLedgerHistory, MSSQL_DroppedLedgerView) dan menambahkan GUID ke nama asli.ing

constraint_name BATASAN

Menentukan bahwa constraint_name dihapus dari tabel. Beberapa batasan dapat dicantumkan.

Anda dapat menentukan nama batasan yang ditentukan pengguna atau yang disediakan sistem dengan mengkueri sys.check_constrainttampilan katalog , sys.default_constraints, sys.key_constraints, dan sys.foreign_keys .

Batasan KUNCI PRIMER tidak dapat dihilangkan jika indeks XML ada pada tabel.

index_name INDEKS

Menentukan bahwa index_name dihapus dari tabel.

Sintaks ALTER TABLE ... ADD/DROP/ALTER INDEX hanya didukung untuk tabel yang dioptimalkan memori.

Penting

Tanpa menggunakan pernyataan ALTER TABLE, pernyataan CREATE INDEX, DROP INDEX, ALTER INDEX, dan PAD_INDEX tidak didukung untuk indeks pada tabel yang dioptimalkan memori.

column_name KOLOM

Menentukan bahwa constraint_name atau column_name dihapus dari tabel. Beberapa kolom dapat dicantumkan.

Kolom tidak dapat dihilangkan saat:

  • Digunakan dalam indeks, baik sebagai kolom kunci atau sebagai INCLUDE
  • Digunakan dalam batasan CHECK, FOREIGN KEY, UNIQUE, atau PRIMARY KEY.
  • Terkait dengan default yang ditentukan dengan kata kunci DEFAULT, atau terikat ke objek default.
  • Terikat pada aturan.

Catatan

Menghilangkan kolom tidak mengklaim kembali ruang disk kolom. Anda mungkin harus mengklaim kembali ruang disk kolom yang dijatuhkan saat ukuran baris tabel mendekati, atau telah melebihi batasnya. Reklamasi ruang dengan membuat indeks berkluster pada tabel atau membangun kembali indeks berkluster yang ada dengan menggunakan ALTER INDEX. Untuk informasi tentang dampak penurunan jenis data LOB, lihat entri blog CSS ini.

PERIODE UNTUK SYSTEM_TIME

Berlaku untuk: SQL Server (SQL Server 2016 (13.x) dan yang lebih baru) dan database Azure SQL.

Menghilangkan spesifikasi untuk kolom yang akan digunakan sistem untuk penerapan versi sistem.

DENGAN <drop_clustered_constraint_option>

Menentukan bahwa satu atau beberapa opsi batasan terkluster yang dihilangkan diatur.

MAXDOP = max_degree_of_parallelism

Berlaku untuk: SQL Server (SQL Server 2008 dan yang lebih baru) dan database Azure SQL.

Mengambil alih tingkat maksimum opsi konfigurasi paralelisme hanya selama durasi operasi. Untuk informasi selengkapnya, lihat Mengonfigurasi tingkat maksimum Opsi Konfigurasi Server paralelisme.

Gunakan opsi MAXDOP untuk membatasi jumlah prosesor yang digunakan dalam eksekusi rencana paralel. Maksimum adalah 64 prosesor.

max_degree_of_parallelism bisa menjadi salah satu nilai berikut:

1
Menekan pembuatan rencana paralel.

>1
Membatasi jumlah maksimum prosesor yang digunakan dalam operasi indeks paralel ke angka yang ditentukan.

0 (default)
Menggunakan jumlah prosesor aktual atau lebih sedikit berdasarkan beban kerja sistem saat ini.

Untuk informasi selengkapnya, lihat Mengonfigurasi Operasi Indeks Paralel.

Catatan

Operasi indeks paralel tidak tersedia di setiap edisi SQL Server. Untuk informasi selengkapnya, lihat Edisi dan Fitur yang Didukung untuk SQL Server 2016, dan Edisi dan Fitur yang Didukung untuk SQL Server 2017.

{ ON | ONLINE =NONAKTIF } <berlaku untuk drop_clustered_constraint_option>

Menentukan apakah tabel yang mendasari dan indeks terkait tersedia untuk kueri dan modifikasi data selama operasi indeks. Defaultnya adalah NONAKTIF. Anda dapat menjalankan REBUILD sebagai operasi ONLINE.

AKTIF
Kunci tabel jangka panjang tidak ditahan selama durasi operasi indeks. Selama fase utama operasi indeks, hanya kunci Berbagi Niat (IS) yang ditahan pada tabel sumber. Perilaku ini memungkinkan kueri atau pembaruan ke tabel dan indeks yang mendasarinya untuk melanjutkan. Pada awal operasi, kunci Bersama (S) ditahan pada objek sumber untuk waktu yang singkat. Pada akhir operasi, untuk waktu yang singkat, kunci S (Bersama) diperoleh pada sumber jika indeks non-kluster sedang dibuat. Atau, kunci SCH-M (Modifikasi Skema) diperoleh ketika indeks berkluster dibuat atau dihilangkan secara online dan ketika indeks terkluster atau non-kluster sedang dibangun kembali. ONLINE tidak dapat diatur ke AKTIF saat indeks sedang dibuat pada tabel sementara lokal. Hanya operasi pembangunan ulang tumpukan berulir tunggal yang diizinkan.

Untuk menjalankan DDL untuk SWITCH atau pembangunan ulang indeks online, semua transaksi pemblokiran aktif yang berjalan pada tabel tertentu harus diselesaikan. Saat mengeksekusi, operasi SWITCH atau pembangunan kembali mencegah transaksi baru dimulai dan mungkin secara signifikan memengaruhi throughput beban kerja dan menunda akses sementara ke tabel yang mendasar.

TIDAK AKTIF
Kunci tabel berlaku selama durasi operasi indeks. Operasi indeks offline yang membuat, membangun kembali, atau menghilangkan indeks berkluster, atau membangun kembali atau menghilangkan indeks non-kluster, memperoleh kunci Modifikasi Skema (Sch-M) pada tabel. Kunci ini mencegah semua akses pengguna ke tabel yang mendasar selama durasi operasi. Operasi indeks offline yang membuat indeks nonkluster memperoleh kunci Bersama (S) pada tabel. Kunci ini mencegah pembaruan pada tabel yang mendasar tetapi memungkinkan operasi baca, seperti pernyataan SELECT. Operasi pembangunan kembali tumpukan multi-utas diizinkan.

Untuk informasi selengkapnya, lihat Cara Kerja Operasi Indeks Online.

Catatan

Operasi indeks online tidak tersedia di setiap edisi SQL Server. Untuk informasi selengkapnya, lihat Edisi dan Fitur yang Didukung untuk SQL Server 2016, dan Edisi dan Fitur yang Didukung untuk SQL Server 2017.

PINDAH KE { partition_scheme_name(column_name [ ,... n ] ) | grup file | "default" }

Berlaku untuk: SQL Server (SQL Server 2008 dan yang lebih baru) dan Database Azure SQL.

Menentukan lokasi untuk memindahkan baris data yang saat ini berada di tingkat daun indeks berkluster. Tabel dipindahkan ke lokasi baru. Opsi ini hanya berlaku untuk batasan yang membuat indeks berkluster.

Catatan

Dalam konteks ini, default bukan kata kunci. Ini adalah pengidentifikasi untuk grup file default dan harus dibatasi, seperti dalam MOVE TO "default" atau MOVE TO [default]. Jika "default" ditentukan, opsi QUOTED_IDENTIFIER harus AKTIF untuk sesi saat ini. Ini adalah pengaturan default. Untuk informasi selengkapnya, lihat MENGATUR QUOTED_IDENTIFIER.

{ CHECK | BATASAN NOCHECK }

Menentukan bahwa constraint_name diaktifkan atau dinonaktifkan. Opsi ini hanya dapat digunakan dengan batasan FOREIGN KEY dan CHECK. Ketika NOCHECK ditentukan, batasan dinonaktifkan dan penyisipan atau pembaruan di masa mendatang ke kolom tidak divalidasi terhadap kondisi batasan. BATASAN DEFAULT, KUNCI PRIMER, dan UNIK tidak dapat dinonaktifkan.

SEMUA
Menentukan bahwa semua batasan dinonaktifkan dengan opsi NOCHECK atau diaktifkan dengan opsi CHECK.

{ ENABLE | NONAKTIFKAN } PEMICU

Menentukan bahwa trigger_name diaktifkan atau dinonaktifkan. Saat pemicu dinonaktifkan, pemicu masih ditentukan untuk tabel. Namun, ketika pernyataan INSERT, UPDATE, atau DELETE berjalan terhadap tabel, tindakan dalam pemicu tidak dilakukan sampai pemicu diaktifkan kembali.

SEMUA
Menentukan bahwa semua pemicu dalam tabel diaktifkan atau dinonaktifkan.

trigger_name
Menentukan nama pemicu untuk dinonaktifkan atau diaktifkan.

{ ENABLE | DISABLE } CHANGE_TRACKING

Berlaku untuk: SQL Server (SQL Server 2008 dan yang lebih baru) dan Database Azure SQL.

Menentukan apakah pelacakan perubahan diaktifkan dinonaktifkan untuk tabel. Secara default, pelacakan perubahan dinonaktifkan.

Opsi ini hanya tersedia saat pelacakan perubahan diaktifkan untuk database. Untuk informasi selengkapnya, lihat Opsi UBAH SET DATABASE.

Untuk mengaktifkan pelacakan perubahan, tabel harus memiliki kunci primer.

WITH ( TRACK_COLUMNS_UPDATED = { ON | NONAKTIF } )

Berlaku untuk: SQL Server (SQL Server 2008 dan yang lebih baru) dan Database Azure SQL.

Menentukan apakah Mesin Database melacak, kolom terlacak perubahan mana yang diperbarui. Nilai defaultnya adalah NONAKTIF.

BERALIH [ PARTISI source_partition_number_expression ] KE [ schema_name. ] target_table [ target_partition_number_expression PARTISI ]

Berlaku untuk: SQL Server (SQL Server 2008 dan yang lebih baru) dan Database Azure SQL.

Mengalihkan blok data dengan salah satu cara berikut:

  • Menetapkan ulang semua data tabel sebagai partisi ke tabel partisi yang sudah ada.
  • Mengalihkan partisi dari satu tabel yang dipartisi ke tabel lainnya.
  • Menetapkan ulang semua data dalam satu partisi tabel yang dipartisi ke tabel yang tidak dipartisi yang ada.

Jika tabel adalah tabel yang dipartisi, Anda harus menentukan source_partition_number_expression. Jika target_table dipartisi, Anda harus menentukan target_partition_number_expression. Saat menetapkan ulang data tabel sebagai partisi ke tabel partisi yang sudah ada, atau mengalihkan partisi dari satu tabel yang dipartisi ke tabel lain, partisi target harus ada dan harus kosong.

Saat menetapkan ulang satu data partisi untuk membentuk satu tabel, tabel target harus sudah ada dan harus kosong. Baik tabel sumber atau partisi, dan tabel target atau partisi, harus terletak di grup file yang sama. Indeks yang sesuai, atau partisi indeks, juga harus terletak di grup file yang sama. Banyak pembatasan tambahan berlaku untuk beralih partisi. tabel dan target_table tidak boleh sama. target_table bisa menjadi pengidentifikasi multi-bagian.

Baik source_partition_number_expression maupun target_partition_number_expression adalah ekspresi konstan yang dapat mereferensikan variabel dan fungsi. Ini termasuk variabel jenis yang ditentukan pengguna dan fungsi yang ditentukan pengguna. Mereka tidak dapat mereferensikan ekspresi Transact-SQL.

Tabel yang dipartisi dengan indeks penyimpan kolom berkluster berulah seperti tumpukan yang dipartisi:

  • Kunci primer harus menyertakan kunci partisi.
  • Indeks unik harus menyertakan kunci partisi. Namun, termasuk kunci partisi dengan indeks unik yang ada dapat mengubah keunikannya.
  • Untuk beralih partisi, semua indeks nonkluster harus menyertakan kunci partisi.

Untuk pembatasan SWITCH saat menggunakan replikasi, lihat Mereplikasi Tabel dan Indeks yang Dipartisi.

Indeks penyimpan kolom non-kluster dibangun dalam format baca-saja sebelum SQL Server 2016 dan untuk SQL Database sebelum versi V12. Anda harus membangun kembali indeks penyimpan kolom non-kluster ke format saat ini (yang dapat diperbarui) sebelum operasi PARTISI apa pun dapat dijalankan.

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

Berlaku untuk: SQL Server (SQL Server 2008 dan yang lebih baru). Azure SQL Database tidak mendukung FILESTREAM.

Menentukan tempat data FILESTREAM disimpan.

UBAH TABEL dengan klausa SET FILESTREAM_ON berhasil hanya jika tabel tidak memiliki kolom FILESTREAM. Anda dapat menambahkan kolom FILESTREAM dengan menggunakan pernyataan ALTER TABLE kedua.

Jika Anda menentukan partition_scheme_name, aturan untuk CREATE TABLE berlaku. Pastikan tabel sudah dipartisi untuk data baris, dan skema partisinya menggunakan fungsi dan kolom partisi yang sama dengan skema partisi FILESTREAM.

filestream_filegroup_name menentukan nama grup file FILESTREAM. Grup file harus memiliki satu file yang ditentukan untuk grup file dengan menggunakan pernyataan CREATE DATABASE atau ALTER DATABASE , atau hasil kesalahan.

"default" menentukan grup file FILESTREAM dengan kumpulan properti DEFAULT. Jika tidak ada grup file FILESTREAM, hasil kesalahan.

"NULL" menentukan bahwa semua referensi ke grup file FILESTREAM untuk tabel dihapus. Semua kolom FILESTREAM harus dihilangkan terlebih dahulu. Gunakan SET FILESTREAM_ON = "NULL" untuk menghapus semua data FILESTREAM yang terkait dengan tabel.

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

Berlaku untuk: SQL Server (SQL Server 2016 (13.x) dan yang lebih baru) dan Database Azure SQL.

Menonaktifkan atau mengaktifkan penerapan versi sistem tabel. Untuk mengaktifkan penerapan versi sistem tabel, sistem memverifikasi bahwa jenis data, batasan nullability, dan persyaratan batasan kunci primer untuk penerapan versi sistem terpenuhi. Sistem akan merekam riwayat setiap rekaman dalam tabel versi sistem dalam tabel riwayat terpisah. HISTORY_TABLE Jika argumen tidak digunakan, nama tabel riwayat ini akan menjadi MSSQL_TemporalHistoryFor<primary_table_object_id>. Jika tabel riwayat tidak ada, sistem menghasilkan tabel riwayat baru yang cocok dengan skema tabel saat ini, membuat tautan antara dua tabel, dan memungkinkan sistem merekam riwayat setiap rekaman dalam tabel saat ini dalam tabel riwayat. Jika Anda menggunakan argumen HISTORY_TABLE untuk membuat tautan ke dan menggunakan tabel riwayat yang ada, sistem membuat tautan antara tabel saat ini dan tabel yang ditentukan. Saat membuat tautan ke tabel riwayat yang sudah ada, Anda bisa memilih untuk melakukan pemeriksaan konsistensi data. Pemeriksaan konsistensi data ini memastikan bahwa rekaman yang ada tidak tumpang tindih. Menjalankan pemeriksaan konsistensi data adalah default. SYSTEM_VERSIONING = ON Gunakan argumen pada tabel yang ditentukan dengan PERIOD FOR SYSTEM_TIME klausa untuk menjadikan tabel yang ada sebagai tabel temporal. Untuk informasi selengkapnya, lihat Tabel Temporal.

HISTORY_RETENTION_PERIOD = { INFINITE | number {DAY | DAYS | | MINGGU | MINGGU | BULAN | BULAN | TAHUN TAHUN} }

Berlaku untuk: SQL Server 2017 (14.x) dan database Azure SQL.

Menentukan retensi terbatas atau tak terbatas untuk data historis dalam tabel temporal. Jika dihilangkan, retensi tak terbatas diasumsikan.

DATA_DELETION

Berlaku untuk:hanya Azure SQL Edge

Mengaktifkan pembersihan berbasis kebijakan penyimpanan data lama atau lama dari tabel dalam database. Untuk informasi selengkapnya, lihat Mengaktifkan dan Menonaktifkan Retensi Data. Parameter berikut harus ditentukan agar retensi data diaktifkan.

FILTER_COLUMN = { column_name }
Menentukan kolom, yang harus digunakan untuk menentukan apakah baris dalam tabel sudah usang atau tidak. Jenis data berikut diperbolehkan untuk kolom filter.

  • Tanggal
  • DateTime
  • DateTime2
  • SmallDateTime
  • DateTimeOffset

RETENTION_PERIOD = { INFINITE | number {DAY | DAYS | | MINGGU | MINGGU | BULAN | BULAN | TAHUN TAHUN }}
Menentukan kebijakan periode retensi untuk tabel. Periode retensi ditentukan sebagai kombinasi dari nilai bilangan bulat positif dan unit bagian tanggal.

SET ( LOCK_ESCALATION = { auto | | TABEL DISABLE } )

Berlaku untuk: SQL Server (SQL Server 2008 dan yang lebih baru) dan Database Azure SQL.

Menentukan metode eskalasi kunci yang diizinkan untuk tabel.

AUTO
Opsi ini memungkinkan SQL Server Mesin Database untuk memilih granularitas eskalasi kunci yang sesuai untuk skema tabel.

  • Jika tabel dipartisi, eskalasi kunci akan diizinkan ke granularitas tumpukan atau pohon B (HoBT). Dengan kata lain, eskalasi akan diizinkan ke tingkat partisi. Setelah kunci di eskalasi ke tingkat HoBT, kunci tidak akan di eskalasi nanti ke granularitas TABLE.
  • Jika tabel tidak dipartisi, eskalasi kunci dilakukan pada granularitas TABLE.

TABEL
Eskalasi kunci dilakukan pada granularitas tingkat tabel apakah tabel dipartisi atau tidak dipartisi. TABLE adalah nilai default.

MENONAKTIFKAN
Mencegah eskalasi kunci dalam banyak kasus. Kunci tingkat tabel tidak sepenuhnya dilarang. Misalnya, saat Anda memindai tabel yang tidak memiliki indeks berkluster di bawah tingkat isolasi yang dapat diserialisasikan, Mesin Database harus mengambil kunci tabel untuk melindungi integritas data.

MEMBANGUN

Gunakan sintaks REBUILD WITH untuk membangun kembali seluruh tabel termasuk semua partisi dalam tabel yang dipartisi. Jika tabel memiliki indeks berkluster, opsi BANGUN ULANG membangun kembali indeks berkluster. PEMBANGUNAN ULANG dapat dijalankan sebagai operasi ONLINE.

Gunakan sintaks REBUILD PARTITION untuk membangun kembali partisi tunggal dalam tabel yang dipartisi.

PARTISI = SEMUA

Berlaku untuk: SQL Server (SQL Server 2008 dan yang lebih baru) dan Database Azure SQL.

Membangun kembali semua partisi saat mengubah pengaturan kompresi partisi.

BANGUN KEMBALI DENGAN ( <rebuild_option> )

Semua opsi berlaku untuk tabel dengan indeks berkluster. Jika tabel tidak memiliki indeks berkluster, struktur timbunan hanya dipengaruhi oleh beberapa opsi.

Ketika pengaturan kompresi tertentu tidak ditentukan dengan operasi PEMBANGUNAN ULANG, pengaturan pemadatan saat ini untuk partisi digunakan. Untuk mengembalikan pengaturan saat ini, kueri data_compression kolom dalam sys.partitions tampilan katalog.

Untuk deskripsi lengkap tentang opsi pembangunan ulang, lihat ALTER TABLE index_option.

DATA_COMPRESSION

Berlaku untuk: SQL Server (SQL Server 2008 dan yang lebih baru) dan Database Azure SQL.

Menentukan opsi pemadatan data untuk tabel, nomor partisi, atau rentang partisi yang ditentukan. Opsinya meliputi:

Tabel NONE atau partisi yang ditentukan tidak dikompresi. Opsi ini tidak berlaku untuk tabel penyimpan kolom.

Tabel ROW atau partisi tertentu dikompresi dengan menggunakan pemadatan baris. Opsi ini tidak berlaku untuk tabel penyimpan kolom.

Tabel HALAMAN atau partisi tertentu dikompresi dengan menggunakan pemadatan halaman. Opsi ini tidak berlaku untuk tabel penyimpan kolom.

PENYIMPAN KOLOM
Berlaku untuk: SQL Server (SQL Server 2014 (12.x) dan yang lebih baru) dan Database Azure SQL.

Hanya berlaku untuk tabel penyimpan kolom. COLUMNSTORE menentukan untuk mendekompresi partisi yang dikompresi dengan opsi COLUMNSTORE_ARCHIVE. Saat data dipulihkan, data terus dikompresi dengan kompresi penyimpan kolom yang digunakan untuk semua tabel penyimpan kolom.

COLUMNSTORE_ARCHIVE
Berlaku untuk: SQL Server (SQL Server 2014 (12.x) dan yang lebih baru) dan Database Azure SQL.

Hanya berlaku untuk tabel penyimpan kolom, yang merupakan tabel yang disimpan dengan indeks penyimpan kolom berkluster. COLUMNSTORE_ARCHIVE akan lebih memadatkan partisi yang ditentukan ke ukuran yang lebih kecil. Gunakan opsi ini untuk arsip atau situasi lain yang membutuhkan lebih sedikit penyimpanan dan dapat membayar lebih banyak waktu untuk penyimpanan dan pengambilan.

Untuk membangun kembali beberapa partisi secara bersamaan, lihat index_option. Jika tabel tidak memiliki indeks berkluster, mengubah kompresi data membangun kembali tumpukan dan indeks non-kluster. Untuk informasi selengkapnya tentang pemadatan, lihat Kompresi Data.

XML_COMPRESSION

Berlaku untuk: pratinjau SQL Server 2022 (16.x) dan yang lebih baru, dan Pratinjau Database Azure SQL.

Menentukan opsi pemadatan XML untuk kolom tipe data xml apa pun dalam tabel. Opsinya meliputi:

AKTIF
Kolom yang menggunakan tipe data xml dikompresi.

TIDAK AKTIF
Kolom yang menggunakan tipe data xml tidak dikompresi.

{ ON | ONLINE =NONAKTIF } <berlaku untuk single_partition_rebuild_option>

Menentukan apakah satu partisi tabel yang mendasari dan indeks terkait tersedia untuk kueri dan modifikasi data selama operasi indeks. Defaultnya adalah NONAKTIF. Anda dapat menjalankan REBUILD sebagai operasi ONLINE.

AKTIF
Kunci tabel jangka panjang tidak ditahan selama operasi indeks. S-lock pada tabel diperlukan di awal pembangunan ulang indeks dan kunci Sch-M pada tabel di akhir pembangunan ulang indeks online. Meskipun kedua kunci adalah kunci metadata pendek, kunci Sch-M harus menunggu semua transaksi pemblokiran selesai. Selama waktu tunggu, kunci Sch-M memblokir semua transaksi lain yang menunggu di belakang kunci ini saat mengakses tabel yang sama.

Catatan

Pembangunan ulang indeks online dapat mengatur opsi yang low_priority_lock_wait dijelaskan nanti di bagian ini.

TIDAK AKTIF
Kunci tabel diterapkan selama durasi operasi indeks. Ini mencegah semua akses pengguna ke tabel yang mendasar selama durasi operasi.

column_set_name COLUMN_SET XML UNTUK ALL_SPARSE_COLUMNS

Berlaku untuk: SQL Server (SQL Server 2008 dan yang lebih baru) dan database Azure SQL.

Nama kumpulan kolom. Kumpulan kolom adalah representasi XML yang tidak ditata yang menggabungkan semua kolom jarang tabel ke dalam output terstruktur. Kumpulan kolom tidak dapat ditambahkan ke tabel yang berisi kolom jarang. Untuk informasi selengkapnya tentang kumpulan kolom, lihat Menggunakan Kumpulan Kolom.

{ ENABLE | DISABLE } FILETABLE_NAMESPACE

Berlaku untuk: SQL Server (SQL Server 2012 (11.x) dan yang lebih baru).

Mengaktifkan atau menonaktifkan batasan yang ditentukan sistem pada FileTable. Hanya dapat digunakan dengan FileTable.

SET ( FILETABLE_DIRECTORY = directory_name )

Berlaku untuk: SQL Server (SQL Server 2012 (11.x) dan yang lebih baru). Azure SQL Database tidak mendukung FILETABLE.

Menentukan nama direktori FileTable yang kompatibel dengan Windows. Nama ini harus unik di antara semua nama direktori FileTable dalam database. Perbandingan keunikan tidak peka huruf besar/kecil, meskipun pengaturan kolase SQL. Hanya dapat digunakan dengan FileTable.

REMOTE_DATA_ARCHIVE

Berlaku untuk: SQL Server (SQL Server 2017 (14.x) dan yang lebih baru).

Mengaktifkan atau menonaktifkan Stretch Database untuk tabel. Untuk informasi selengkapnya, lihat Stretch Database.

Penting

Stretch Database tidak digunakan lagi dalam Pratinjau SQL Server 2022 (16.x). Fitur ini akan dihapus dalam versi Microsoft SQL Server yang akan datang. Hindari menggunakan fitur ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan fitur ini.

Mengaktifkan Stretch Database untuk tabel

Saat Anda mengaktifkan Stretch untuk tabel dengan menentukan ON, Anda juga harus menentukan MIGRATION_STATE = OUTBOUND untuk segera mulai memigrasikan data, atau MIGRATION_STATE = PAUSED untuk menunda migrasi data. Nilai defaultnya adalah MIGRATION_STATE = OUTBOUND. Untuk informasi selengkapnya tentang mengaktifkan Stretch untuk tabel, lihat Mengaktifkan Stretch Database untuk tabel.

Prasyarat. Sebelum mengaktifkan Stretch untuk tabel, Anda harus mengaktifkan Stretch di server dan di database. Untuk informasi selengkapnya, lihat Mengaktifkan Stretch Database untuk database.

Izin. Mengaktifkan Stretch untuk database atau tabel memerlukan izin db_owner . Mengaktifkan Stretch untuk tabel juga memerlukan izin ALTER pada tabel.

Menonaktifkan Stretch Database untuk tabel

Saat Menonaktifkan Stretch untuk tabel, Anda memiliki dua opsi untuk data jarak jauh yang sudah dimigrasikan ke Azure. Untuk informasi selengkapnya, lihat Menonaktifkan Stretch Database dan membawa kembali data jarak jauh.

  • Untuk menonaktifkan Stretch untuk tabel dan menyalin data jarak jauh untuk tabel dari Azure kembali ke SQL Server, jalankan perintah berikut. Perintah ini tidak dapat dibatalkan.

    ALTER TABLE <table_name>
       SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) ) ;
    

Operasi ini menimbulkan biaya transfer data, dan tidak dapat dibatalkan. Untuk informasi selengkapnya, lihat Detail Harga Transfer Data.

Setelah semua data jarak jauh disalin dari Azure kembali ke SQL Server, Stretch dinonaktifkan untuk tabel.

  • Untuk menonaktifkan Stretch untuk tabel dan mengabaikan data jarak jauh, jalankan perintah berikut.

    ALTER TABLE <table_name>
       SET ( REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED ) ) ;
    

Setelah Anda menonaktifkan Stretch Database untuk tabel, migrasi data berhenti dan hasil kueri tidak lagi menyertakan hasil dari tabel jarak jauh.

Menonaktifkan Stretch tidak menghapus tabel jarak jauh. Jika Anda ingin menghapus tabel jarak jauh, Anda menghilangkannya dengan menggunakan portal Azure.

[ FILTER_PREDICATE = { | null predikat } ]

Berlaku untuk: SQL Server (SQL Server 2017 (14.x) dan yang lebih baru).

Secara opsional menentukan predikat filter untuk memilih baris yang akan dimigrasikan dari tabel yang berisi data historis dan saat ini. Predikat harus memanggil fungsi bernilai tabel sebaris deterministik. Untuk informasi selengkapnya, lihat Mengaktifkan Stretch Database untuk tabel dan Memilih baris untuk dimigrasikan dengan menggunakan fungsi filter - Stretch Database.

Penting

Jika Anda memberikan predikat filter yang berkinerja buruk, migrasi data juga berkinerja buruk. Stretch Database menerapkan predikat filter ke tabel dengan menggunakan operator CROSS APPLY.

Jika Anda tidak menentukan predikat filter, seluruh tabel akan dimigrasikan.

Saat Menentukan predikat filter, Anda juga harus menentukan MIGRATION_STATE.

MIGRATION_STATE = { | KELUAR | MASUK DIJEDA }

Berlaku untuk: SQL Server (SQL Server 2017 (14.x) dan yang lebih baru).

WAIT_AT_LOW_PRIORITY

Berlaku untuk: SQL Server (SQL Server 2014 (12.x) dan yang lebih baru) dan database Azure SQL.

Pembangunan ulang indeks online harus menunggu operasi pemblokiran pada tabel ini. WAIT_AT_LOW_PRIORITY menunjukkan bahwa operasi pembangunan ulang indeks online menunggu kunci berprioritas rendah, memungkinkan operasi lain untuk dilaksanakan saat operasi build indeks online sedang menunggu. Menghilangkan opsi TUNGGU DI PRIORITAS RENDAH sama WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)dengan .

MAX_DURATION = waktu [MENIT ]
Berlaku untuk: SQL Server (SQL Server 2014 (12.x) dan yang lebih baru) dan database Azure SQL.

Waktu tunggu, yang merupakan nilai bilangan bulat yang ditentukan dalam hitungan menit, bahwa switch atau kunci pembangunan ulang indeks online menunggu dengan prioritas rendah saat menjalankan perintah DDL. Jika operasi diblokir untuk waktu MAX_DURATION , salah satu tindakan ABORT_AFTER_WAIT akan berjalan. MAX_DURATION waktu selalu dalam hitungan menit, dan Anda dapat menghilangkan kata MINUTES.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

Berlaku untuk: SQL Server (SQL Server 2014 (12.x) dan yang lebih baru) dan database Azure SQL.

TIDAK ADA
Lanjutkan menunggu kunci dengan prioritas normal (reguler).

DIRI
Keluar dari switch atau indeks online membangun kembali operasi DDL saat ini sedang dijalankan tanpa mengambil tindakan apa pun.

BLOCKER
Matikan semua transaksi pengguna yang saat ini memblokir operasi SWITCH atau DDL pembangunan ulang indeks online sehingga operasi dapat dilanjutkan.

Memerlukan izin UBAH KONEKSI APA PUN .

JIKA ADA

Berlaku untuk: SQL Server (SQL Server 2016 (13.x) dan yang lebih baru) dan database Azure SQL.

Secara kondisional menghilangkan kolom atau batasan hanya jika sudah ada.

DAPAT DIUMUMKAN KEMBALI = { ON | OFF}

Berlaku untuk: pratinjau SQL Server 2022 (16.x) dan yang lebih baru.

Menentukan apakah ALTER TABLE ADD CONSTRAINT operasi dapat diulang. Operasi tambahkan batasan tabel dapat dilanjutkan saat ON. Operasi tambahkan batasan tabel tidak dapat dilanjutkan saat OFF. Defaultnya adalah OFF. Opsi RESUMABLE ini dapat digunakan sebagai bagian dari index_option ALTER TABLE dalam table_constraint ALTER TABLE.

MAX_DURATION saat digunakan dengan RESUMABLE = ON (memerlukan ONLINE = ON) menunjukkan waktu (nilai bilangan bulat yang ditentukan dalam menit) bahwa operasi batasan penambahan online yang dapat dilanjutkan dijalankan sebelum dijeda. Jika tidak ditentukan, operasi berlanjut hingga selesai.

Untuk informasi selengkapnya tentang mengaktifkan dan menggunakan operasi yang dapat dilanjutkan ALTER TABLE ADD CONSTRAINT , lihat Tabel yang dapat dilanjutkan menambahkan batasan.

Keterangan

Untuk menambahkan baris data baru, gunakan INSERT. Untuk menghapus baris data, gunakan DELETE atau TRUNCATE TABLE. Untuk mengubah nilai di baris yang sudah ada, gunakan UPDATE.

Jika ada rencana eksekusi dalam cache prosedur yang mereferensikan tabel, ALTER TABLE menandainya untuk dikommpilasikan ulang pada eksekusi berikutnya.

Mengubah ukuran kolom

Anda dapat mengubah panjang, presisi, atau skala kolom dengan menentukan ukuran baru untuk jenis data kolom. Gunakan klausa ALTER COLUMN. Jika data ada di kolom, ukuran baru tidak boleh lebih kecil dari ukuran maksimum data. Selain itu, Anda tidak dapat menentukan kolom dalam indeks, kecuali kolom adalah jenis data varchar, nvarchar, atau varbinary dan indeks bukan hasil dari batasan KUNCI PRIMER. Lihat contoh di bagian pendek berjudul Mengubah Definisi Kolom.

Kunci dan UBAH TABEL

Perubahan yang Anda tentukan dalam ALTER TABLE segera diimplementasikan. Jika perubahan memerlukan modifikasi baris dalam tabel, UBAH TABEL memperbarui baris. ALTER TABLE memperoleh kunci modifikasi skema (SCH-M) pada tabel untuk memastikan bahwa tidak ada koneksi lain yang mereferensikan bahkan metadata untuk tabel selama perubahan, kecuali operasi indeks online yang memerlukan kunci SCH-M pendek di akhir. ALTER TABLE...SWITCH Dalam operasi, kunci diperoleh pada tabel sumber dan target. Modifikasi yang dilakukan pada tabel dicatat dan dapat dipulihkan sepenuhnya. Perubahan yang memengaruhi semua baris dalam tabel besar, seperti menghilangkan kolom atau, pada beberapa edisi SQL Server, menambahkan kolom NOT NULL dengan nilai default, dapat memakan waktu lama untuk menyelesaikan dan menghasilkan banyak rekaman log. Jalankan pernyataan ALTER TABLE ini dengan perhatian yang sama dengan pernyataan INSERT, UPDATE, atau DELETE apa pun yang memengaruhi banyak baris.

Menambahkan kolom NOT NULL sebagai operasi online

Dimulai dengan SQL Server 2012 (11.x) Enterprise Edition, menambahkan kolom NOT NULL dengan nilai default adalah operasi online ketika nilai default adalah konstanta runtime. Ini berarti bahwa operasi selesai hampir seketika meskipun jumlah baris dalam tabel, karena baris yang ada dalam tabel tidak diperbarui selama operasi. Sebaliknya, nilai default hanya disimpan dalam metadata tabel dan nilai dicari, sesuai kebutuhan, dalam kueri yang mengakses baris ini. Perilaku ini otomatis. Tidak ada sintaks tambahan yang diperlukan untuk mengimplementasikan operasi online di luar sintaks ADD COLUMN. Konstanta runtime adalah ekspresi yang menghasilkan nilai yang sama pada runtime untuk setiap baris dalam tabel meskipun determinismenya. Misalnya, ekspresi konstanta "Data sementara saya", atau fungsi sistem GETUTCDATETIME() adalah konstanta runtime. Sebaliknya, fungsi atau NEWSEQUENTIALID() bukan konstanta NEWID() runtime, karena nilai unik dihasilkan untuk setiap baris dalam tabel. Menambahkan kolom NOT NULL dengan nilai default yang bukan konstanta runtime selalu dijalankan secara offline dan kunci eksklusif (SCH-M) diperoleh selama durasi operasi.

Meskipun baris yang ada mereferensikan nilai yang disimpan dalam metadata, nilai default disimpan pada baris untuk baris baru apa pun yang disisipkan dan tidak menentukan nilai lain untuk kolom tersebut. Nilai default yang disimpan dalam metadata berpindah ke baris yang sudah ada saat baris diperbarui (bahkan jika kolom aktual tidak ditentukan dalam pernyataan UPDATE), atau jika tabel atau indeks berkluster dibangun kembali.

Kolom jenis varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography, atau CLR UDTS, tidak dapat ditambahkan dalam operasi online. Kolom tidak dapat ditambahkan secara online jika melakukannya menyebabkan ukuran baris maksimum yang mungkin melebihi batas 8.060 byte. Kolom ditambahkan sebagai operasi offline dalam kasus ini.

Eksekusi rencana paralel

Di Microsoft SQL Server 2012 Enterprise dan yang lebih tinggi, jumlah prosesor yang digunakan untuk menjalankan satu pernyataan BATASAN ALTER TABLE ADD (berbasis indeks) atau DROP (indeks berkluster) ditentukan oleh opsi konfigurasi paralelisme tingkat maksimum dan beban kerja saat ini. Jika Mesin Database mendeteksi bahwa sistem sibuk, tingkat paralelisme operasi secara otomatis berkurang sebelum eksekusi pernyataan dimulai. Anda dapat mengonfigurasi jumlah prosesor yang digunakan untuk menjalankan pernyataan secara manual dengan menentukan opsi MAXDOP. Untuk informasi selengkapnya, lihat Mengonfigurasi tingkat maksimum Opsi Konfigurasi Server paralelisme.

Tabel berpartisi

Selain melakukan operasi SWITCH yang melibatkan tabel yang dipartisi, gunakan ALTER TABLE untuk mengubah status kolom, batasan, dan pemicu tabel yang dipartisi seperti yang digunakan untuk tabel yang tidak dipartisi. Namun, pernyataan ini tidak dapat digunakan untuk mengubah cara tabel itu sendiri dipartisi. Untuk mempartisi ulang tabel yang dipartisi, gunakan ALTER PARTITION SCHEME dan ALTER PARTITION FUNCTION. Selain itu, Anda tidak dapat mengubah jenis data kolom tabel yang dipartisi.

Pembatasan pada tabel dengan tampilan terikat skema

Pembatasan yang berlaku untuk pernyataan ALTER TABLE pada tabel dengan tampilan terikat skema sama dengan pembatasan yang saat ini diterapkan saat memodifikasi tabel dengan indeks sederhana. Menambahkan kolom diperbolehkan. Namun, menghapus atau mengubah kolom yang berpartisipasi dalam tampilan terikat skema apa pun tidak diizinkan. Jika pernyataan ALTER TABLE memerlukan perubahan kolom yang digunakan dalam tampilan terikat skema, ALTER TABLE gagal dan Mesin Database memunculkan pesan kesalahan. Untuk informasi selengkapnya tentang pengikatan skema dan tampilan terindeks, lihat CREATE VIEW.

Menambahkan atau menghapus pemicu pada tabel dasar tidak terpengaruh dengan membuat tampilan terikat skema yang mereferensikan tabel.

Indeks dan UBAH TABEL

Indeks yang dibuat sebagai bagian dari batasan dihilangkan saat batasan dihilangkan. Indeks yang dibuat dengan CREATE INDEX harus dihilangkan dengan DROP INDEX. Gunakan pernyataan ALTER INDEX untuk membangun kembali bagian indeks dari definisi batasan; batasan tidak harus dihilangkan dan ditambahkan lagi dengan ALTER TABLE.

Semua indeks dan batasan berdasarkan kolom harus dihapus sebelum kolom dapat dihapus.

Saat Anda menghapus batasan yang membuat indeks berkluster, baris data yang disimpan di tingkat daun indeks berkluster disimpan dalam tabel non-kluster. Anda dapat menghilangkan indeks berkluster dan memindahkan tabel yang dihasilkan ke grup file atau skema partisi lain dalam satu transaksi dengan menentukan opsi PINDAHKAN KE. Opsi PINDAHKAN KE memiliki batasan berikut:

  • MOVE TO tidak valid untuk tampilan terindeks atau indeks non-kluster.
  • Skema partisi atau grup file harus sudah ada.
  • Jika MOVE TO tidak ditentukan, tabel terletak dalam skema partisi atau grup file yang sama seperti yang didefinisikan untuk indeks berkluster.

Saat Anda menghilangkan indeks berkluster, tentukan ONLINE **=** ON opsi sehingga transaksi DROP INDEX tidak memblokir kueri dan modifikasi pada data yang mendasarinya dan indeks non-kluster terkait.

ONLINE = ON memiliki batasan berikut:

  • ONLINE = ON tidak valid untuk indeks berkluster yang juga dinonaktifkan. Indeks yang dinonaktifkan harus dihilangkan dengan menggunakan ONLINE = OFF.
  • Hanya satu indeks pada satu waktu yang dapat dihilangkan.
  • ONLINE = ON tidak valid untuk tampilan terindeks, indeks non-kluster, atau indeks pada tabel sementara lokal.
  • ONLINE = ON tidak valid untuk indeks penyimpan kolom.

Ruang disk sementara yang sama dengan ukuran indeks berkluster yang ada diperlukan untuk menghilangkan indeks berkluster. Ruang tambahan ini dirilis segera setelah operasi selesai.

Catatan

Opsi yang tercantum di bawah <drop_clustered_constraint_option> berlaku untuk indeks berkluster pada tabel dan tidak dapat diterapkan ke indeks berkluster pada tampilan atau indeks non-kluster.

Mereplikasi perubahan skema

Saat Anda menjalankan ALTER TABLE pada tabel yang diterbitkan di SQL Server Publisher, secara default, perubahan tersebut disebarluaskan ke semua Pelanggan SQL Server. Fungsionalitas ini memiliki beberapa batasan. Anda dapat menonaktifkannya. Untuk informasi selengkapnya, lihat Membuat Perubahan Skema pada Database Publikasi.

Pemadatan data

Tabel sistem tidak dapat diaktifkan untuk pemadatan. Jika tabel adalah tumpukan, operasi pembangunan ulang untuk mode ONLINE akan menjadi utas tunggal. Gunakan mode OFFLINE untuk operasi pembangunan kembali tumpukan multi-utas. Untuk informasi selengkapnya tentang pemadatan data, lihat Pemadatan Data.

Untuk mengevaluasi bagaimana mengubah status pemadatan akan memengaruhi tabel, indeks, atau partisi, gunakan prosedur tersimpan sistem sp_estimate_data_compression_savings .

Pembatasan berikut berlaku untuk tabel yang dipartisi:

  • Anda tidak dapat mengubah pengaturan kompresi partisi tunggal jika tabel memiliki indeks yang tidak ditandatangani.
  • ... ALTER TABLE <table> REBUILD PARTITION sintaks membangun kembali partisi yang ditentukan.
  • ... ALTER TABLE <table> REBUILD WITH sintaks membangun kembali semua partisi.

Menghilangkan kolom NTEXT

Saat menghilangkan kolom menggunakan jenis data NTEXT yang tidak digunakan lagi, pembersihan data yang dihapus terjadi sebagai operasi berseri pada semua baris. Pembersihan dapat membutuhkan banyak waktu. Saat menghilangkan kolom NTEXT dalam tabel dengan banyak baris, perbarui kolom NTEXT ke nilai NULL terlebih dahulu, lalu letakkan kolom. Anda dapat menjalankan opsi ini dengan operasi paralel dan membuatnya jauh lebih cepat.

PEMBANGUNAN ULANG indeks online

Untuk menjalankan pernyataan DDL untuk pembangunan ulang indeks online, semua transaksi pemblokiran aktif yang berjalan pada tabel tertentu harus diselesaikan. Ketika pembangunan ulang indeks online diluncurkan, indeks ini memblokir semua transaksi baru yang siap untuk mulai berjalan pada tabel ini. Meskipun durasi kunci untuk pembangunan ulang indeks online singkat, menunggu semua transaksi terbuka pada tabel tertentu selesai dan memblokir transaksi baru untuk memulai, mungkin secara signifikan memengaruhi throughput. Ini dapat menyebabkan beban kerja lambat atau waktu habis dan secara signifikan membatasi akses ke tabel yang mendasar. Opsi WAIT_AT_LOW_PRIORITY memungkinkan DBA mengelola kunci S-lock dan Sch-M yang diperlukan untuk pembangunan ulang indeks online. Dalam ketiga kasus: NONE, SELF, dan BLOCKERS, jika selama waktu tunggu ( (MAX_DURATION =n [minutes]) ) tidak ada aktivitas pemblokiran, pembangunan ulang indeks online segera dijalankan tanpa menunggu dan pernyataan DDL selesai.

Dukungan kompatibilitas

Pernyataan ALTER TABLE hanya mendukung nama tabel dua bagian (schema.object). Dalam SQL Server, menentukan nama tabel menggunakan format berikut gagal pada waktu kompilasi dengan kesalahan 117.

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

Di versi sebelumnya, menentukan format server.database.schema.table yang mengembalikan kesalahan 4902. Menentukan format .database.schema.table atau format ..schema.table berhasil.

Untuk mengatasi masalah, hapus penggunaan awalan empat bagian.

Izin

Memerlukan izin UBAH pada tabel.

Izin ALTER TABLE berlaku untuk kedua tabel yang terlibat dalam pernyataan ALTER TABLE SWITCH. Data apa pun yang dialihkan mewarisi keamanan tabel target.

Jika Anda telah menentukan kolom apa pun dalam pernyataan ALTER TABLE menjadi jenis umum yang ditentukan pengguna runtime bahasa umum (CLR) atau jenis data alias, izin REFERENSI pada jenis tersebut diperlukan.

Menambahkan atau mengubah kolom yang memperbarui baris tabel memerlukan izin UPDATE pada tabel. Misalnya, menambahkan kolom NOT NULL dengan nilai default atau menambahkan kolom identitas saat tabel tidak kosong.

Contoh

Kategori Elemen sintaksis unggulan
Menambahkan kolom dan batasan ADD * PRIMARY KEY dengan opsi indeks * kolom jarang dan kumpulan kolom *
Menghilangkan kolom dan batasan DROP
Mengubah definisi kolom ubah jenis data * ubah ukuran kolom * kolab
Mengubah definisi tabel DATA_COMPRESSION * PARTISI SWITCH * ESKALASI KUNCI * pelacakan perubahan
Menonaktifkan dan mengaktifkan batasan dan pemicu CENTANG * TIDAK ADA PEMERIKSAAN * AKTIFKAN PEMICU * NONAKTIFKAN PEMICU
Operasi online ONLINE
Penerapan versi sistem SYSTEM_VERSIONING

Menambahkan kolom dan batasan

Contoh di bagian ini menunjukkan penambahan kolom dan batasan ke tabel.

J. Menambahkan kolom baru

Contoh berikut menambahkan kolom yang memungkinkan nilai null dan tidak memiliki nilai yang disediakan melalui definisi DEFAULT. Di kolom baru, setiap baris akan memiliki NULL.

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

B. Menambahkan kolom dengan batasan

Contoh berikut menambahkan kolom baru dengan UNIQUE batasan.

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. Menambahkan batasan CHECK yang belum diverifikasi ke kolom yang sudah ada

Contoh berikut menambahkan batasan ke kolom yang sudah ada dalam tabel. Kolom memiliki nilai yang melanggar batasan. Oleh karena itu, WITH NOCHECK digunakan untuk mencegah batasan divalidasi terhadap baris yang ada, dan untuk memungkinkan batasan ditambahkan.

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. Menambahkan batasan DEFAULT ke kolom yang sudah ada

Contoh berikut membuat tabel dengan dua kolom dan menyisipkan nilai ke kolom pertama, dan kolom lainnya tetap NULL. Batasan DEFAULT kemudian ditambahkan ke kolom kedua. Untuk memverifikasi bahwa default diterapkan, nilai lain dimasukkan ke dalam kolom pertama, dan tabel dikueri.

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. Menambahkan beberapa kolom dengan batasan

Contoh berikut menambahkan beberapa kolom dengan batasan yang ditentukan dengan kolom baru. Kolom baru pertama memiliki IDENTITY properti . Setiap baris dalam tabel memiliki nilai inkremental baru di kolom identitas.

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. Menambahkan kolom yang dapat diubah ke null dengan nilai default

Contoh berikut menambahkan kolom nullable dengan DEFAULT definisi, dan menggunakan WITH VALUES untuk menyediakan nilai untuk setiap baris yang ada dalam tabel. Jika WITH VALUES tidak digunakan, setiap baris memiliki nilai NULL di kolom baru.

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. Membuat batasan KUNCI PRIMER dengan opsi kompresi indeks atau data

Contoh berikut membuat batasan PK_TransactionHistoryArchive_TransactionID KUNCI PRIMER dan mengatur opsi FILLFACTOR, , ONLINEdan PAD_INDEX. Indeks berkluster yang dihasilkan akan memiliki nama yang sama dengan batasan.

Berlaku untuk: SQL Server 2008 dan yang lebih baru dan 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

Contoh serupa ini menerapkan pemadatan halaman saat menerapkan kunci primer berkluster.

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

H. Menambahkan kolom jarang

Contoh berikut menunjukkan penambahan dan pengubahan kolom jarang dalam tabel T1. Kode untuk membuat tabel T1 adalah sebagai berikut.

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

Untuk menambahkan kolom C5jarang tambahan , jalankan pernyataan berikut.

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

Untuk mengonversi C4 kolom yang tidak jarang menjadi kolom jarang, jalankan pernyataan berikut.

ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO

Untuk mengonversi C4 kolom jarang menjadi kolom yang tidak jarang, jalankan pernyataan berikut.

ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE ;
GO

i. Menambahkan kumpulan kolom

Contoh berikut menunjukkan penambahan kolom ke tabel T2. Kumpulan kolom tidak dapat ditambahkan ke tabel yang sudah berisi kolom jarang. Kode untuk membuat tabel T2 adalah sebagai berikut.

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

Tiga pernyataan berikut menambahkan kumpulan kolom bernama CS, lalu memodifikasi kolom C2 dan C3 ke 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. Menambahkan kolom terenkripsi

Pernyataan berikut menambahkan kolom terenkripsi bernama 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. Menambahkan kunci primer dengan operasi yang dapat dilanjutkan

Operasi yang dapat dilanjutkan ALTER TABLE untuk menambahkan kunci primer yang diklusterkan pada kolom (a) dengan MAX_DURATION 240 menit.

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

Menghilangkan kolom dan batasan

Contoh di bagian ini menunjukkan menghilangkan kolom dan batasan.

J. Menjatuhkan kolom atau kolom

Contoh pertama memodifikasi tabel untuk menghapus kolom. Contoh kedua menghapus beberapa kolom.

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. Menghilangkan batasan dan kolom

Contoh pertama menghapus UNIQUE batasan dari tabel. Contoh kedua menghapus dua batasan dan satu kolom.

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. Menghilangkan batasan KUNCI PRIMER dalam mode ONLINE

Contoh berikut menghapus batasan KUNCI PRIMER dengan opsi diatur ONLINE ke ON.

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

D. Menambahkan dan menghilangkan batasan KUNCI ASING

Contoh berikut membuat tabel ContactBackup, lalu mengubah tabel, pertama dengan menambahkan batasan yang mereferensikan FOREIGN KEY tabel Person.Person, lalu dengan menghilangkan FOREIGN KEY batasan.

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 ;

Mengubah definisi kolom

J. Mengubah tipe data kolom

Contoh berikut mengubah kolom tabel dari INT menjadi 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. Mengubah ukuran kolom

Contoh berikut meningkatkan ukuran kolom varchar dan presisi dan skala kolom desimal . Karena kolom berisi data, ukuran kolom hanya dapat ditingkatkan. Perhatikan juga bahwa col_a didefinisikan dalam indeks unik. Ukuran col_a masih dapat ditingkatkan karena jenis data adalah varchar dan indeks bukan hasil dari batasan KUNCI PRIMER.

-- 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. Mengubah kolajek kolom

Contoh berikut menunjukkan cara mengubah kolab kolom. Pertama, tabel dibuat tabel dengan kolatasi pengguna default.

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

Selanjutnya, kolatasi kolom C2 diubah menjadi Latin1_General_BIN. Jenis data diperlukan, meskipun tidak diubah.

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

D. Mengenkripsi kolom

Contoh berikut menunjukkan cara mengenkripsi kolom menggunakan Always Encrypted dengan enklave aman.

Pertama, tabel dibuat tanpa kolom terenkripsi.

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

Selanjutnya, kolom 'C2' dienkripsi dengan kunci enkripsi kolom, bernama CEK1, dan enkripsi acak. Agar pernyataan berikut berhasil:

  • Kunci enkripsi kolom harus diaktifkan enklave. Artinya, itu harus dienkripsi dengan kunci master kolom yang memungkinkan komputasi enklave.
  • Instans SQL Server target harus mendukung Always Encrypted dengan enklave aman.
  • Pernyataan harus dikeluarkan melalui koneksi yang disiapkan untuk Always Encrypted dengan enklave aman, dan menggunakan driver klien yang didukung.
  • Aplikasi panggilan harus memiliki akses ke kunci master kolom, melindungi 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

Mengubah definisi tabel

Contoh di bagian ini menunjukkan cara mengubah definisi tabel.

J. Memodifikasi tabel untuk mengubah pemadatan

Contoh berikut mengubah pemadatan tabel yang tidak dipartisi. Indeks timbunan atau berkluster akan dibangun kembali. Jika tabel adalah tumpukan, semua indeks yang tidak berkluster akan dibangun kembali.

ALTER TABLE T1
REBUILD WITH (DATA_COMPRESSION = PAGE) ;

Contoh berikut mengubah pemadatan tabel yang dipartisi. Sintaksis REBUILD PARTITION = 1 hanya menyebabkan nomor 1 partisi dibangun kembali.

Berlaku untuk: SQL Server 2008 dan yang lebih baru dan Azure SQL Database.

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

Operasi yang sama menggunakan sintaks alternatif berikut menyebabkan semua partisi dalam tabel dibangun kembali.

Berlaku untuk: SQL Server 2008 dan yang lebih baru dan Azure SQL Database.

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

Untuk contoh kompresi data tambahan, lihat Kompresi Data.

B. Memodifikasi tabel penyimpan kolom untuk mengubah pemadatan arsip

Contoh berikut selanjutnya memadatkan partisi tabel penyimpan kolom dengan menerapkan algoritma kompresi tambahan. Pemadatan ini mengurangi tabel ke ukuran yang lebih kecil, tetapi juga meningkatkan waktu yang diperlukan untuk penyimpanan dan pengambilan. Ini berguna untuk pengarsipan atau untuk situasi yang membutuhkan lebih sedikit ruang dan mampu lebih banyak waktu untuk penyimpanan dan pengambilan.

Berlaku untuk: SQL Server 2014 (12.x) dan yang lebih baru dan database Azure SQL.

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

Contoh berikut mendekompresi partisi tabel penyimpan kolom yang dikompresi dengan opsi COLUMNSTORE_ARCHIVE. Ketika data dipulihkan, data akan terus dikompresi dengan kompresi penyimpan kolom yang digunakan untuk semua tabel penyimpan kolom.

Berlaku untuk: SQL Server 2014 (12.x) dan yang lebih baru dan database Azure SQL.

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

C. Mengalihkan partisi antar tabel

Contoh berikut membuat tabel yang dipartisi, dengan asumsi bahwa skema myRangePS1 partisi sudah dibuat dalam database. Selanjutnya, tabel non-partisi dibuat dengan struktur yang sama dengan tabel yang dipartisi dan pada grup file yang sama dengan PARTITION 2 tabel PartitionTable. Data PARTITION 2 tabel PartitionTable kemudian dialihkan ke dalam tabel 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. Mengizinkan eskalasi kunci pada tabel yang dipartisi

Contoh berikut memungkinkan eskalasi kunci ke tingkat partisi pada tabel yang dipartisi. Jika tabel tidak dipartisi, eskalasi kunci diatur pada tingkat TABLE.

Berlaku untuk: SQL Server 2008 dan yang lebih baru dan Azure SQL Database.

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

E. Mengonfigurasi pelacakan perubahan pada tabel

Contoh berikut memungkinkan pelacakan perubahan pada Person.Person tabel.

Berlaku untuk: SQL Server 2008 dan yang lebih baru dan Azure SQL Database.

USE AdventureWorks;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING ;

Contoh berikut memungkinkan pelacakan perubahan dan mengaktifkan pelacakan kolom yang diperbarui selama perubahan.

Berlaku untuk: SQL Server 2008 dan yang lebih baru.

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

Contoh berikut menonaktifkan pelacakan perubahan pada Person.Person tabel.

Berlaku untuk: SQL Server 2008 dan yang lebih baru dan Azure SQL Database.

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

Menonaktifkan dan mengaktifkan batasan dan pemicu

J. Menonaktifkan dan mengaktifkan kembali batasan

Contoh berikut menonaktifkan batasan yang membatasi gaji yang diterima dalam data. NOCHECK CONSTRAINT digunakan dengan ALTER TABLE untuk menonaktifkan batasan dan memungkinkan penyisipan yang biasanya melanggar batasan. CHECK CONSTRAINT mengaktifkan kembali batasan.

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. Menonaktifkan dan mengaktifkan kembali pemicu

Contoh berikut menggunakan DISABLE TRIGGER opsi ALTER TABLE untuk menonaktifkan pemicu dan memungkinkan penyisipan yang biasanya melanggar pemicu. ENABLE TRIGGER kemudian digunakan untuk mengaktifkan kembali pemicu.

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

Operasi online

J. Pembangunan ulang indeks online menggunakan opsi tunggu berprioritas rendah

Contoh berikut menunjukkan cara melakukan pembangunan ulang indeks online yang menentukan opsi tunggu berprioritas rendah.

Berlaku untuk: SQL Server 2014 (12.x) dan yang lebih baru dan database 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. Kolom perubahan online

Contoh berikut menunjukkan cara menjalankan operasi ubah kolom dengan opsi ONLINE.

Berlaku untuk: SQL Server 2016 (13.x) dan yang lebih baru dan database 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

Penerapan versi sistem

Empat contoh berikut akan membantu Anda terbiasa dengan sintaks untuk menggunakan penerapan versi sistem. Untuk bantuan tambahan, lihat Memulai tabel Temporal System-Versioned.

Berlaku untuk: SQL Server 2016 (13.x) dan yang lebih baru dan database Azure SQL.

J. Menambahkan penerapan versi sistem ke tabel yang sudah ada

Contoh berikut menunjukkan cara menambahkan penerapan versi sistem ke tabel yang sudah ada dan membuat tabel riwayat di masa mendatang. Contoh ini mengasumsikan bahwa ada tabel yang sudah ada yang disebut InsurancePolicy dengan kunci primer yang ditentukan. Contoh ini mengisi kolom periode yang baru dibuat untuk penerapan versi sistem menggunakan nilai default untuk waktu mulai dan berakhir karena nilai ini tidak boleh null. Contoh ini menggunakan klausul HIDDEN untuk memastikan tidak ada dampak pada aplikasi yang ada yang berinteraksi dengan tabel saat ini. Ini juga menggunakan HISTORY_RETENTION_PERIOD yang hanya tersedia di SQL Database.

--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. Memigrasikan solusi yang ada untuk menggunakan penerapan versi sistem

Contoh berikut menunjukkan cara bermigrasi ke penerapan versi sistem dari solusi yang menggunakan pemicu untuk meniru dukungan temporal. Contoh mengasumsikan ada solusi yang sudah ada yang menggunakan ProjectTask tabel dan ProjectTaskHistory tabel untuk solusi yang sudah ada, yang menggunakan Changed Date kolom dan Revised Date untuk periodenya, bahwa kolom periode ini tidak menggunakan jenis data datetime2 dan bahwa tabel memiliki kunci primer yang ProjectTask ditentukan.

-- 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. Menonaktifkan dan mengaktifkan kembali penerapan versi sistem untuk mengubah skema tabel

Contoh ini memperlihatkan cara menonaktifkan penerapan versi sistem pada Department tabel, menambahkan kolom, dan mengaktifkan kembali penerapan versi sistem. Menonaktifkan penerapan versi sistem diperlukan untuk mengubah skema tabel. Lakukan langkah-langkah ini dalam transaksi untuk mencegah pembaruan pada kedua tabel saat memperbarui skema tabel, yang memungkinkan DBA melewati pemeriksaan konsistensi data saat mengaktifkan kembali penerapan versi sistem dan mendapatkan manfaat performa. Tugas seperti membuat statistik, mengalihkan partisi, atau menerapkan pemadatan ke satu atau kedua tabel tidak memerlukan pennonaktifkan penerapan versi sistem.

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. Menghapus penerapan versi sistem

Contoh ini memperlihatkan cara menghapus penerapan versi sistem sepenuhnya dari tabel Departemen dan menghilangkan DepartmentHistory tabel. Secara opsional, Anda mungkin juga ingin menghilangkan kolom periode yang digunakan oleh sistem untuk merekam informasi penerapan versi sistem. Anda tidak dapat menjatuhkan Department tabel atau DepartmentHistory saat penerapan versi sistem diaktifkan.

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

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)

Contoh berikut A hingga C menggunakan FactResellerSales tabel dalam database AdventureWorksPDW2012 .

J. Menentukan apakah tabel dipartisi

Kueri berikut mengembalikan satu atau beberapa baris jika tabel FactResellerSales dipartisi. Jika tabel tidak dipartisi, tidak ada baris yang dikembalikan.

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. Menentukan nilai batas untuk tabel yang dipartisi

Kueri berikut mengembalikan nilai batas untuk setiap partisi dalam FactResellerSales tabel.

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. Menentukan kolom partisi untuk tabel yang dipartisi

Kueri berikut mengembalikan nama kolom partisi untuk tabel. 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. Menggabungkan dua partisi

Contoh berikut menggabungkan dua partisi pada tabel.

Tabel Customer memiliki definisi berikut:

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

Perintah berikut menggabungkan 10 dan 25 batas partisi.

ALTER TABLE Customer MERGE RANGE (10);

DDL baru untuk tabel adalah:

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. Memisahkan partisi

Contoh berikut membagi partisi pada tabel.

Tabel Customer memiliki DDL berikut:

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

Perintah berikut membuat partisi baru yang terikat oleh nilai 75, antara 50 dan 100.

ALTER TABLE Customer SPLIT RANGE (75);

DDL baru untuk tabel adalah:

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. Menggunakan SWITCH untuk memindahkan partisi ke tabel riwayat

Contoh berikut memindahkan data dalam partisi Orders tabel ke partisi dalam OrdersHistory tabel.

Tabel Orders memiliki DDL berikut:

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

Dalam contoh ini, Orders tabel memiliki partisi berikut. Setiap partisi berisi data.

Partisi Memiliki data? Rentang batas
1 Ya OrderDate < '2004-01-01'
2 Ya '2004-01-01' <= OrderDate < '2005-01-01'
3 Ya '2005-01-01' <= OrderDate< '2006-01-01'
4 Ya '2006-01-01'<= OrderDate < '2007-01-01'
5 Ya '2007-01-01' <= OrderDate
  • Partisi 1 (memiliki data): OrderDate < '2004-01-01'
  • Partisi 2 (memiliki data): '2004-01-01' <= OrderDate < '2005-01-01'
  • Partisi 3 (memiliki data): '2005-01-01' <= OrderDate< '2006-01-01'
  • Partisi 4 (memiliki data): '2006-01-01'<= OrderDate < '2007-01-01'
  • Partisi 5 (memiliki data): '2007-01-01' <= OrderDate

Tabel OrdersHistory memiliki DDL berikut, yang memiliki nama kolom dan kolom yang identik sebagai Orders tabel. Keduanya didistribusikan hash pada id kolom .

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

Meskipun nama kolom dan kolom harus sama, batas partisi tidak perlu sama. Dalam contoh ini, OrdersHistory tabel memiliki dua partisi berikut dan kedua partisi kosong:

  • Partisi 1 (tanpa data): OrderDate < '2004-01-01'
  • Partisi 2 (kosong): '2004-01-01' <= OrderDate

Untuk dua tabel sebelumnya, perintah berikut memindahkan semua baris dengan OrderDate < '2004-01-01' dari Orders tabel ke OrdersHistory tabel.

ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersHistory PARTITION 1;

Akibatnya, partisi pertama di Orders kosong dan partisi pertama dalam OrdersHistory berisi data. Tabel sekarang muncul sebagai berikut:

Orders Meja

  • Partisi 1 (kosong): OrderDate < '2004-01-01'
  • Partisi 2 (memiliki data): '2004-01-01' <= OrderDate < '2005-01-01'
  • Partisi 3 (memiliki data): '2005-01-01' <= OrderDate< '2006-01-01'
  • Partisi 4 (memiliki data): '2006-01-01'<= OrderDate < '2007-01-01'
  • Partisi 5 (memiliki data): '2007-01-01' <= OrderDate

OrdersHistory Meja

  • Partisi 1 (memiliki data): OrderDate < '2004-01-01'
  • Partisi 2 (kosong): '2004-01-01' <= OrderDate

Untuk membersihkan Orders tabel, Anda dapat menghapus partisi kosong dengan menggabungkan partisi 1 dan 2 sebagai berikut:

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

Setelah penggabungan, Orders tabel memiliki partisi berikut:

Orders Meja

  • Partisi 1 (memiliki data): OrderDate < '2005-01-01'
  • Partisi 2 (memiliki data): '2005-01-01' <= OrderDate< '2006-01-01'
  • Partisi 3 (memiliki data): '2006-01-01'<= OrderDate < '2007-01-01'
  • Partisi 4 (memiliki data): '2007-01-01' <= OrderDate

Misalkan satu tahun lagi berlalu dan Anda siap untuk mengarsipkan tahun 2005. Anda dapat mengalokasikan partisi kosong untuk tahun 2005 dalam OrdersHistory tabel dengan membagi partisi kosong sebagai berikut:

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

Setelah pemisahan, OrdersHistory tabel memiliki partisi berikut:

OrdersHistory Meja

  • Partisi 1 (memiliki data): OrderDate < '2004-01-01'
  • Partisi 2 (kosong): '2004-01-01' < '2005-01-01'
  • Partisi 3 (kosong): '2005-01-01' <= OrderDate

Lihat juga

Langkah berikutnya