ALTER INDEX (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 tabel atau indeks tampilan yang ada (rowstore, columnstore, atau XML) dengan menonaktifkan, membangun kembali, atau mengatur ulang indeks; atau dengan mengatur opsi pada indeks.

tautan topikIkon Konvensi Sintaks Transact-SQL

Sintaks

-- Syntax for SQL Server and Azure SQL Database

ALTER INDEX { index_name | ALL } ON <object>
{
      REBUILD {
            [ PARTITION = ALL ] [ WITH ( <rebuild_index_option> [ ,...n ] ) ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) [ ,...n ] ]
      }
    | DISABLE
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]
    | SET ( <set_index_option> [ ,...n ] )
    | RESUME [WITH (<resumable_index_options>,[...n])]
    | PAUSE
    | ABORT
}
[ ; ]

<object> ::=
{
    { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
}

<rebuild_index_option > ::=
{
      PAD_INDEX = { ON | OFF }
    | FILLFACTOR = fillfactor
    | SORT_IN_TEMPDB = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | STATISTICS_INCREMENTAL = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [MINUTES}
    | ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | 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_index_option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [MINUTES}
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE } }
    | XML_COMPRESSION = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<reorganize_option>::=
{
       LOB_COMPACTION = { ON | OFF }
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF}
}

<set_index_option>::=
{
      ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | COMPRESSION_DELAY = { 0 | delay [Minutes] }
}

<resumable_index_option> ::=
 {
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION =<time> [MINUTES]
    | <low_priority_lock_wait>
 }

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse

ALTER INDEX { index_name | ALL }
    ON   [ schema_name. ] table_name
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> )] ]
      }
    | DISABLE
    | REORGANIZE [ PARTITION = partition_number ]
}
[;]

<rebuild_index_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_index_option > ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
}

Catatan

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

Argumen

index_name

Nama indeks. Nama indeks harus unik dalam tabel atau tampilan tetapi tidak harus unik dalam database. Nama indeks harus mengikuti aturan pengidentifikasi.

SEMUA

Menentukan semua indeks yang terkait dengan tabel atau tampilan terlepas dari jenis indeks. Menentukan SEMUA menyebabkan pernyataan gagal jika satu atau beberapa indeks berada dalam grup file offline atau baca-saja atau operasi yang ditentukan tidak diizinkan pada satu atau beberapa jenis indeks. Tabel berikut mencantumkan operasi indeks dan jenis indeks yang tidak diizinkan.

Menggunakan kata kunci ALL dengan operasi ini Gagal jika tabel memiliki satu atau beberapa
BANGUN ULANG DENGAN ONLINE = AKTIF Indeks XML

Indeks spasial

Indeks penyimpan kolom: Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2012 (11.x)) dan Database Azure SQL
MEMBANGUN KEMBALI PARTISI = partition_number Indeks nonpartisi, indeks XML, indeks spasial, atau indeks yang dinonaktifkan
MENGATUR ULANG Indeks dengan ALLOW_PAGE_LOCKS diatur ke NONAKTIF
MENGATUR ULANG PARTISI = partition_number Indeks nonpartisi, indeks XML, indeks spasial, atau indeks yang dinonaktifkan
IGNORE_DUP_KEY = AKTIF Indeks XML

Indeks spasial

Indeks penyimpan kolom: Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2012 (11.x)) dan Database Azure SQL
ONLINE = AKTIF Indeks XML

Indeks spasial

Indeks penyimpan kolom: Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2012 (11.x)) dan Database Azure SQL
DAPAT DIANDALKAN = AKTIF Indeks yang dapat diulang tidak didukung dengan semua kata kunci.

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2017 (14.x)) dan Database Azure SQL

Peringatan

Untuk informasi lebih rinci tentang operasi indeks yang dapat dilakukan secara online, lihat Panduan untuk Operasi Indeks Online.

Jika ALL ditentukan dengan PARTITION = partition_number, semua indeks harus diratakan. Ini berarti bahwa mereka dipartisi berdasarkan fungsi partisi yang setara. Menggunakan ALL dengan PARTISI menyebabkan semua partisi indeks dengan partition_number yang sama dibangun kembali atau diorganisir ulang. Untuk informasi selengkapnya tentang indeks yang dipartisi, lihat Tabel dan Indeks yang Dipartisi.

database_name

Nama database.

nama_skema

Nama skema tempat tabel atau tampilan berada.

table_or_view_name

Nama tabel atau tampilan yang terkait dengan indeks. Untuk menampilkan laporan indeks pada objek, gunakan tampilan katalog sys.indexes .

SQL Database mendukung format nama tiga bagian database_name. [schema_name].table_or_view_name ketika database_name adalah database saat ini atau database_name adalah tempdb dan table_or_view_name dimulai dengan #.

BANGUN KEMBALI [ DENGAN (<rebuild_index_option> [ ,... n]) ]

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2012 (11.x)) dan Database Azure SQL

Menentukan indeks akan dibangun kembali menggunakan kolom, jenis indeks, atribut keunikan, dan urutan pengurutan yang sama. Klausa ini setara dengan DBCC DBREINDEX. REBUILD mengaktifkan indeks yang dinonaktifkan. Membangun kembali indeks berkluster tidak membangun kembali indeks non-kluster terkait kecuali kata kunci SEMUA ditentukan. Jika opsi indeks tidak ditentukan, nilai opsi indeks yang ada yang disimpan dalam sys.indexes diterapkan. Untuk opsi indeks apa pun yang nilainya tidak disimpan di , default yang ditunjukkan sys.indexesdalam definisi argumen opsi berlaku.

Jika ALL ditentukan dan tabel yang mendasar adalah tumpukan, REBUILD operasi tidak berpengaruh pada tabel. Setiap indeks nonkluster yang terkait dengan tabel dibangun kembali.

REBUILD Operasi dapat dicatat secara minimal jika model pemulihan database diatur ke dicatat secara massal atau sederhana.

Catatan

Saat Anda membangun kembali indeks XML utama, tabel pengguna yang mendasar tidak tersedia selama operasi indeks.

Untuk indeks penyimpan kolom, REBUILD operasi:

  • Tidak menggunakan urutan pengurutan.
  • Memperoleh kunci eksklusif pada tabel atau partisi saat REBUILD terjadi. Data "offline" dan tidak tersedia selama REBUILD, bahkan saat menggunakan NOLOCK, isolasi Read Committed Snapshot (RCSI), atau Isolasi rekam jepret (SI).
  • Mengkompresi ulang semua data ke dalam penyimpan kolom. Ada dua salinan indeks penyimpan kolom saat REBUILD sedang berlangsung. REBUILD Setelah selesai, SQL Server menghapus indeks penyimpan kolom asli.

Untuk informasi selengkapnya, lihat Mengatur ulang dan Membangun Ulang Indeks.

PARTITION

Menentukan bahwa hanya satu partisi indeks yang akan dibangun kembali atau diatur ulang. PARTISI tidak dapat ditentukan jika index_name bukan indeks yang dipartisi.

PARTITION = SEMUA membangun kembali semua partisi.

Peringatan

Membuat dan membangun kembali indeks yang tidak ditandatangani pada tabel dengan lebih dari 1.000 partisi dimungkinkan, tetapi tidak didukung. Melakukannya dapat menyebabkan penurunan performa atau konsumsi memori yang berlebihan selama operasi ini. Microsoft merekomendasikan untuk hanya menggunakan indeks yang selaras ketika jumlah partisi melebihi 1.000.

partition_number

Nomor partisi indeks yang dipartisi yang akan dibangun kembali atau diorganisir ulang. partition_number adalah ekspresi konstanta yang dapat mereferensikan variabel. Ini termasuk variabel atau fungsi jenis yang ditentukan pengguna dan fungsi yang ditentukan pengguna, tetapi tidak dapat mereferensikan pernyataan Transact-SQL. partition_number harus ada atau pernyataan gagal.

WITH (<single_partition_rebuild_index_option>)

SORT_IN_TEMPDB, MAXDOP, DATA_COMPRESSION, dan XML_COMPRESSION adalah opsi yang dapat ditentukan ketika Anda REBUILD satu partisi (PARTITION = partition_number). Indeks XML tidak dapat ditentukan dalam satu operasi partisi REBUILD .

MENONAKTIFKAN

Menandai indeks sebagai dinonaktifkan dan tidak tersedia untuk digunakan oleh Mesin Database. Indeks apa pun dapat dinonaktifkan. Definisi indeks indeks yang dinonaktifkan tetap berada di katalog sistem tanpa data indeks yang mendasar. Menonaktifkan indeks berkluster mencegah akses pengguna ke data tabel yang mendasar. Untuk mengaktifkan indeks, gunakan ALTER INDEX REBUILD atau CREATE INDEX WITH DROP_EXISTING. Untuk informasi selengkapnya, lihat Menonaktifkan Indeks dan Batasan dan Mengaktifkan Indeks dan Batasan.

MENGATUR ULANG indeks rowstore

Untuk indeks rowstore, REORGANIZE menentukan untuk mengatur ulang tingkat daun indeks. Operasi REORGANIZE adalah:

  • Selalu dilakukan secara online. Ini berarti kunci tabel pemblokiran jangka panjang tidak ditahan dan kueri atau pembaruan pada tabel yang mendasarinya dapat dilanjutkan ALTER INDEX REORGANIZE selama transaksi.
  • Tidak diperbolehkan untuk indeks yang dinonaktifkan.
  • Tidak diperbolehkan saat ALLOW_PAGE_LOCKS diatur ke NONAKTIF.
  • Tidak digulung balik ketika dilakukan dalam transaksi dan transaksi digulung balik.

Catatan

Ketika ALTER INDEX REORGANIZE menggunakan transaksi eksplisit (misalnya, ALTER INDEX di dalam BEGIN TRAN ... COMMIT/ROLLBACK) alih-alih mode transaksi implisit default, perilaku penguncian REORGANIZE menjadi lebih ketat, berpotensi menyebabkan pemblokiran. Untuk informasi selengkapnya tentang transaksi implisit, lihat SET IMPLICIT_TRANSACTIONS (Transact-SQL).

Untuk informasi selengkapnya, lihat Mengatur Ulang dan Membangun Ulang Indeks.

MENGATUR ULANG DENGAN ( LOB_COMPACTION = { ON | OFF } )

Berlaku untuk indeks rowstore.

LOB_COMPACTION = AKTIF

  • Menentukan untuk memampatkan semua halaman yang berisi data jenis data objek besar (LOB) ini: gambar, teks, ntext, varchar(max), nvarchar(max), varbinary(max), dan xml. Memampatkan data ini dapat mengurangi ukuran data pada disk.
  • Untuk indeks berkluster, ini memampatkan semua kolom LOB yang terkandung dalam tabel.
  • Untuk indeks nonkluster, ini memadatkan semua kolom LOB yang merupakan kolom non-kunci (disertakan) dalam indeks.
  • REORGANIZE ALL melakukan LOB_COMPACTION pada semua indeks. Untuk setiap indeks, ini memadatkan semua kolom LOB dalam indeks berkluster, tabel yang mendasar, atau kolom yang disertakan dalam indeks non-kluster.

LOB_COMPACTION = NONAKTIF

  • Halaman yang berisi data objek besar tidak dikompresi.
  • OFF tidak berpengaruh pada tumpukan.

MENGATUR ULANG indeks penyimpan kolom

Untuk indeks penyimpan kolom, REORGANIZE kompres setiap grup baris delta TERTUTUP ke dalam penyimpan kolom sebagai grup baris terkompresi. Operasi REORGANIZE selalu dilakukan secara online. Ini berarti kunci tabel pemblokiran jangka panjang tidak ditahan dan kueri atau pembaruan pada tabel yang mendasarinya dapat dilanjutkan ALTER INDEX REORGANIZE selama transaksi. Untuk informasi selengkapnya, lihat Mengatur Ulang dan Membangun Ulang Indeks.

  • REORGANIZE tidak diperlukan untuk memindahkan grup baris delta CLOSED ke grup baris terkompresi. Proses tuple-mover (TM) latar belakang bangun secara berkala untuk memadatkan grup baris delta CLOSED. Sebaiknya gunakan REORGANIZE ketika tuple-mover tertinggal. MENGATUR ULANG dapat memadatkan grup baris secara lebih agresif.
  • Untuk memadatkan semua grup baris OPEN dan CLOSED, lihat REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS) opsi di bagian ini.

Untuk indeks penyimpan kolom di SQL Server (Dimulai dengan SQL Server 2016 (13.x)) dan Database Azure SQL, REORGANIZE melakukan pengoptimalan defragmentasi tambahan berikut secara online:

  • Secara fisik menghapus baris dari grup baris ketika 10% atau lebih baris telah dihapus secara logis. Byte yang dihapus diklaim kembali di media fisik. Misalnya, jika grup baris terkompresi 1 juta baris memiliki 100.000 baris yang dihapus, SQL Server akan menghapus baris yang dihapus dan mengompresi ulang grup baris dengan baris 900k. Ini menyimpan pada penyimpanan dengan menghapus baris yang dihapus.

  • Menggabungkan satu atau beberapa grup baris terkompresi untuk meningkatkan baris per grup baris hingga maksimum 1.048.576 baris. Misalnya, jika Anda mengimpor secara massal 5 batch dari 102.400 baris, Anda akan mendapatkan 5 grup baris terkompresi. Jika Anda menjalankan REORGANIZE, grup baris ini akan digabungkan menjadi 1 grup baris terkompresi dengan ukuran 512.000 baris. Ini mengasumsikan tidak ada ukuran kamus atau batasan memori.

  • Untuk grup baris di mana 10% atau lebih baris telah dihapus secara logis, SQL Server akan mencoba menggabungkan grup baris ini dengan satu atau beberapa grup baris. Misalnya, grup baris 1 dikompresi dengan 500.000 baris dan grup baris 21 dikompresi dengan maksimum 1.048.576 baris. Grup baris 21 memiliki 60% baris yang dihapus yang meninggalkan 409.830 baris. SQL Server mendukung menggabungkan kedua grup baris ini untuk memadatkan grup baris baru yang memiliki 909.830 baris.

MENGATUR ULANG DENGAN ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

Berlaku untuk indeks penyimpan kolom.

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2016 (13.x)) dan Database Azure SQL

COMPRESS_ALL_ROW_GROUPS menyediakan cara untuk memaksa grup baris delta TERBUKA atau TERTUTUP ke dalam penyimpan kolom. Dengan opsi ini, tidak perlu membangun kembali indeks penyimpan kolom untuk mengosongkan grup baris delta. Ini, dikombinasikan dengan fitur defragmentasi penghapusan dan penggabungan lainnya membuatnya tidak lagi diperlukan untuk membangun kembali indeks dalam sebagian besar situasi.

  • ON memaksa semua grup baris ke dalam penyimpan kolom, terlepas dari ukuran dan status (CLOSED atau OPEN).
  • OFF memaksa semua grup baris TERTUTUP ke dalam penyimpan kolom.

Untuk informasi selengkapnya, lihat Mengatur Ulang dan Membangun Ulang Indeks.

SET ( <opsi> set_index [ ,... n] )

Menentukan opsi indeks tanpa membangun kembali atau mengatur ulang indeks. SET tidak dapat ditentukan untuk indeks yang dinonaktifkan.

PAD_INDEX = { ON | OFF }

Menentukan padding indeks. Defaultnya adalah NONAKTIF.

AKTIF

Persentase ruang kosong yang ditentukan oleh FILLFACTOR diterapkan ke halaman tingkat menengah indeks. Jika FILLFACTOR tidak ditentukan pada saat PAD_INDEX yang sama diatur ke AKTIF, nilai faktor pengisian yang disimpan dalam sys.indexes digunakan.

OFF atau fillfactor tidak ditentukan

Halaman tingkat menengah diisi mendekati kapasitas. Ini menyisakan ruang yang cukup untuk setidaknya satu baris dengan ukuran maksimum yang dapat dimiliki indeks, berdasarkan kumpulan kunci pada halaman perantara.

Untuk informasi selengkapnya, lihat CREATE INDEX (Transact-SQL).

FILLFACTOR = fillfactor

Menentukan persentase yang menunjukkan seberapa lengkap Mesin Database harus membuat tingkat daun dari setiap halaman indeks selama pembuatan atau perubahan indeks. Nilai untuk fillfactor harus berupa nilai bilangan bulat dari 1 hingga 100. Defaultnya adalah 0. Nilai faktor pengisian 0 dan 100 sama dalam semua hal.

Pengaturan eksplisit FILLFACTOR hanya berlaku saat indeks pertama kali dibuat atau dibangun kembali. Mesin Database tidak secara dinamis menyimpan persentase ruang kosong yang ditentukan di halaman. Untuk informasi selengkapnya, lihat CREATE INDEX (Transact-SQL).

Untuk melihat pengaturan faktor pengisian, gunakan fill_factor di sys.indexes.

Penting

Membuat atau mengubah indeks berkluster dengan FILLFACTOR nilai memengaruhi jumlah ruang penyimpanan yang ditempati data, karena Mesin Database mendistribusikan ulang data saat membuat indeks berkluster.

SORT_IN_TEMPDB = { ON | OFF }

Menentukan apakah akan menyimpan hasil pengurutan dalam tempdb. Defaultnya adalah NONAKTIF kecuali untuk Azure SQL Database Hyperscale. Untuk semua operasi pembangunan ulang indeks di Hyperscale, SORT_IN_TEMPDB selalu AKTIF, terlepas dari opsi yang ditentukan kecuali pembangunan ulang indeks yang dapat diulang digunakan.

AKTIF
Hasil pengurutan menengah yang digunakan untuk membangun indeks disimpan dalam tempdb. Jika tempdb berada pada sekumpulan disk yang berbeda dari database pengguna, ini dapat mengurangi waktu yang diperlukan untuk membuat indeks. Namun, ini meningkatkan jumlah ruang disk yang digunakan selama build indeks.

TIDAK AKTIF
Hasil pengurutan menengah disimpan dalam database yang sama dengan indeks.

Jika operasi pengurutan tidak diperlukan, atau jika pengurutan dapat dilakukan dalam memori, SORT_IN_TEMPDB opsi diabaikan.

Untuk informasi selengkapnya, lihat Opsi SORT_IN_TEMPDB Untuk Indeks.

IGNORE_DUP_KEY = { ON | OFF }

Menentukan respons kesalahan saat operasi penyisipan mencoba menyisipkan nilai kunci duplikat ke dalam indeks unik. Opsi IGNORE_DUP_KEY ini hanya berlaku untuk menyisipkan operasi setelah indeks dibuat atau dibangun kembali. Defaultnya adalah NONAKTIF.

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

TIDAK AKTIF
Pesan kesalahan akan terjadi ketika nilai kunci duplikat dimasukkan ke dalam indeks unik. Seluruh operasi INSERT akan digulung balik.

IGNORE_DUP_KEY tidak dapat diatur ke AKTIF untuk indeks yang dibuat pada tampilan, indeks non-unik, 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 = { ON | OFF }

Menentukan apakah statistik distribusi dikomputasi ulang. Defaultnya adalah NONAKTIF.

AKTIF
Statistik kedaluarsa tidak dikomputasi ulang secara otomatis.

TIDAK AKTIF
Pembaruan statistik otomatis diaktifkan.

Untuk memulihkan pembaruan statistik otomatis, atur STATISTICS_NORECOMPUTE ke NONAKTIF, atau jalankan UPDATE STATISTICSNORECOMPUTE tanpa klausul .

Penting

Menonaktifkan komputasi ulang statistik distribusi otomatis dapat mencegah Pengoptimal Kueri memilih rencana eksekusi optimal untuk kueri yang melibatkan tabel.

STATISTICS_INCREMENTAL = { ON | OFF }

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2014 (12.x)) dan Database Azure SQL

Saat ON, statistik yang dibuat adalah statistik per partisi. Saat OFF, pohon statistik dijatuhkan dan SQL Server mengolah ulang statistik. Defaultnya adalah NONAKTIF.

Jika statistik per partisi tidak didukung, opsi diabaikan dan peringatan dibuat. Statistik inkremental tidak didukung untuk jenis statistik berikut:

  • Statistik yang dibuat dengan indeks yang tidak selaras dengan tabel dasar
  • Statistik yang dibuat pada database sekunder yang dapat dibaca grup ketersediaan
  • Statistik yang dibuat pada database baca-saja
  • Statistik yang dibuat pada indeks yang difilter
  • Statistik dibuat pada tampilan
  • Statistik yang dibuat pada tabel internal
  • Statistik yang dibuat dengan indeks spasial atau indeks XML

ONLINE = { ON | OFF } <sebagaimana berlaku untuk rebuild_index_option>

Menentukan apakah tabel yang mendasari dan indeks terkait tersedia untuk kueri dan modifikasi data selama operasi indeks. Defaultnya adalah NONAKTIF.

Untuk indeks XML atau indeks spasial, hanya ONLINE = OFF didukung, dan jika ONLINE diatur ke AKTIF, kesalahan dimunculkan.

Penting

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

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 untuk tabel dan indeks yang mendasarinya untuk melanjutkan. Pada awal operasi, kunci Bersama (S) sangat singkat ditahan pada objek sumber. Pada akhir operasi, kunci S sangat singkat ditahan pada sumber jika indeks non-kluster sedang dibuat. Kunci Modifikasi Skema (Sch-M) 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.

TIDAK AKTIF
Kunci tabel diterapkan selama operasi indeks. Operasi indeks offline yang membuat, membangun kembali, atau menghilangkan indeks berkluster, spasial, atau XML, 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 nonkluster memperoleh kunci Bersama (S) pada tabel. Ini mencegah pembaruan pada tabel yang mendasar tetapi memungkinkan operasi baca, seperti pernyataan SELECT.

Untuk informasi selengkapnya, lihat Melakukan Operasi Indeks Online.

Indeks, termasuk indeks pada tabel sementara global, dapat dibangun kembali secara online kecuali untuk kasus berikut:

  • Indeks XML
  • Indeks pada tabel sementara lokal
  • Indeks berkluster unik awal pada tampilan
  • Indeks penyimpan kolom
  • Indeks berkluster, jika tabel yang mendasar berisi jenis data LOB (gambar, ntext, teks) dan jenis data spasial
  • Kolom varchar(max) dan varbinary(max) tidak dapat menjadi bagian dari indeks. Dalam SQL Server (Dimulai dengan SQL Server 2012 (11.x)) dan database Azure SQL, saat tabel berisi kolom varchar(max) atau varbinary(max), indeks berkluster yang berisi kolom lain dapat dibangun atau dibangun kembali menggunakan ONLINE opsi . Azure SQL Database tidak mengizinkan ONLINE opsi saat tabel dasar berisi kolom varchar(max) atau varbinary(max)

Untuk informasi selengkapnya, lihat Cara Kerja Operasi Indeks Online.

DAPAT DIMUAT ULANG = { ON | OFF}

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2017 (14.x)) dan Database Azure SQL

Menentukan apakah operasi indeks online dapat diulang.

AKTIF
Operasi indeks dapat diulang.

TIDAK AKTIF
Operasi indeks tidak dapat diulang.

MAX_DURATION = waktu [MINUTES] digunakan dengan RESUMABLE = ON (memerlukan ONLINE = ON)

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2017 (14.x)) dan Database Azure SQL

Menunjukkan waktu (nilai bilangan bulat yang ditentukan dalam menit) bahwa operasi indeks online yang dapat dilanjutkan dijalankan sebelum dijeda.

Penting

Untuk informasi selengkapnya tentang operasi indeks yang dapat dilakukan secara online, lihat Panduan untuk Operasi Indeks Online.

Catatan

Pembangunan ulang indeks online yang dapat diulang tidak didukung pada indeks penyimpan kolom.

ALLOW_ROW_LOCKS = { ON | OFF }

Menentukan apakah kunci baris diperbolehkan. Defaultnya adalah AKTIF.

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

TIDAK AKTIF
Kunci baris tidak digunakan.

ALLOW_PAGE_LOCKS = { ON | OFF }

Menentukan apakah kunci halaman diperbolehkan. Defaultnya adalah AKTIF.

AKTIF
Kunci halaman diperbolehkan saat Anda mengakses indeks. Mesin Database menentukan kapan kunci halaman digunakan.

TIDAK AKTIF
Kunci halaman tidak digunakan.

Catatan

Indeks tidak dapat diatur ulang ketika ALLOW_PAGE_LOCKS diatur ke NONAKTIF.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2019 (15.x)) dan Database Azure SQL

Menentukan apakah akan mengoptimalkan ketidakcocokan sisipan halaman terakhir atau tidak. Defaultnya adalah NONAKTIF. Untuk informasi selengkapnya, lihat Kunci Berurutan.

MAXDOP = max_degree_of_parallelism

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

Penting

Meskipun opsi MAXDOP secara sintaksis didukung untuk semua indeks XML, untuk indeks spasial atau indeks XML utama, ALTER INDEX saat ini hanya menggunakan satu 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 SQL Server. Untuk daftar fitur yang didukung oleh edisi SQL Server, lihat Edisi dan Fitur yang Didukung untuk SQL Server 2016.

COMPRESSION_DELAY = { 0 | durasi [Menit] }

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2016 (13.x))

Untuk tabel berbasis disk, penundaan menentukan jumlah menit minimum grup baris delta dalam status TERTUTUP harus tetap berada di grup baris delta sebelum SQL Server dapat memadatkannya ke dalam grup baris terkompresi. Karena tabel berbasis disk tidak melacak waktu penyisipan dan pembaruan pada baris individual, SQL Server menerapkan penundaan ke grup baris delta dalam status TERTUTUP.

Defaultnya adalah 0 menit.

Untuk rekomendasi tentang kapan harus menggunakan COMPRESSION_DELAY, lihat Mulai menggunakan Columnstore untuk analitik operasional real time.

DATA_COMPRESSION

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

TIDAK ADA
Indeks atau partisi yang ditentukan tidak dikompresi. Ini tidak berlaku untuk indeks penyimpan kolom.

ROW
Indeks atau partisi yang ditentukan dikompresi dengan menggunakan pemadatan baris. Ini tidak berlaku untuk indeks penyimpan kolom.

PAGE
Indeks atau partisi yang ditentukan dikompresi dengan menggunakan pemadatan halaman. Ini tidak berlaku untuk indeks penyimpan kolom.

PENYIMPAN KOLOM

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2014 (12.x)) dan Database Azure SQL

Hanya berlaku untuk indeks penyimpan kolom, termasuk penyimpan kolom non-kluster dan indeks penyimpan kolom berkluster. COLUMNSTORE menentukan untuk mendekompresi indeks atau partisi tertentu yang dikompresi dengan opsi COLUMNSTORE_ARCHIVE. Ketika data dipulihkan, data akan terus dikompresi dengan pemadatan penyimpan kolom yang digunakan untuk semua indeks penyimpan kolom.

COLUMNSTORE_ARCHIVE

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2014 (12.x)) dan Database Azure SQL

Hanya berlaku untuk indeks penyimpan kolom, termasuk penyimpan kolom non-kluster dan indeks penyimpan kolom berkluster. COLUMNSTORE_ARCHIVE selanjutnya akan memadatkan partisi yang ditentukan ke ukuran yang lebih kecil. Ini dapat digunakan untuk pengarsipan, atau untuk situasi lain yang memerlukan ukuran penyimpanan yang lebih kecil dan dapat memberikan lebih banyak waktu untuk penyimpanan dan pengambilan.

Untuk informasi selengkapnya tentang pemadatan, lihat Kompresi Data.

XML_COMPRESSION

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

Menentukan opsi pemadatan XML untuk indeks yang ditentukan yang berisi satu atau beberapa kolom tipe data xml . Opsinya meliputi:

AKTIF
Indeks atau partisi yang ditentukan dikompresi dengan menggunakan pemadatan XML.

TIDAK AKTIF
Indeks atau partisi yang ditentukan tidak dikompresi.

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

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

Catatan

XML_COMPRESSION hanya tersedia mulai SQL Server 2022 (16.x), dan Pratinjau Database Azure SQL.

<partition_number_expression> dapat ditentukan dengan cara berikut:

  • Berikan nomor untuk partisi, misalnya: ON PARTITIONS (2).
  • Berikan nomor partisi untuk beberapa partisi individual yang dipisahkan oleh koma, misalnya: ON PARTITIONS (1, 5).
  • Sediakan rentang dan partisi individual: 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 opsi DATA_COMPRESSION lebih dari sekali, misalnya:

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

Anda juga dapat menentukan opsi XML_COMPRESSION lebih dari sekali, misalnya:

REBUILD WITH
(
  XML_COMPRESSION = OFF ON PARTITIONS (1),
  XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
  XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

ONLINE = { ON | OFF } <sebagaimana berlaku untuk single_partition_rebuild_index_option>

Menentukan apakah indeks atau partisi indeks dari tabel yang mendasar dapat dibangun kembali secara online atau offline. Jika REBUILD ... ONLINE = ON dilakukan, data dalam tabel ini tersedia untuk kueri dan modifikasi data selama operasi indeks. Defaultnya adalah NONAKTIF.

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. Kunci Stabilitas Skema (Sch-S) pada tabel diperlukan saat memulai pembangunan ulang indeks, dan kunci Modifikasi Skema (Sch-M) diperlukan pada tabel di akhir pembangunan ulang indeks online. Meskipun kedua kunci metadata berdurasi pendek, kunci Sch-M terutama 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 low_priority_lock_wait opsi, lihat WAIT_AT_LOW_PRIORITY dengan operasi indeks online.

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

MELANJUTKAN

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2017 (14.x)) dan Database Azure SQL

Lanjutkan operasi indeks yang dijeda secara manual atau karena kegagalan.

MAX_DURATION digunakan dengan RESUMABLE = ON

Waktu (nilai bilangan bulat yang ditentukan dalam menit) bahwa operasi indeks online yang dapat dilanjutkan dijalankan setelah dilanjutkan. Setelah waktu berakhir, operasi yang dapat dilanjutkan dijeda jika masih berjalan.

WAIT_AT_LOW_PRIORITY digunakan dengan RESUMABLE = ON dan ONLINE = ON.

Melanjutkan pembangunan ulang indeks online setelah jeda harus menunggu operasi pemblokiran pada tabel ini. WAIT_AT_LOW_PRIORITY menunjukkan bahwa operasi pembangunan ulang indeks online akan menunggu kunci prioritas rendah, memungkinkan operasi lain untuk melanjutkan saat operasi build indeks online sedang menunggu. WAIT_AT_LOW_PRIORITY Menghilangkan opsi setara dengan WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). Untuk informasi selengkapnya, lihat WAIT_AT_LOW_PRIORITY.

JEDA

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2017 (14.x)) dan Database Azure SQL

Menjeda operasi pembangunan ulang indeks online yang dapat diulang.

MEMBATALKAN

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2017 (14.x)) dan Database Azure SQL

Batalkan operasi indeks yang sedang berjalan atau dijeda yang dinyatakan dapat dilanjutkan. Anda harus secara eksplisit menjalankan ABORT perintah untuk mengakhiri operasi pembangunan ulang indeks yang dapat diulang. Kegagalan atau jeda operasi indeks yang dapat diulang tidak mengakhiri eksekusinya; sebaliknya, ia meninggalkan operasi dalam status jeda yang tidak terbatas.

Keterangan

ALTER INDEX tidak dapat digunakan untuk mempartisi ulang indeks atau memindahkannya ke grup file yang berbeda. Pernyataan ini tidak dapat digunakan untuk mengubah definisi indeks, seperti menambahkan atau menghapus kolom atau mengubah urutan kolom. Gunakan CREATE INDEX dengan DROP_EXISTING klausa untuk melakukan operasi ini.

Saat opsi tidak ditentukan secara eksplisit, pengaturan saat ini diterapkan. Misalnya, jika FILLFACTOR pengaturan tidak ditentukan dalam REBUILD klausul , nilai faktor pengisian yang disimpan dalam katalog sistem akan digunakan selama proses pembangunan ulang. Untuk melihat pengaturan opsi indeks saat ini, gunakan sys.indexes.

Nilai untuk ONLINE, MAXDOP, dan SORT_IN_TEMPDB tidak disimpan dalam katalog sistem. Kecuali ditentukan dalam pernyataan indeks, nilai default untuk opsi digunakan.

Pada komputer multiprosesor, seperti halnya kueri lain, ALTER INDEX REBUILD secara otomatis menggunakan lebih banyak prosesor untuk melakukan operasi pemindaian dan pengurutan yang terkait dengan modifikasi indeks. Saat Anda menjalankan ALTER INDEX REORGANIZE, dengan atau tanpa LOB_COMPACTION, tingkat maksimum nilai paralelisme adalah operasi utas tunggal. Untuk informasi selengkapnya, lihat Mengonfigurasi Operasi Indeks Paralel.

Penting

Indeks tidak dapat diatur ulang atau dibangun ulang jika grup file tempat indeks berada offline atau diatur ke baca-saja. Ketika kata kunci ALL ditentukan dan satu atau beberapa indeks berada dalam grup file offline atau baca-saja, pernyataan gagal.

Membangun ulang indeks

Membangun ulang indeks turun dan membuat ulang indeks. Ini menghapus fragmentasi, merebut kembali ruang disk dengan memampatkan halaman berdasarkan pengaturan faktor pengisian yang ditentukan atau yang ada, dan menyusun ulang baris indeks di halaman yang berdekatan. Ketika ALL ditentukan, semua indeks pada tabel dihilangkan dan dibangun kembali dalam satu transaksi. Batasan kunci asing tidak harus dihilangkan terlebih dahulu. Ketika indeks dengan tingkat 128 atau lebih dibangun kembali, Mesin Database menangguhkan dealokasi halaman aktual, dan kunci terkaitnya, sampai setelah transaksi dilakukan.

Untuk informasi selengkapnya, lihat Mengatur Ulang dan Membangun Ulang Indeks.

Mengatur ulang indeks

Mengatur ulang indeks menggunakan sumber daya sistem minimal. Ini mendefrori tingkat daun indeks berkluster dan non-kluster pada tabel dan tampilan dengan menyusun ulang halaman tingkat daun secara fisik agar sesuai dengan urutan node daun yang logis, kiri ke kanan. Mengatur ulang juga memampatkan halaman indeks. Pemadatan didasarkan pada nilai faktor pengisian yang ada.

Ketika ALL ditentukan, indeks relasional, baik terkluster maupun non-kluster, dan indeks XML pada tabel diatur ulang. Beberapa batasan berlaku saat menentukan ALL, lihat definisi untuk ALL di bagian Argumen di artikel ini.

Untuk informasi selengkapnya, lihat Mengatur Ulang dan Membangun Ulang Indeks.

Penting

Untuk tabel Azure Synapse Analytics dengan indeks penyimpan kolom berkluster yang diurutkan, ALTER INDEX REORGANIZE tidak mengurutkan ulang data. Untuk menggunakan data ALTER INDEX REBUILD.

Menonaktifkan indeks

Menonaktifkan indeks mencegah akses pengguna ke indeks, dan untuk indeks berkluster, ke data tabel yang mendasar. Definisi indeks tetap berada dalam katalog sistem. Menonaktifkan indeks non-kluster atau indeks berkluster pada tampilan akan menghapus data indeks secara fisik. Menonaktifkan indeks berkluster mencegah akses ke data, tetapi data tetap tidak dipertahankan di pohon B hingga indeks dihilangkan atau dibangun kembali. Untuk menampilkan status indeks yang diaktifkan atau dinonaktifkan, kueri is_disabled kolom dalam sys.indexes tampilan katalog.

Catatan

SQL Server dokumentasi menggunakan istilah pohon B umumnya mengacu pada indeks. Dalam indeks rowstore, SQL Server mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau penyimpanan data dalam memori. Tinjau Panduan Arsitektur dan Desain Indeks SQL Server untuk detailnya.

Jika tabel berada dalam publikasi replikasi transaksional, Anda tidak dapat menonaktifkan indeks apa pun yang terkait dengan kolom kunci utama. Indeks ini diperlukan oleh replikasi. Untuk menonaktifkan indeks, Anda harus terlebih dahulu menghilangkan tabel dari publikasi. Untuk informasi selengkapnya, lihat Menerbitkan Data dan Objek Database.

ALTER INDEX REBUILD Gunakan pernyataan atau CREATE INDEX WITH DROP_EXISTING pernyataan untuk mengaktifkan indeks. Membangun kembali indeks berkluster yang dinonaktifkan tidak dapat dilakukan dengan opsi ONLINE yang diatur ke AKTIF. Untuk informasi selengkapnya, lihat Menonaktifkan Indeks dan Batasan.

Opsi pengaturan

Anda dapat mengatur opsi ALLOW_ROW_LOCKS, , ALLOW_PAGE_LOCKSOPTIMIZE_FOR_SEQUENTIAL_KEY, IGNORE_DUP_KEY dan STATISTICS_NORECOMPUTE untuk indeks tertentu tanpa membangun kembali atau mengatur ulang indeks tersebut. Nilai yang dimodifikasi segera diterapkan ke indeks. Untuk melihat pengaturan ini, gunakan sys.indexes. Untuk informasi selengkapnya, lihat Mengatur Opsi Indeks.

Opsi penguncian baris dan halaman

Saat ALLOW_ROW_LOCKS = ON dan ALLOW_PAGE_LOCK = ON, kunci tingkat baris, tingkat halaman, dan tingkat tabel diizinkan saat Anda mengakses indeks. Mesin Database memilih kunci yang sesuai dan dapat meningkatkan kunci dari baris atau kunci halaman ke kunci tabel.

Ketika ALLOW_ROW_LOCKS = OFF dan ALLOW_PAGE_LOCK = OFF, hanya kunci tingkat tabel yang diizinkan saat Anda mengakses indeks.

Jika ALL ditentukan saat opsi kunci baris atau halaman diatur, pengaturan diterapkan ke semua indeks. Saat tabel yang mendasar adalah tumpukan, pengaturan diterapkan dengan cara berikut:

Opsi Detail
ALLOW_ROW_LOCKS = AKTIF atau NONAKTIF Ke tumpukan dan indeks nonkluster terkait.
ALLOW_PAGE_LOCKS = AKTIF Ke tumpukan dan indeks nonkluster terkait.
ALLOW_PAGE_LOCKS = NONAKTIF Sepenuhnya ke indeks nonkluster. Ini berarti bahwa semua kunci halaman tidak diizinkan pada indeks nonkluster. Pada heap, hanya kunci bersama (S), pembaruan (U) dan eksklusif (X) untuk halaman yang tidak diizinkan. Mesin Database masih dapat memperoleh kunci halaman niat (IS, IU atau IX) untuk tujuan internal.

Operasi indeks online

Saat membangun ulang indeks dan opsi ONLINE diatur ke AKTIF, objek yang mendasarinya, tabel dan indeks terkait, tersedia untuk kueri dan modifikasi data. Anda juga dapat membangun kembali secara online sebagian indeks yang berada di satu partisi. Kunci tabel eksklusif hanya ditahan untuk waktu yang sangat singkat selama proses perubahan.

Mengatur ulang indeks selalu dilakukan secara online. Proses ini tidak menahan kunci jangka panjang dan, oleh karena itu, tidak memblokir kueri atau pembaruan yang sedang berjalan.

Anda dapat melakukan operasi indeks online bersamaan pada tabel atau partisi tabel yang sama hanya saat melakukan hal berikut:

  • Membuat beberapa indeks nonkluster.
  • Mengatur ulang indeks yang berbeda pada tabel yang sama.
  • Mengatur ulang indeks yang berbeda saat membangun kembali indeks yang tidak tumpang tindih pada tabel yang sama.

Semua operasi indeks online lainnya yang dilakukan pada saat yang sama gagal. Misalnya, Anda tidak dapat membangun kembali dua indeks atau lebih pada tabel yang sama secara bersamaan, atau membuat indeks baru saat membangun kembali indeks yang ada pada tabel yang sama.

Untuk informasi selengkapnya, lihat Melakukan Operasi Indeks Online.

Operasi indeks yang dapat diulang

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2017 (14.x)) dan Database Azure SQL

Pembangunan ulang indeks online ditentukan sebagai dapat diulang menggunakan RESUMABLE = ON opsi .

  • Opsi RESUMABLE ini tidak bertahan dalam metadata untuk indeks tertentu dan hanya berlaku untuk durasi pernyataan DDL saat ini. Oleh karena itu, RESUMABLE = ON klausul harus ditentukan secara eksplisit untuk memungkinkan kelanjutan kembali.

  • MAX_DURATION opsi didukung untuk RESUMABLE = ON opsi atau low_priority_lock_wait opsi .

    • MAX_DURATION untuk opsi RESUMABLE menentukan interval waktu untuk indeks yang sedang dibangun kembali. Setelah waktu ini digunakan, pembangunan ulang indeks dijeda atau menyelesaikan eksekusinya. Pengguna memutuskan kapan pembangunan ulang untuk indeks yang dijeda dapat dilanjutkan. Waktu (dalam menit) harus MAX_DURATION lebih besar dari 0 menit dan kurang atau sama dengan satu minggu (7 * 24 * 60 = 10080 menit). Memiliki jeda panjang untuk operasi indeks dapat memengaruhi performa DML pada tabel tertentu serta kapasitas disk database karena keduanya mengindeks yang asli dan yang baru dibuat memerlukan ruang disk dan perlu diperbarui selama operasi DML. Jika MAX_DURATION opsi dihilangkan, operasi indeks akan berlanjut hingga selesai atau sampai kegagalan terjadi.
    • Opsi low_priority_lock_wait argumen memungkinkan Anda memutuskan bagaimana operasi indeks dapat dilanjutkan ketika diblokir pada kunci Sch-M.
  • Menjalankan kembali pernyataan asli ALTER INDEX REBUILD dengan parameter yang sama melanjutkan operasi pembangunan ulang indeks yang dijeda. Anda juga dapat melanjutkan operasi pembangunan ulang indeks yang dijeda dengan menjalankan ALTER INDEX RESUME pernyataan .

  • Opsi SORT_IN_TEMPDB = ON ini tidak didukung untuk indeks yang dapat diulang

  • Perintah DDL dengan RESUMABLE = ON tidak dapat dijalankan di dalam transaksi eksplisit (tidak dapat menjadi bagian BEGIN TRAN ... COMMIT dari blok).

  • Hanya operasi indeks yang dijeda yang dapat dilanjutkan.

  • Saat melanjutkan operasi indeks yang dijeda, Anda dapat mengubah nilai MAXDOP menjadi nilai baru. Jika MAXDOP tidak ditentukan saat melanjutkan operasi indeks yang dijeda, nilai MAXDOP terakhir akan diambil. JIKA opsi MAXDOP tidak ditentukan sama sekali untuk operasi pembangunan ulang indeks, nilai default akan diambil.

  • Untuk segera menjeda operasi indeks, Anda dapat menghentikan perintah yang sedang berlangsung (Ctrl-C) atau Anda dapat menjalankan ALTER INDEX PAUSE perintah atau KILL <session_id> perintah . Setelah perintah dijeda, perintah dapat dilanjutkan menggunakan RESUME opsi .

  • Perintah ABORT mematikan sesi yang menghosting indeks asli membangun kembali dan membatalkan operasi indeks

  • Tidak ada sumber daya tambahan yang diperlukan untuk pembangunan ulang indeks yang dapat dilanjutkan kecuali untuk

    • Ruang tambahan yang diperlukan untuk menjaga indeks tetap dibangun, termasuk waktu ketika indeks sedang dijeda
    • Status DDL mencegah modifikasi DDL
  • Pembersihan hantu akan berjalan selama fase jeda indeks, tetapi akan dijeda selama eksekusi indeks. Fungsionalitas berikut dinonaktifkan untuk operasi pembangunan ulang indeks yang dapat dilanjutkan

    • Membangun kembali indeks yang dinonaktifkan tidak didukung dengan RESUMABLE = ON
    • perintah ALTER INDEX REBUILD ALL
    • ALTER TABLE menggunakan pembangunan ulang indeks
    • Perintah DDL dengan RESUMABLE = ON tidak dapat dijalankan di dalam transaksi eksplisit (tidak dapat menjadi bagian dari BEGIN TRAN ... COMMIT blok)
    • Membangun kembali indeks yang memiliki kolom komputasi atau TIMESTAMP sebagai kolom kunci.
  • Jika tabel dasar berisi kolom LOB yang dapat dilanjutkan pembangunan ulang indeks berkluster memerlukan kunci Sch-M di Awal operasi ini

Catatan

Perintah DDL berjalan hingga selesai, dijeda, atau gagal. Jika perintah dijeda, kesalahan akan dikeluarkan yang menunjukkan bahwa operasi dijeda dan pembuatan indeks tidak selesai. Informasi lebih lanjut tentang status indeks saat ini dapat diperoleh dari sys.index_resumable_operations. Seperti sebelumnya jika terjadi kegagalan, kesalahan juga akan dikeluarkan.

WAIT_AT_LOW_PRIORITY dengan operasi indeks online

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2014 (12.x)) dan Database Azure SQL

Sintaks low_priority_lock_wait memungkinkan untuk menentukan WAIT_AT_LOW_PRIORITY perilaku. WAIT_AT_LOW_PRIORITY hanya dapat digunakan dengan ONLINE = ON .

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 dijalankan, indeks ini memblokir semua transaksi baru yang siap untuk memulai eksekusi pada tabel ini. Meskipun durasi kunci untuk pembangunan ulang indeks online sangat singkat, menunggu semua transaksi terbuka pada tabel tertentu untuk menyelesaikan dan memblokir transaksi baru untuk memulai, mungkin secara signifikan memengaruhi throughput, menyebabkan beban kerja melambat atau waktu habis, dan secara signifikan membatasi akses ke tabel yang mendasar.

Opsi ini WAIT_AT_LOW_PRIORITY memungkinkan DBA untuk mengelola kunci Schema Stability (Sch-S) dan kunci Modifikasi Skema (Sch-M) yang diperlukan untuk pembangunan kembali indeks online dan memungkinkan mereka untuk memilih salah satu dari dua opsi. Dalam kedua kasus, jika selama waktu MAX_DURATION = n [minutes]tunggu , tidak ada aktivitas pemblokiran, pembangunan ulang indeks online segera dijalankan tanpa menunggu dan pernyataan DDL selesai.

WAIT_AT_LOW_PRIORITY menunjukkan bahwa operasi pembangunan ulang indeks online akan menunggu kunci prioritas rendah, memungkinkan operasi lain untuk melanjutkan saat operasi build indeks online sedang menunggu. 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) kunci pembangunan ulang indeks online akan menunggu dengan prioritas rendah saat menjalankan perintah DDL. Jika operasi diblokir untuk waktu tersebut MAX_DURATION , tindakan yang ditentukan ABORT_AFTER_WAIT akan dijalankan. MAX_DURATION waktu selalu dalam hitungan menit, dan kata MINUTES dapat dihilangkan.

ABORT_AFTER_WAIT = [ NONE | SELF | BLOCKERS ]

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

DIRI
Keluar dari operasi DDL pembangunan ulang indeks online yang saat ini sedang dijalankan tanpa mengambil tindakan apa pun. Opsi SELF tidak dapat digunakan dengan MAX_DURATION 0.

BLOCKER
Matikan semua transaksi pengguna yang memblokir operasi DDL pembangunan ulang indeks online sehingga operasi dapat dilanjutkan. Opsi BLOCKERS mengharuskan login memiliki ALTER ANY CONNECTION izin.

Pembatasan indeks spasial

Saat Anda membangun kembali indeks spasial, tabel pengguna yang mendasar tidak tersedia selama operasi indeks karena indeks spasial memegang kunci skema.

Batasan KUNCI PRIMER dalam tabel pengguna tidak dapat dimodifikasi saat indeks spasial ditentukan pada kolom tabel tersebut. Untuk mengubah batasan KUNCI PRIMER, pertama-tama hilangkan setiap indeks spasial tabel. Setelah memodifikasi batasan KUNCI PRIMER, Anda dapat membuat ulang setiap indeks spasial.

Dalam operasi pembangunan ulang partisi tunggal, Anda tidak dapat menentukan indeks spasial apa pun. Namun, Anda dapat menentukan indeks spasial dalam pembangunan ulang partisi lengkap.

Untuk mengubah opsi yang khusus untuk indeks spasial, seperti BOUNDING_BOX atau GRID, Anda dapat menggunakan CREATE SPATIAL INDEX pernyataan yang menentukan , atau menghilangkan indeks spasial DROP_EXISTING = ONdan membuat yang baru. Misalnya, lihat MEMBUAT INDEKS SPASIAL (Transact-SQL).

Pemadatan data

Untuk informasi selengkapnya tentang pemadatan data, lihat Pemadatan Data.

Untuk mengevaluasi bagaimana mengubah pemadatan HALAMAN dan BARIS akan memengaruhi tabel, indeks, atau partisi, gunakan prosedur tersimpan sp_estimate_data_compression_savings .

Pembatasan berikut berlaku untuk indeks yang dipartisi:

  • Saat Anda menggunakan ALTER INDEX ALL ..., Anda tidak dapat mengubah pengaturan kompresi partisi tunggal jika tabel memiliki indeks yang tidak ditandatangani.
  • Sintaks membangun ALTER INDEX <index> ... REBUILD PARTITION ... kembali partisi indeks yang ditentukan.
  • Sintaks membangun ALTER INDEX <index> ... REBUILD WITH ... kembali semua partisi indeks.

Statistik

Saat Anda menjalankan ALTER INDEX ALL ... pada tabel, hanya statistik yang terkait dengan indeks yang diperbarui. Statistik otomatis atau manual yang dibuat pada tabel (bukan indeks) tidak diperbarui.

Izin

Untuk menjalankan ALTER INDEX, minimal, ALTER izin pada tabel atau tampilan diperlukan.

Catatan Versi

  • SQL Database tidak menggunakan opsi grup file dan aliran file.
  • Indeks penyimpan kolom tidak tersedia sebelum SQL Server 2012 (11.x).
  • Operasi indeks yang dapat dilanjutkan tersedia dimulai dengan SQL Server 2017 (14.x) dan Database Azure SQL.

Contoh sintaks dasar

ALTER INDEX index1 ON table1 REBUILD;

ALTER INDEX ALL ON table1 REBUILD;

ALTER INDEX ALL ON dbo.table1 REBUILD;

Contoh: Indeks Penyimpan Kolom

Contoh-contoh ini berlaku untuk indeks penyimpan kolom.

J. MENGATUR ULANG demo

Contoh ini menunjukkan cara ALTER INDEX REORGANIZE kerja perintah. Ini membuat tabel yang memiliki beberapa grup baris, lalu menunjukkan cara REORGANIZE menggabungkan grup baris.

-- Create a database
CREATE DATABASE [ columnstore ];
GO

-- Create a rowstore staging table
CREATE TABLE [ staging ] (
     AccountKey              int NOT NULL,
     AccountDescription      nvarchar (50),
     AccountType             nvarchar(50),
     AccountCodeAlternateKey     int
     )

-- Insert 10 million rows into the staging table.
DECLARE @loop int
DECLARE @AccountDescription varchar(50)
DECLARE @AccountKey int
DECLARE @AccountType varchar(50)
DECLARE @AccountCode int

SELECT @loop = 0
BEGIN TRAN
    WHILE (@loop < 300000)
      BEGIN
        SELECT @AccountKey = CAST (RAND()*10000000 as int);
        SELECT @AccountDescription = 'accountdesc ' + CONVERT(varchar(20), @AccountKey);
        SELECT @AccountType = 'AccountType ' + CONVERT(varchar(20), @AccountKey);
        SELECT @AccountCode =  CAST (RAND()*10000000 as int);

        INSERT INTO  staging VALUES (@AccountKey, @AccountDescription, @AccountType, @AccountCode);

        SELECT @loop = @loop + 1;
    END
COMMIT

-- Create a table for the clustered columnstore index

CREATE TABLE cci_target (
     AccountKey              int NOT NULL,
     AccountDescription      nvarchar (50),
     AccountType             nvarchar(50),
     AccountCodeAlternateKey int
     )

-- Convert the table to a clustered columnstore index named inxcci_cci_target;
CREATE CLUSTERED COLUMNSTORE INDEX idxcci_cci_target ON cci_target;

Gunakan opsi TABLOCK untuk menyisipkan baris secara paralel. Dimulai dengan SQL Server 2016 (13.x), INSERT INTO operasi dapat berjalan secara paralel saat TABLOCK digunakan.

INSERT INTO cci_target WITH (TABLOCK)
SELECT TOP 300000 * FROM staging;

Jalankan perintah ini untuk melihat grup baris delta TERBUKA. Jumlah grup baris tergantung pada tingkat paralelisme.

SELECT *
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id  = object_id('cci_target');

Jalankan perintah ini untuk memaksa semua grup baris CLOSED dan OPEN ke dalam columnstore.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Jalankan perintah ini lagi dan Anda akan melihat bahwa grup baris yang lebih kecil digabungkan menjadi satu grup baris terkompresi.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

B. Memadatkan grup baris delta CLOSED ke dalam penyimpan kolom

Contoh ini menggunakan REORGANIZE opsi untuk memadatkan setiap grup baris delta CLOSED ke dalam penyimpan kolom sebagai grup baris terkompresi. Ini tidak diperlukan, tetapi berguna ketika tuple-mover tidak memadatkan grup baris CLOSED dengan cukup cepat.

Anda dapat menjalankan kedua contoh dalam AdventureWorksDW database sampel.

Sampel ini akan menjalankan REORGANIZE pada semua partisi.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

Sampel ini akan menjalankan REORGANIZE pada partisi tertentu.

-- REORGANIZE a specific partition
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0;

C. Memadatkan semua grup baris delta TERBUKA DAN TERTUTUP ke dalam penyimpan kolom

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2016 (13.x)) dan Database Azure SQL

Perintah REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = ON ) memadatkan setiap grup baris delta OPEN dan CLOSED ke dalam penyimpan kolom sebagai grup baris terkompresi. Ini mengoreksi deltastore dan memaksa semua baris untuk dikompresi ke dalam penyimpan kolom. Ini berguna terutama setelah melakukan banyak operasi penyisipan karena operasi ini menyimpan baris dalam satu atau beberapa grup baris delta.

REORGANIZE menggabungkan grup baris untuk mengisi grup baris hingga jumlah baris <maksimum = 1.024.576. Oleh karena itu, ketika Anda memadatkan semua grup baris OPEN dan CLOSED, Anda tidak akan berakhir dengan banyak grup baris terkompresi yang hanya memiliki beberapa baris di dalamnya. Anda ingin grup baris penuh mungkin untuk mengurangi ukuran terkompresi dan meningkatkan performa kueri.

Contoh berikut menggunakan AdventureWorksDW2016 database.

Contoh ini memindahkan semua grup baris delta OPEN dan CLOSED ke dalam indeks penyimpan kolom.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Contoh ini memindahkan semua grup baris delta OPEN AND CLOSED ke dalam indeks penyimpan kolom untuk partisi tertentu.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0 WITH (COMPRESS_ALL_ROW_GROUPS = ON);

D. Defragmentasi indeks penyimpan kolom secara online

Tidak berlaku untuk: SQL Server 2012 (11.x) dan SQL Server 2014 (12.x).

Dimulai dengan SQL Server 2016 (13.x), REORGANIZE melakukan lebih dari memadatkan grup baris delta ke dalam penyimpan kolom. Ini juga melakukan defragmentasi online. Pertama, ini mengurangi ukuran penyimpan kolom dengan menghapus baris yang dihapus secara fisik ketika 10% atau lebih baris dalam grup baris telah dihapus. Kemudian, ini menggabungkan grup baris bersama-sama untuk membentuk grup baris yang lebih besar yang memiliki hingga maksimum 1.024.576 baris per grup baris. Semua grup baris yang diubah dikompresi ulang.

Catatan

Dimulai dengan SQL Server 2016 (13.x), membangun kembali indeks penyimpan kolom tidak lagi diperlukan dalam sebagian besar situasi karena REORGANIZE secara fisik menghapus baris yang dihapus dan menggabungkan grup baris. Opsi COMPRESS_ALL_ROW_GROUPS memaksa semua grup baris delta TERBUKA atau TERTUTUP ke dalam penyimpan kolom yang sebelumnya hanya dapat dilakukan dengan pembangunan ulang. REORGANIZE online dan terjadi di latar belakang sehingga kueri dapat dilanjutkan saat operasi terjadi.

Contoh berikut melakukan REORGANIZE untuk mendefinisikan indeks dengan menghapus baris yang telah dihapus secara logis dari tabel, dan menggabungkan grup baris.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

E. Membangun kembali indeks penyimpan kolom berkluster secara offline

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2012 (11.x))

Tip

Dimulai dengan SQL Server 2016 (13.x) dan di Azure SQL Database, sebaiknya gunakan ALTER INDEX REORGANIZE alih-alih ALTER INDEX REBUILD untuk indeks penyimpan kolom.

Catatan

Pada SQL Server 2012 (11.x) dan SQL Server 2014 (12.x), REORGANIZE hanya digunakan untuk memadatkan grup baris TERTUTUP ke dalam columnstore. Satu-satunya cara untuk melakukan operasi defragmentasi dan untuk memaksa semua grup baris delta ke dalam penyimpan kolom adalah dengan membangun kembali indeks.

Contoh ini menunjukkan cara membangun kembali indeks penyimpan kolom berkluster dan memaksa semua grup baris delta ke dalam penyimpan kolom. Langkah pertama ini menyiapkan tabel FactInternetSales2 dalam AdventureWorksDW database dengan indeks penyimpan kolom berkluster, dan menyisipkan data dari empat kolom pertama.

CREATE TABLE dbo.FactInternetSales2 (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales2
ON dbo.FactInternetSales2;

INSERT INTO dbo.FactInternetSales2
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.FactInternetSales;

SELECT * FROM sys.column_store_row_groups;

Hasilnya memperlihatkan satu grup baris TERBUKA, yang berarti SQL Server akan menunggu lebih banyak baris ditambahkan sebelum menutup grup baris dan memindahkan data ke penyimpan kolom. Pernyataan berikutnya ini membangun kembali indeks penyimpan kolom berkluster, yang memaksa semua baris ke dalam penyimpan kolom.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REBUILD;
SELECT * FROM sys.column_store_row_groups;

Hasil SELECT pernyataan menunjukkan grup baris DIKOMPRESI, yang berarti segmen kolom grup baris sekarang dikompresi dan disimpan di penyimpan kolom.

F. Membangun kembali partisi indeks penyimpan kolom berkluster secara offline

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2012 (11.x))

Untuk membangun kembali partisi indeks penyimpan kolom berkluster besar, gunakan ALTER INDEX REBUILD dengan opsi partisi. Contoh ini membangun kembali partisi 12. Dimulai dengan SQL Server 2016 (13.x), sebaiknya ganti REBUILD dengan REORGANIZE.

ALTER INDEX cci_fact3
ON fact3
REBUILD PARTITION = 12;

G. Mengubah indeks penyimpan kolom berkluster untuk menggunakan pemadatan arsip

Tidak berlaku untuk: SQL Server 2012 (11.x)

Anda dapat memilih untuk mengurangi ukuran indeks penyimpan kolom berkluster lebih jauh dengan menggunakan opsi kompresi data COLUMNSTORE_ARCHIVE. Ini praktis untuk data lama yang ingin Anda simpan di penyimpanan yang lebih murah. Sebaiknya hanya gunakan ini pada data yang tidak sering diakses karena dekompresi lebih lambat daripada dengan kompresi COLUMNSTORE normal.

Contoh berikut membangun kembali indeks penyimpan kolom berkluster untuk menggunakan kompresi arsip, lalu menunjukkan cara menghapus kompresi arsip. Hasil akhir hanya akan menggunakan kompresi penyimpan kolom.

Pertama, siapkan contoh dengan membuat tabel dengan indeks penyimpan kolom berkluster. Kemudian, kompres tabel lebih lanjut dengan menggunakan kompresi arsip.

--Prepare the example by creating a table with a clustered columnstore index.
CREATE TABLE SimpleTable (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL
);

CREATE CLUSTERED INDEX cci_SimpleTable ON SimpleTable (ProductKey);

CREATE CLUSTERED COLUMNSTORE INDEX cci_SimpleTable
ON SimpleTable
WITH (DROP_EXISTING = ON);

--Compress the table further by using archival compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

Sampel ini menghapus kompresi arsip, dan hanya menggunakan kompresi penyimpan kolom.

ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE);
GO

Contoh: Indeks rowstore

J. Menyusun ulang indeks

Contoh berikut membangun kembali satu indeks pada Employee tabel dalam AdventureWorks2012 database.

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

B. Membangun ulang semua indeks pada tabel dan menentukan opsi

Contoh berikut menentukan kata kunci ALL. Ini membangun kembali semua indeks yang terkait dengan tabel Production.Product dalam AdventureWorks2012 database. Tiga opsi ditentukan.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

Contoh berikut menambahkan opsi ONLINE termasuk opsi kunci prioritas rendah, dan menambahkan opsi pemadatan baris.

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2014 (12.x)) dan Database Azure SQL

ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
    DATA_COMPRESSION = ROW
);

C. Mengatur ulang indeks dengan pemadatan LOB

Contoh berikut mengatur ulang satu indeks berkluster dalam AdventureWorks2012 database. Karena indeks berisi jenis data LOB di tingkat daun, pernyataan juga memampatkan semua halaman yang berisi data objek besar. Menentukan WITH (LOB_COMPACTION = ON) opsi tidak diperlukan karena nilai defaultnya aktif.

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE WITH (LOB_COMPACTION = ON);

D. Mengatur opsi pada indeks

Contoh berikut menetapkan beberapa opsi pada indeks AK_SalesOrderHeader_SalesOrderNumber dalam AdventureWorks2012 database.

ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

E. Menonaktifkan indeks

Contoh berikut menonaktifkan indeks nonkluster pada Employee tabel dalam AdventureWorks2012 database.

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F. Menonaktifkan batasan

Contoh berikut menonaktifkan batasan KUNCI PRIMER dengan menonaktifkan indeks KUNCI PRIMER dalam AdventureWorks2012 database. Batasan KUNCI ASING pada tabel yang mendasar secara otomatis dinonaktifkan dan pesan peringatan ditampilkan.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department DISABLE;

Tataan hasil mengembalikan pesan peringatan ini.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'
on table 'EmployeeDepartmentHistory' referencing table 'Department'
was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. Aktifkan batasan

Contoh berikut mengaktifkan batasan KUNCI PRIMER dan KUNCI ASING yang dinonaktifkan di Contoh F.

Batasan KUNCI PRIMER diaktifkan dengan membangun kembali indeks KUNCI PRIMER.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;

Batasan KUNCI ASING kemudian diaktifkan.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. Membangun kembali indeks yang dipartisi

Contoh berikut membangun kembali partisi tunggal, nomor 5partisi , dari indeks IX_TransactionHistory_TransactionDate yang dipartisi dalam AdventureWorks2012 database. Partisi 5 dibangun kembali dengan ONLINE=ON dan waktu tunggu 10 menit untuk kunci prioritas rendah berlaku secara terpisah untuk setiap kunci yang diperoleh oleh operasi pembangunan ulang indeks. Jika selama waktu ini kunci tidak dapat diperoleh untuk menyelesaikan pembangunan ulang indeks, pernyataan operasi pembangunan ulang itu sendiri dibatalkan, karena ABORT_AFTER_WAIT = SELF.

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2014 (12.x)) dan Database Azure SQL

-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5
   WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF)));
GO

i. Mengubah pengaturan pemadatan indeks

Contoh berikut membangun kembali indeks pada tabel rowstore yang tidak dipartisi.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO

j. Mengubah pengaturan indeks dengan pemadatan XML

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

Contoh berikut membangun kembali indeks pada tabel rowstore yang tidak dipartisi.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (XML_COMPRESSION = ON);
GO

Untuk contoh kompresi data lainnya, lihat Kompresi Data.

K. Pembangunan ulang indeks yang dapat dilanjutkan online

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2017 (14.x)) dan Database Azure SQL

Contoh berikut menunjukkan cara menggunakan pembangunan ulang indeks online yang dapat dilanjutkan.

  1. Jalankan pembangunan ulang indeks online sebagai operasi yang dapat diulang dengan MAXDOP = 1.

    ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON) ;
    
  2. Menjalankan perintah yang sama lagi (lihat di atas) setelah operasi indeks dijeda, melanjutkan secara otomatis operasi pembangunan ulang indeks.

  3. Jalankan pembangunan ulang indeks online sebagai operasi yang dapat diulang dengan MAX_DURATION diatur ke 240 menit.

    ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240) ;
    
  4. Jeda pembangunan ulang indeks online yang dapat diandalkan yang sedang berjalan.

    ALTER INDEX test_idx on test_table PAUSE ;
    
  5. Lanjutkan pembangunan ulang indeks online untuk pembangunan ulang indeks yang dijalankan sebagai operasi yang dapat dilanjutkan yang menentukan nilai baru untuk MAXDOP yang diatur ke 4.

    ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP = 4) ;
    
  6. Lanjutkan operasi pembangunan ulang indeks online untuk pembangunan ulang online indeks yang dijalankan sebagai dapat dilanjutkan. Atur MAXDOP ke 2, atur waktu eksekusi untuk indeks yang berjalan sebagai dapat dilanjutkan menjadi 240 menit, dan jika indeks diblokir pada kunci, tunggu 10 menit dan setelah itu matikan semua pemblokir.

       ALTER INDEX test_idx on test_table
          RESUME WITH (MAXDOP = 2, MAX_DURATION = 240 MINUTES,
          WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10, ABORT_AFTER_WAIT = BLOCKERS)) ;
    
  7. Batalkan operasi pembangunan ulang indeks yang dapat dilanjutkan yang sedang berjalan atau dijeda.

    ALTER INDEX test_idx on test_table ABORT ;
    

Lihat juga