Bagikan melalui

ALTER TABLE index_option (Transact-SQL)

  • Artikel

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

Menentukan sekumpulan opsi yang dapat diterapkan ke indeks yang merupakan bagian dari definisi batasan yang dibuat dengan menggunakan ALTER TABLE.

Untuk deskripsi lengkap opsi indeks, lihat MEMBUAT INDEKS.

Konvensi sintaks transact-SQL

Sintaks

{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> }
      [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> }
      [ , ...n ] ) ]
  | ONLINE = { ON | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [ MINUTES ]
}

<range> ::=
<partition_number_expression> TO <partition_number_expression>

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

Argumen

PAD_INDEX = { ON | NONAKTIF }

Berlaku untuk: SQL Server 2008 (10.0.x) dan versi yang lebih baru

Menentukan padding indeks. Default adalah OFF.

  • AKTIF

    Persentase ruang kosong yang ditentukan oleh FILLFACTOR diterapkan ke halaman tingkat menengah indeks.

  • OFF atau fillfactor tidak ditentukan

    Halaman tingkat menengah diisi ke kapasitas dekat, menyisakan ruang yang cukup untuk setidaknya satu baris dari ukuran maksimum yang dapat dimiliki indeks, mengingat set kunci pada halaman perantara.

FILLFACTOR = fillfactor

Berlaku untuk: SQL Server 2008 (10.0.x) dan versi yang lebih baru

Menentukan persentase yang menunjukkan seberapa penuh Mesin Database harus membuat tingkat daun setiap halaman indeks selama pembuatan atau perubahan indeks. Nilai yang ditentukan harus berupa nilai bilangan bulat dari 1 hingga 100. Defaultnya adalah 0.

Catatan

Nilai faktor pengisian 0 dan 100 identik dalam segala hal.

IGNORE_DUP_KEY = { AKTIF | NONAKTIF }

Menentukan jenis respons saat operasi sisipkan mencoba menyisipkan nilai kunci duplikat ke dalam indeks unik. Opsi IGNORE_DUP_KEY hanya berlaku untuk menyisipkan operasi setelah indeks dibuat atau dibangun kembali. Opsi ini tidak berpengaruh saat menjalankan CREATE INDEX, ALTER INDEX, atau UPDATE. Default adalah OFF.

  • AKTIF

    Pesan peringatan terjadi ketika nilai kunci duplikat disisipkan ke dalam indeks unik. Hanya baris yang melanggar batasan keunikan yang gagal.

  • TIDAK AKTIF

    Pesan kesalahan terjadi ketika nilai kunci duplikat disisipkan ke dalam indeks unik. Seluruh INSERT operasi digulung balik.

IGNORE_DUP_KEY tidak dapat diatur ke untuk ON indeks yang dibuat pada tampilan, indeks nonunique, indeks XML, indeks spasial, dan indeks yang difilter.

Untuk melihat IGNORE_DUP_KEY, gunakan sys.indexes.

Dalam sintaksis kompatibel mundur, WITH IGNORE_DUP_KEY setara dengan WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { AKTIF | NONAKTIF }

Nonaktifkan atau aktifkan opsi pembaruan statistik otomatis, AUTO_STATISTICS_UPDATE, untuk statistik yang terkait dengan indeks yang ditentukan. Default adalah OFF.

  • AKTIF

    Pembaruan statistik otomatis dinonaktifkan setelah indeks dibangun kembali.

  • TIDAK AKTIF

    Pembaruan statistik otomatis diaktifkan setelah indeks dibangun kembali.

Untuk memulihkan pembaruan statistik otomatis, atur ke OFF, atau jalankan STATISTICS_NORECOMPUTE UPDATE STATISTICS tanpa NORECOMPUTE klausa.

Peringatan

Jika Anda menonaktifkan pembaruan statistik otomatis, mungkin mencegah Pengoptimal Kueri memilih rencana eksekusi optimal untuk kueri yang melibatkan tabel. Anda harus menggunakan opsi ini dengan hemat, dan hanya oleh administrator database yang memenuhi syarat.

Pengaturan ini tidak mencegah pembaruan otomatis dengan fullscan statistik terkait indeks, selama operasi pembangunan ulang.

ALLOW_ROW_LOCKS = { AKTIF | NONAKTIF }

Berlaku untuk: SQL Server 2008 (10.0.x) dan versi yang lebih baru

Menentukan apakah kunci baris diizinkan. Defaultnya adalah ON.

  • AKTIF

    Kunci baris diperbolehkan saat mengakses indeks. Mesin Database menentukan kapan kunci baris digunakan.

  • TIDAK AKTIF

    Kunci baris tidak digunakan.

ALLOW_PAGE_LOCKS = { AKTIF | NONAKTIF }

Berlaku untuk: SQL Server 2008 (10.0.x) dan versi yang lebih baru

Menentukan apakah kunci halaman diizinkan. Defaultnya adalah ON.

  • AKTIF

    Kunci halaman diizinkan saat mengakses indeks. Mesin Database menentukan kapan kunci halaman digunakan.

  • TIDAK AKTIF

    Kunci halaman tidak digunakan.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { AKTIF | NONAKTIF }

Berlaku untuk: SQL Server 2019 (15.x) dan versi yang lebih baru

Menentukan apakah akan mengoptimalkan ketidakcocokan sisipan halaman terakhir atau tidak. Default adalah OFF. Untuk informasi selengkapnya, lihat bagian Kunci berurutan di CREATE INDEX artikel.

SORT_IN_TEMPDB = { AKTIF | NONAKTIF }

Berlaku untuk: SQL Server 2008 (10.0.x) dan versi yang lebih baru

Menentukan apakah akan menyimpan hasil pengurutan di tempdb. Default adalah OFF.

  • AKTIF

    Hasil pengurutan menengah yang digunakan untuk membangun indeks disimpan di tempdb. Ini mungkin mengurangi waktu yang diperlukan untuk membuat indeks jika tempdb berada pada kumpulan disk yang berbeda dari database pengguna. Namun, ini meningkatkan jumlah ruang disk yang digunakan selama build indeks.

  • TIDAK AKTIF

    Hasil pengurutan menengah disimpan dalam database yang sama dengan indeks.

ONLINE = { AKTIF | NONAKTIF }

Berlaku untuk: SQL Server 2008 (10.0.x) dan versi yang lebih baru

Menentukan apakah tabel yang mendasari dan indeks terkait tersedia untuk kueri dan modifikasi data selama operasi indeks. Default adalah OFF. REBUILD dapat dilakukan sebagai ONLINE operasi.

Catatan

Indeks nonclustered unik tidak dapat dibuat secara online. Ini termasuk indeks yang dibuat karena UNIQUE batasan atau PRIMARY KEY .

  • AKTIF

    Kunci tabel jangka panjang tidak ditahan selama operasi indeks. Selama fase utama operasi indeks, hanya kunci Berbagi Niat (IS) yang ditahan pada tabel sumber. Ini memungkinkan kueri atau pembaruan pada 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 berkluster atau non-kluster sedang dibangun kembali. Meskipun kunci indeks online adalah kunci metadata pendek, terutama kunci Sch-M harus menunggu semua transaksi pemblokiran selesai pada tabel ini. Selama waktu tunggu, kunci Sch-M memblokir semua transaksi lain yang menunggu di belakang kunci ini saat mengakses tabel yang sama. ONLINE tidak dapat diatur ke ON saat indeks sedang dibuat pada tabel sementara lokal.

    Catatan

    Pembangunan ulang indeks online dapat mengatur opsi low_priority_lock_wait yang dijelaskan nanti di bagian ini. low_priority_lock_wait mengelola prioritas kunci S dan Sch-M selama pembangunan ulang indeks online.

  • TIDAK AKTIF

    Kunci tabel diterapkan selama operasi indeks. Ini mencegah semua akses pengguna ke tabel yang mendasar selama operasi. 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. Ini mencegah semua akses pengguna ke tabel yang mendasar selama operasi. Operasi indeks offline yang membuat indeks nonclustered memperoleh kunci Bersama (S) pada tabel. Ini mencegah pembaruan pada tabel yang mendasar tetapi memungkinkan operasi baca, seperti SELECT pernyataan.

Untuk informasi selengkapnya, lihat Cara Kerja Operasi Indeks Online.

Catatan

Operasi indeks online tidak tersedia di setiap edisi Microsoft SQL Server. Untuk daftar fitur yang didukung oleh edisi SQL Server, lihat Edisi dan fitur yang didukung SQL Server 2022.

DAPAT DI-RESUMABLE = { ON | OFF}

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

Menentukan apakah ALTER TABLE ADD CONSTRAINT operasi dapat diulang. Tambahkan operasi batasan tabel dapat dilanjutkan saat ON. Operasi tambahkan batasan tabel tidak dapat dilanjutkan saat OFF. Defaultnya adalah OFF. RESUMABLE Saat opsi diatur ke ON, opsi ONLINE = ON diperlukan.

MAX_DURATION ketika 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. MAXDOPjuga didukung.RESUMABLE = ON

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

MAXDOP = max_degree_of_parallelism

Berlaku untuk: SQL Server 2008 (10.0.x) dan versi yang lebih baru

Mengambil alih tingkat maksimum opsi konfigurasi paralelisme selama operasi indeks. Untuk informasi selengkapnya, lihat Mengonfigurasi tingkat paralelisme maksimum (opsi konfigurasi server). Gunakan MAXDOP untuk membatasi jumlah prosesor yang digunakan dalam eksekusi rencana paralel. Maksimum adalah 64 prosesor.

max_degree_of_parallelism dapat berupa:

  • 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 Microsoft SQL Server. Untuk daftar fitur yang didukung oleh edisi SQL Server, lihat Edisi dan fitur yang didukung SQL Server 2022.

DATA_COMPRESSION

Berlaku untuk: SQL Server 2008 (10.0.x) dan versi yang lebih baru

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

  • NONE

    Tabel atau partisi yang ditentukan tidak dikompresi. Hanya berlaku untuk tabel rowstore; tidak berlaku untuk tabel penyimpan kolom.

  • BARIS

    Tabel atau partisi tertentu dikompresi dengan menggunakan pemadatan baris. Hanya berlaku untuk tabel rowstore; tidak berlaku untuk tabel penyimpan kolom.

  • PAGE

    Tabel atau partisi yang ditentukan dikompresi dengan menggunakan pemadatan halaman. Hanya berlaku untuk tabel rowstore; tidak berlaku untuk tabel penyimpan kolom.

  • PENYIMPAN KOLOM

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

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

  • COLUMNSTORE_ARCHIVE

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

    Hanya berlaku untuk tabel penyimpan kolom, yang merupakan tabel yang disimpan dengan indeks penyimpan kolom berkluster. COLUMNSTORE_ARCHIVE lebih lanjut memadatkan partisi yang ditentukan ke ukuran yang lebih kecil. Ini dapat digunakan untuk pengarsipan, atau untuk situasi lain yang membutuhkan lebih sedikit penyimpanan dan mampu lebih banyak waktu untuk penyimpanan dan pengambilan

Untuk informasi selengkapnya tentang pemadatan, lihat Pemadatan data.

XML_COMPRESSION

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance.

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

  • AKTIF

    Kolom yang menggunakan jenis data xml dikompresi.

  • TIDAK AKTIF

    Kolom yang menggunakan tipe data xml tidak dikompresi.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

Berlaku untuk: SQL Server 2008 (10.0.x) dan versi yang lebih baru

Menentukan partisi yang diterapkan pengaturan DATA_COMPRESSION atau XML_COMPRESSION . Jika tabel tidak dipartisi, ON PARTITIONS argumen akan menghasilkan kesalahan. ON PARTITIONS Jika klausa tidak disediakan, DATA_COMPRESSION opsi atau XML_COMPRESSION berlaku untuk semua partisi tabel yang dipartisi.

<partition_number_expression> dapat ditentukan dengan cara berikut:

  • Berikan nomor partisi, misalnya: ON PARTITIONS (2).
  • Berikan nomor partisi untuk beberapa partisi individual yang dipisahkan oleh koma, misalnya: ON PARTITIONS (1, 5).
  • Berikan rentang dan partisi individual, misalnya: ON PARTITIONS (2, 4, 6 TO 8).

<range> dapat ditentukan sebagai nomor partisi yang dipisahkan oleh kata TO, misalnya: ON PARTITIONS (6 TO 8).

Untuk mengatur berbagai jenis kompresi data untuk partisi yang berbeda, tentukan DATA_COMPRESSION opsi lebih dari sekali, misalnya:

--For rowstore tables
REBUILD WITH
(
  DATA_COMPRESSION = NONE ON PARTITIONS (1),
  DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
  DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
)

--For columnstore tables
REBUILD WITH
(
  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (1, 3, 5),
  DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4, 6 TO 8)
)

<single_partition_rebuild__option>

Dalam kebanyakan kasus, membangun kembali indeks juga membangun kembali semua partisi indeks yang dipartisi. Opsi berikut, saat diterapkan ke satu partisi, jangan bangun kembali semua partisi.

  • SORT_IN_TEMPDB
  • MAXDOP
  • DATA_COMPRESSION
  • XML_COMPRESSION

low_priority_lock_wait

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

Pembangunan SWITCH kembali indeks atau online selesai segera setelah tidak ada operasi pemblokiran untuk tabel ini. WAIT_AT_LOW_PRIORITY menunjukkan bahwa jika SWITCH operasi pembangunan ulang atau indeks online tidak dapat segera diselesaikan, operasi tersebut akan menunggu. Operasi ini memegang kunci prioritas rendah, memungkinkan operasi lain yang menahan kunci yang bertentangan dengan pernyataan DDL untuk melanjutkan. WAIT AT LOW PRIORITY Menghilangkan opsi setara dengan WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = waktu [ MENIT ]

Waktu tunggu (nilai bilangan bulat yang ditentukan dalam menit) yang SWITCH kunci pembangunan ulang indeks atau online yang harus diperoleh, menunggu saat menjalankan perintah DDL. Operasi SWITCH pembangunan ulang indeks atau online mencoba untuk segera diselesaikan. Jika operasi diblokir untuk waktu tersebut MAX_DURATION , salah ABORT_AFTER_WAIT satu tindakan dijalankan. MAX_DURATION waktu selalu dalam hitungan menit, dan kata MINUTES dapat dihilangkan.

ABORT_AFTER_WAIT = { NONE | SELF | PEMBLOKIR }

  • NONE

    SWITCH Melanjutkan operasi pembangunan kembali atau indeks online tanpa mengubah prioritas kunci (menggunakan prioritas reguler).

  • DIRI

    Keluar dari SWITCH operasi DDL pembangunan ulang indeks online atau yang saat ini sedang dijalankan tanpa mengambil tindakan apa pun.

  • BLOCKER

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

    BLOCKERSALTER ANY CONNECTION memerlukan izin.

Konten terkait