BUAT INDEKS (Transact-SQL)

Artikel
06/06/2023

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

Membuat indeks relasional pada tabel atau tampilan. Juga disebut indeks rowstore karena merupakan indeks pohon B berkluster atau berkluster. Anda dapat membuat indeks rowstore sebelum ada data dalam tabel. Gunakan indeks rowstore untuk meningkatkan performa kueri, terutama saat kueri memilih dari kolom tertentu atau mengharuskan nilai diurutkan dalam urutan tertentu.

Catatan

Dokumentasi menggunakan istilah pohon B umumnya dalam referensi ke indeks. Dalam indeks rowstore, Mesin Database mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau indeks pada tabel yang dioptimalkan memori. Untuk informasi selengkapnya, lihat panduan arsitektur dan desain indeks SQL Server dan Azure SQL.

Azure Synapse Analytics and Analytics Platform System (PDW) saat ini tidak mendukung batasan unik. Contoh apa pun yang mereferensikan batasan unik hanya berlaku untuk SQL Server dan SQL Database.

Untuk informasi tentang panduan desain indeks, lihat Panduan Desain Indeks SQL Server.

Contoh:

Membuat indeks nonclustered pada tabel atau tampilan
```
CREATE INDEX index1 ON schema1.table1 (column1);
```
Membuat indeks berkluster pada tabel dan menggunakan nama 3 bagian untuk tabel
```
CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
```

Membuat indeks non-kluster dengan batasan unik dan menentukan urutan pengurutan

CREATE UNIQUE INDEX index1 ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);

Skenario utama:

Dimulai dengan SQL Server 2016 (13.x) dan SQL Database, Anda dapat menggunakan indeks non-kluster pada indeks penyimpan kolom untuk meningkatkan performa kueri pergudangan data. Untuk informasi selengkapnya, lihat Indeks Penyimpan Kolom - Gudang Data.

Untuk jenis indeks tambahan, lihat:

Konvensi sintaks transact-SQL

Sintaks

Sintaks untuk SQL Server, Azure SQL Database, Azure SQL Managed Instance

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

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

<relational_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 }
  | DROP_EXISTING = { 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 }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
}

<filter_predicate> ::=
    <conjunct> [ AND ] [ ...n ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

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

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

Indeks relasional yang kompatibel mundur

Penting

Struktur sintaks indeks relasional yang kompatibel mundur akan dihapus dalam versi SQL Server yang akan datang. Hindari menggunakan struktur sintaks ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan fitur tersebut. Gunakan struktur sintaks yang ditentukan dalam <relational_index_option> sebagai gantinya.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ]
    table_or_view_name
}

<backward_compatible_index_option> ::=
{
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE
  | DROP_EXISTING
}

Sintaks untuk Azure Synapse Analytics dan Gudang Data Paralel


CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
    [ORDER (column[,...n])]
    [WITH ( DROP_EXISTING = { ON | OFF } )]
[;]


CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
        ( { column [ ASC | DESC ] } [ ,...n ] )
    WITH ( DROP_EXISTING = { ON | OFF } )
[;]

Argumen

UNIQUE

Membuat indeks unik pada tabel atau tampilan. Indeks unik adalah indeks di mana tidak ada dua baris yang diizinkan untuk memiliki nilai kunci indeks yang sama. Indeks berkluster pada tampilan harus unik.

Mesin Database tidak mengizinkan pembuatan indeks unik pada kolom yang sudah menyertakan nilai duplikat, apakah diatur ke AKTIF atau tidak IGNORE_DUP_KEY . Jika ini dicoba, Mesin Database menampilkan pesan kesalahan. Nilai duplikat harus dihapus sebelum indeks unik dapat dibuat pada kolom atau kolom. Kolom yang digunakan dalam indeks unik harus diatur ke NOT NULL, karena beberapa nilai null dianggap duplikat saat indeks unik dibuat.

CLUSTERED

Membuat indeks di mana urutan logis nilai kunci menentukan urutan fisik baris yang sesuai dalam tabel. Bagian bawah, atau daun, tingkat indeks berkluster berisi baris data aktual tabel. Tabel atau tampilan diperbolehkan satu indeks berkluster pada satu waktu.

Tampilan dengan indeks berkluster unik disebut tampilan terindeks. Membuat indeks berkluster unik pada tampilan secara fisik mewujudkan tampilan. Indeks berkluster unik harus dibuat pada tampilan sebelum indeks lain dapat ditentukan pada tampilan yang sama. Untuk informasi selengkapnya, lihat Membuat Tampilan Terindeks.

Buat indeks berkluster sebelum membuat indeks non-kluster apa pun. Indeks non-kluster yang ada pada tabel dibangun kembali saat indeks berkluster dibuat.

Jika CLUSTERED tidak ditentukan, indeks non-kluster dibuat.

Catatan

Karena tingkat daun indeks berkluster dan halaman data sama menurut definisi, membuat indeks berkluster dan menggunakan ON partition_scheme_name klausa atau ON filegroup_name secara efektif memindahkan tabel dari grup file tempat tabel dibuat ke skema partisi atau grup file baru. Sebelum membuat tabel atau indeks pada grup file tertentu, verifikasi grup file mana yang tersedia dan memiliki ruang kosong yang cukup untuk indeks.

Dalam beberapa kasus, membuat indeks berkluster dapat mengaktifkan indeks yang dinonaktifkan sebelumnya. Untuk informasi selengkapnya, lihat Mengaktifkan Indeks dan Batasan dan Menonaktifkan Indeks dan Batasan.

TIDAK TERKLUSTER

Membuat indeks yang menentukan urutan logis tabel. Dengan indeks non-kluster, urutan fisik baris data tidak tergantung pada urutan terindeksnya.

Setiap tabel dapat memiliki hingga 999 indeks non-kluster, terlepas dari bagaimana indeks dibuat: baik secara implisit dengan batasan KUNCI PRIMER dan UNIK, atau secara eksplisit dengan CREATE INDEX.

Untuk tampilan terindeks, indeks non-kluster hanya dapat dibuat pada tampilan yang memiliki indeks berkluster unik yang sudah ditentukan.

Jika tidak ditentukan lain, jenis indeks default tidak di-nonclustered.

index_name

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

column

Kolom atau kolom yang menjadi dasar indeks. Tentukan dua nama kolom atau lebih untuk membuat indeks komposit pada nilai gabungan dalam kolom yang ditentukan. Cantumkan kolom yang akan disertakan dalam indeks komposit, dalam urutan prioritas pengurutan, di dalam tanda kurung setelah table_or_view_name.

Hingga 32 kolom dapat digabungkan ke dalam satu kunci indeks komposit. Semua kolom dalam kunci indeks komposit harus berada dalam tabel atau tampilan yang sama. Ukuran maksimum yang diperbolehkan dari nilai indeks gabungan adalah 900 byte untuk indeks berkluster, atau 1.700 untuk indeks non-kluster. Batasnya adalah 16 kolom dan 900 byte untuk versi sebelum SQL Database dan SQL Server 2016 (13.x).

Kolom yang merupakan tipe data objek besar (LOB) ntext, teks, varchar(max), nvarchar(max), varbinary(max), xml, atau gambar tidak dapat ditentukan sebagai kolom kunci untuk indeks. Selain itu, definisi tampilan tidak dapat menyertakan kolom ntext, teks, atau gambar , meskipun tidak dirujuk dalam CREATE INDEX pernyataan.

Anda dapat membuat indeks pada kolom jenis yang ditentukan pengguna CLR jika jenis mendukung pengurutan biner. Anda juga dapat membuat indeks pada kolom komputasi yang didefinisikan sebagai pemanggilan metode dari kolom jenis yang ditentukan pengguna, selama metode ditandai deterministik dan tidak melakukan operasi akses data. Untuk informasi selengkapnya tentang mengindeks kolom jenis yang ditentukan pengguna CLR, lihat Jenis yang Ditentukan Pengguna CLR.

[ ASC | DESC ]

Menentukan arah pengurutan naik atau turun untuk kolom indeks tertentu. Defaultnya adalah ASC.

INCLUDE (kolom [ ,... n ] )

Menentukan kolom non-kunci yang akan ditambahkan ke tingkat daun indeks non-kluster. Indeks nonclustered bisa unik atau tidak unik.

Nama kolom tidak dapat diulang dalam daftar INCLUDE dan tidak dapat digunakan secara bersamaan sebagai kolom kunci dan non-kunci. Indeks berkluster selalu berisi kolom indeks berkluster jika indeks berkluster ditentukan pada tabel. Untuk informasi selengkapnya, lihat Membuat Indeks dengan Kolom yang Disertakan.

Semua jenis data diizinkan kecuali teks, ntext, dan gambar. Dimulai dengan SQL Server 2012 (11.x) dan Azure SQL Database, jika salah satu kolom non-kunci yang ditentukan adalah jenis data varchar(max), nvarchar(max), atau varbinary(max ), indeks dapat dibangun atau dibangun kembali menggunakan opsi ONLINE.

Kolom komputasi yang deterministik dan tepat atau tidak tepat dapat disertakan kolom. Kolom komputasi yang berasal dari tipe data gambar, ntext, teks, varchar(max), nvarchar(max), varbinary(max), dan xml dapat disertakan dalam kolom non-kunci selama jenis data kolom yang dihitung diizinkan sebagai kolom yang disertakan. Untuk informasi selengkapnya, lihat Indeks pada Kolom Komputasi.

Untuk informasi tentang membuat indeks XML, lihat MEMBUAT INDEKS XML.

WHERE <filter_predicate>

Membuat indeks yang difilter dengan menentukan baris mana yang akan disertakan dalam indeks. Indeks yang difilter harus merupakan indeks non-kluster pada tabel. Membuat statistik yang difilter untuk baris data dalam indeks yang difilter.

Predikat filter menggunakan logika perbandingan sederhana dan tidak dapat mereferensikan kolom komputasi, kolom UDT, kolom jenis data spasial, atau kolom tipe data hierarkiID. Perbandingan menggunakan NULL literal tidak diizinkan dengan operator perbandingan. IS NULL Gunakan operator dan IS NOT NULL sebagai gantinya.

Berikut adalah beberapa contoh predikat filter untuk Production.BillOfMaterials tabel:

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Indeks yang difilter tidak berlaku untuk indeks XML dan indeks teks lengkap. Untuk indeks UNIQUE, hanya baris yang dipilih yang harus memiliki nilai indeks unik. Indeks yang difilter tidak mengizinkan IGNORE_DUP_KEY opsi .

ON partition_scheme_name ( column_name )

Menentukan skema partisi yang menentukan grup file tempat partisi indeks yang dipartisi akan dipetakan. Skema partisi harus ada dalam database dengan menjalankan CREATE PARTITION SCHEME atau ALTER PARTITION SCHEME. column_name menentukan kolom tempat indeks yang dipartisi akan dipartisi. Kolom ini harus cocok dengan tipe data, panjang, dan presisi argumen fungsi partisi yang partition_scheme_name gunakan. column_name tidak dibatasi untuk kolom dalam definisi indeks. Kolom apa pun dalam tabel dasar dapat ditentukan, kecuali saat mempartisi indeks UNIK, column_name harus dipilih dari antara yang digunakan sebagai kunci unik. Pembatasan ini memungkinkan Mesin Database untuk memverifikasi keunikan nilai kunci dalam satu partisi saja.

Catatan

Saat Anda mempartisi indeks berkluster yang tidak unik, Mesin Database secara default menambahkan kolom partisi ke daftar kunci indeks berkluster, jika belum ditentukan. Saat mempartisi indeks non-unik dan tidak berkluster, Mesin Database menambahkan kolom partisi sebagai kolom non-kunci (disertakan) indeks, jika belum ditentukan.

Jika partition_scheme_name atau grup file tidak ditentukan dan tabel dipartisi, indeks ditempatkan dalam skema partisi yang sama, menggunakan kolom partisi yang sama, sebagai tabel yang mendasar.

Catatan

Anda tidak dapat menentukan skema partisi pada indeks XML. Jika tabel dasar dipartisi, indeks XML menggunakan skema partisi yang sama dengan tabel.

Untuk informasi selengkapnya tentang mempartisi indeks, Tabel dan Indeks Yang Dipartisi.

ON filegroup_name

Membuat indeks yang ditentukan pada grup file yang ditentukan. Jika tidak ada lokasi yang ditentukan dan tabel atau tampilan tidak dipartisi, indeks menggunakan grup file yang sama dengan tabel atau tampilan yang mendasar. Grup file harus sudah ada.

AKTIF "default"

Membuat indeks yang ditentukan pada grup file atau skema partisi yang sama dengan tabel atau tampilan.

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

Catatan

"default" tidak menunjukkan grup file default database dalam konteks CREATE INDEX. Ini berbeda dari CREATE TABLE, di mana "default" menemukan tabel pada grup file default database.

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

Menentukan penempatan data FILESTREAM untuk tabel saat indeks berkluster dibuat. Klausa FILESTREAM_ON memungkinkan data FILESTREAM dipindahkan ke grup file ATAU skema partisi FILESTREAM yang berbeda.

filestream_filegroup_name adalah nama grup file FILESTREAM. Grup file harus memiliki satu file yang ditentukan untuk grup file dengan menggunakan pernyataan CREATE DATABASE atau ALTER DATABASE ; jika tidak, kesalahan akan muncul.

Jika tabel dipartisi, FILESTREAM_ON klausul harus disertakan dan harus menentukan skema partisi grup file FILESTREAM yang menggunakan fungsi partisi dan kolom partisi yang sama dengan skema partisi untuk tabel. Jika tidak, kesalahan akan muncul.

Jika tabel tidak dipartisi, kolom FILESTREAM tidak dapat dipartisi. Data FILESTREAM untuk tabel harus disimpan dalam satu grup file yang ditentukan dalam FILESTREAM_ON klausa.

FILESTREAM_ON NULL dapat ditentukan dalam CREATE INDEX pernyataan jika indeks berkluster sedang dibuat dan tabel tidak berisi kolom FILESTREAM.

Untuk informasi selengkapnya, lihat FILESTREAM (SQL Server).

<object>::=

Objek yang sepenuhnya memenuhi syarat atau tidak memenuhi syarat untuk diindeks.

database_name

Nama database.

schema_name

Nama skema tempat tabel atau tampilan berada.

table_or_view_name

Nama tabel atau tampilan yang akan diindeks.

Tampilan harus didefinisikan dengan SCHEMABINDING untuk membuat indeks di atasnya. Indeks berkluster unik harus dibuat pada tampilan sebelum indeks non-kluster dibuat. Untuk informasi selengkapnya tentang tampilan terindeks, lihat bagian Keterangan.

Dimulai dengan SQL Server 2016 (13.x), objek dapat menjadi tabel yang disimpan dengan indeks penyimpan kolom berkluster.

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

<>relational_index_option::=

Menentukan opsi yang akan digunakan saat Anda membuat indeks.

PAD_INDEX = { ON | NONAKTIF }

Menentukan padding indeks. Defaultnya adalah NONAKTIF.

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.

Opsi PAD_INDEX ini hanya berguna ketika FILLFACTOR ditentukan, karena PAD_INDEX menggunakan persentase yang ditentukan oleh FILLFACTOR. Jika persentase yang ditentukan untuk FILLFACTOR tidak cukup besar untuk memungkinkan satu baris, Mesin Database secara internal mengambil alih persentase untuk memungkinkan minimum. Jumlah baris pada halaman indeks menengah tidak pernah kurang dari dua, terlepas dari seberapa rendah nilai fillfactor.

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

FILLFACTOR = fillfactor

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

FILLFACTOR Pengaturan hanya berlaku saat indeks dibuat atau dibangun kembali. Mesin Database tidak secara dinamis menyimpan persentase ruang kosong yang ditentukan di halaman.

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

Penting

Membuat indeks berkluster dengan FILLFACTOR kurang dari 100 memengaruhi jumlah ruang penyimpanan yang ditempati data karena Mesin Database mendistribusikan ulang data saat membuat indeks berkluster.

Untuk informasi selengkapnya, lihat Menentukan Faktor Pengisian untuk Indeks.

SORT_IN_TEMPDB = { AKTIF | NONAKTIF }

Menentukan apakah akan menyimpan hasil pengurutan sementara dalam tempdb. Defaultnya adalah NONAKTIF kecuali untuk Azure SQL Database Hyperscale. Untuk semua operasi build 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. Ini dapat mengurangi waktu yang diperlukan untuk membuat indeks jika tempdb berada di 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.

Selain ruang yang diperlukan dalam database pengguna untuk membuat indeks, tempdb harus memiliki jumlah ruang tambahan yang sama untuk menyimpan hasil pengurutan menengah. Untuk informasi selengkapnya, lihat Opsi SORT_IN_TEMPDB Untuk Indeks.

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

IGNORE_DUP_KEY = { AKTIF | NONAKTIF }

Menentukan respons kesalahan 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. 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 = { AKTIF | 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 STATISTICS NORECOMPUTE tanpa klausa.

Penting

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

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

STATISTICS_INCREMENTAL = { AKTIF | NONAKTIF }

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

Saat AKTIF, statistik yang dibuat adalah statistik per partisi. Ketika NONAKTIF, pohon statistik dihilangkan dan SQL Server menghitung ulang statistik. Defaultnya adalah NONAKTIF.

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

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

DROP_EXISTING = { AKTIF | NONAKTIF }

Adalah opsi untuk menghilangkan dan membangun kembali indeks berkluster atau non-kluster yang ada dengan spesifikasi kolom yang dimodifikasi, dan mempertahankan nama yang sama untuk indeks. Defaultnya adalah NONAKTIF.

AKTIF
Menentukan untuk menghilangkan dan membangun kembali indeks yang ada, yang harus memiliki nama yang sama dengan parameter index_name.

TIDAK AKTIF
Menentukan untuk tidak menghilangkan dan membangun kembali indeks yang ada. SQL Server menampilkan kesalahan jika nama indeks yang ditentukan sudah ada.

Dengan DROP_EXISTING, Anda dapat mengubah:

Indeks rowstore non-kluster ke indeks rowstore berkluster.

Dengan DROP_EXISTING, Anda tidak dapat mengubah:

Indeks rowstore berkluster ke indeks rowstore non-kluster.
Indeks penyimpan kolom berkluster ke semua jenis indeks rowstore.

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

ONLINE = { AKTIF | NONAKTIF }

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

Penting

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.

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. Ini memungkinkan kueri atau pembaruan pada tabel dan indeks yang mendasarinya untuk melanjutkan. Pada awal operasi, kunci Bersama (S) ditahan pada objek sumber untuk jangka waktu yang sangat singkat. Pada akhir operasi, untuk waktu yang singkat, kunci S (Bersama) diperoleh pada sumber jika indeks non-kluster sedang dibuat. 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.

Catatan

Pembuatan indeks online dapat mengatur low_priority_lock_wait opsi, lihat WAIT_AT_LOW_PRIORITY dengan operasi indeks online.

TIDAK AKTIF
Kunci tabel diterapkan 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. Ini mencegah semua akses pengguna ke tabel yang mendasar selama durasi 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 pernyataan SELECT.

Untuk informasi selengkapnya, lihat Melakukan Operasi Indeks Online.

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

Indeks XML
Indeks pada tabel sementara lokal
Indeks kluster unik awal pada tampilan
Indeks terkluster yang dinonaktifkan
Panduan 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 kunci indeks. Di SQL Server (Dimulai dengan SQL Server 2012 (11.x)) dan Azure SQL Database, ketika tabel berisi kolom varchar(max) atau varbinary(max), indeks berkluster yang berisi kolom lain dapat dibangun atau dibangun kembali menggunakan ONLINE opsi .

Untuk informasi selengkapnya, lihat Cara Kerja Operasi Indeks Online.

DAPAT DI-RESUMABLE = { ON | NONAKTIF }

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

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 2019 (15.x)) dan Azure SQL Database

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 atau indeks yang dinonaktifkan.

ALLOW_ROW_LOCKS = { AKTIF | NONAKTIF }

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 }

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 (Dimulai dengan SQL Server 2019 (15.x)) dan Azure SQL Database

Menentukan apakah akan mengoptimalkan ketidakcocokan sisipan halaman terakhir atau tidak. Defaultnya adalah NONAKTIF. Lihat bagian Kunci Berurutan untuk informasi selengkapnya.

MAXDOP = max_degree_of_parallelism

Mengambil alih tingkat maksimum opsi konfigurasi paralelisme selama durasi 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.

max_degree_of_parallelism dapat berupa:

1
Menekan pembuatan rencana paralel.

>1
Membatasi jumlah maksimum prosesor yang digunakan dalam operasi indeks paralel ke jumlah yang ditentukan atau lebih sedikit berdasarkan beban kerja sistem saat ini.

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

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

NONE
Indeks atau partisi yang ditentukan tidak dikompresi.

BARIS
Indeks atau partisi yang ditentukan dikompresi dengan menggunakan pemadatan baris.

PAGE
Indeks atau partisi tertentu dikompresi dengan menggunakan pemadatan halaman.

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 indeks yang ditentukan yang berisi satu atau beberapa kolom tipe data xml . Opsinya meliputi:

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

TIDAK AKTIF
Indeks atau partisi yang ditentukan tidak dikompresi.

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

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

<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).
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:

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

Keterangan

Pernyataan dioptimalkan CREATE INDEX seperti kueri lainnya. Untuk menyimpan operasi I/O, prosesor kueri dapat memilih untuk memindai indeks lain alih-alih melakukan pemindaian tabel. Operasi pengurutan dapat dihilangkan dalam beberapa situasi. Pada komputer multiprosesor CREATE INDEX dapat menggunakan lebih banyak prosesor untuk melakukan operasi pemindaian dan pengurutan yang terkait dengan pembuatan indeks, dengan cara yang sama seperti yang dilakukan kueri lain. Untuk informasi selengkapnya, lihat Mengonfigurasi Operasi Indeks Paralel.

CREATE INDEX Operasi dapat dicatat secara minimal jika model pemulihan database diatur ke yang dicatat secara massal atau sederhana.

Indeks dapat dibuat pada tabel sementara. Saat tabel dihilangkan atau sesi berakhir, indeks akan dihilangkan.

Indeks berkluster dapat dibangun pada variabel tabel saat Kunci Primer dibuat. Saat kueri selesai atau sesi berakhir, indeks akan dihilangkan.

Indeks mendukung properti yang diperluas.

CREATE INDEX tidak didukung di Microsoft Fabric.

Indeks berkluster

Membuat indeks berkluster pada tabel (tumpukan) atau menjatuhkan dan membuat ulang indeks berkluster yang ada mengharuskan ruang kerja tambahan tersedia dalam database untuk mengakomodasi pengurutan data dan salinan sementara tabel asli atau data indeks terkluster yang ada. Untuk informasi selengkapnya tentang indeks berkluster, lihat Membuat Indeks Berkluster dan Arsitektur dan Panduan Desain Indeks SQL Server.

Indeks non-kluster

Dimulai dengan SQL Server 2016 (13.x) dan di Azure SQL Database, Anda dapat membuat indeks non-kluster pada tabel yang disimpan sebagai indeks penyimpan kolom berkluster. Jika Anda pertama kali membuat indeks non-kluster pada tabel yang disimpan sebagai indeks tumpukan atau berkluster, indeks akan tetap ada jika Nanti Anda mengonversi tabel ke indeks penyimpan kolom berkluster. Anda juga tidak perlu menghilangkan indeks non-kluster saat Anda membangun kembali indeks penyimpan kolom berkluster.

Batasan dan Pembatasan:

Opsi FILESTREAM_ON ini tidak valid saat Anda membuat indeks non-kluster pada tabel yang disimpan sebagai indeks penyimpan kolom berkluster.

Indeks unik

Ketika indeks unik ada, Mesin Database memeriksa nilai duplikat setiap kali data ditambahkan oleh operasi sisipan. Sisipkan operasi yang akan menghasilkan nilai kunci duplikat digulung balik, dan Mesin Database menampilkan pesan kesalahan. Ini benar bahkan jika operasi sisipkan mengubah banyak baris tetapi hanya menyebabkan satu duplikat. Jika upaya dilakukan untuk memasukkan data yang ada indeks unik dan IGNORE_DUP_KEY klausa diatur ke AKTIF, hanya baris yang melanggar indeks UNIK yang gagal.

Indeks yang dipartisi

Indeks yang dipartisi dibuat dan dikelola dengan cara yang sama dengan tabel yang dipartisi, tetapi seperti indeks biasa, indeks tersebut ditangani sebagai objek database terpisah. Anda dapat memiliki indeks yang dipartisi pada tabel yang tidak dipartisi, dan Anda dapat memiliki indeks nonpartisi pada tabel yang dipartisi.

Jika Anda membuat indeks pada tabel yang dipartisi, dan tidak menentukan grup file untuk menempatkan indeks, indeks dipartisi dengan cara yang sama seperti tabel yang mendasar. Ini karena indeks, secara default, ditempatkan pada grup file yang sama dengan tabel yang mendasarnya, dan untuk tabel yang dipartisi dalam skema partisi yang sama yang menggunakan kolom partisi yang sama. Saat indeks menggunakan skema partisi dan kolom partisi yang sama dengan tabel, indeks diratakan dengan tabel.

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. Sebaiknya gunakan hanya indeks yang selaras ketika jumlah partisi melebihi 1.000.

Saat mempartisi indeks berkluster yang tidak unik, Mesin Database secara default menambahkan kolom partisi apa pun ke daftar kunci indeks berkluster, jika belum ditentukan.

Tampilan terindeks dapat dibuat pada tabel yang dipartisi dengan cara yang sama seperti indeks pada tabel. Untuk informasi selengkapnya tentang indeks yang dipartisi, lihat Tabel dan Indeks yang Dipartisi dan Arsitektur dan Panduan Desain Indeks SQL Server.

Di SQL Server, statistik tidak dibuat dengan memindai semua baris dalam tabel saat indeks yang dipartisi dibuat atau dibangun kembali. Sebagai gantinya, pengoptimal kueri menggunakan algoritma pengambilan sampel default untuk menghasilkan statistik. Untuk mendapatkan statistik pada indeks yang dipartisi dengan memindai semua baris dalam tabel, gunakan CREATE STATISTICS atau UPDATE STATISTICS dengan FULLSCAN klausul .

Indeks yang difilter

Indeks yang difilter adalah indeks non-kluster yang dioptimalkan, cocok untuk kueri yang memilih persentase kecil baris dari tabel. Ini menggunakan predikat filter untuk mengindeks sebagian data dalam tabel. Indeks terfilter yang dirancang dengan baik dapat meningkatkan performa kueri, mengurangi biaya penyimpanan, dan mengurangi biaya pemeliharaan.

Opsi SET yang diperlukan untuk indeks yang difilter

Opsi SET di kolom Nilai yang Diperlukan diperlukan setiap kali salah satu kondisi berikut ini terjadi:

Membuat indeks yang difilter.
Operasi INSERT, UPDATE, DELETE, atau MERGE memodifikasi data dalam indeks yang difilter.

Indeks yang difilter digunakan oleh pengoptimal kueri untuk menghasilkan rencana kueri.

ATUR opsi	Nilai yang diperlukan	Nilai server default	Default Nilai OLE DB dan ODBC	Default Nilai Pustaka DB
ANSI_NULLS	AKTIF	AKTIF	AKTIF	TIDAK AKTIF
ANSI_PADDING	AKTIF	AKTIF	AKTIF	TIDAK AKTIF
ANSI_WARNINGS*	AKTIF	AKTIF	AKTIF	TIDAK AKTIF
ARITHABORT	AKTIF	AKTIF	TIDAK AKTIF	TIDAK AKTIF
CONCAT_NULL_YIELDS_NULL	AKTIF	AKTIF	AKTIF	TIDAK AKTIF
NUMERIC_ROUNDABORT	TIDAK AKTIF	TIDAK AKTIF	TIDAK AKTIF	TIDAK AKTIF
QUOTED_IDENTIFIER	AKTIF	AKTIF	AKTIF	TIDAK AKTIF

Mengatur ANSI_WARNINGS ke AKTIF secara implisit mengatur ARITHABORT ke AKTIF saat tingkat kompatibilitas database diatur ke 90 atau lebih tinggi. Jika tingkat kompatibilitas database diatur ke 80 atau yang lebih lama, opsi ARITHABORT harus secara eksplisit diatur ke AKTIF.

Jika opsi SET salah, kondisi berikut dapat terjadi:

Indeks yang difilter tidak dibuat.
Mesin Database menghasilkan kesalahan dan mengembalikan pernyataan INSERT, UPDATE, DELETE, atau MERGE yang mengubah data dalam indeks.
Pengoptimal kueri tidak mempertimbangkan indeks dalam rencana eksekusi untuk pernyataan Transact-SQL apa pun.

Untuk informasi selengkapnya tentang Indeks Yang Difilter, lihat Membuat Indeks Terfilter dan Arsitektur dan Panduan Desain Indeks SQL Server.

Indeks spasial

Untuk informasi tentang indeks spasial, lihat MEMBUAT INDEKS SPASIAL dan Gambaran Umum Indeks Spasial.

Indeks XML

Untuk informasi tentang indeks XML, lihat MEMBUAT INDEKS XML dan Indeks XML (SQL Server).

Ukuran kunci indeks

Ukuran maksimum untuk kunci indeks adalah 900 byte untuk indeks berkluster dan 1.700 byte untuk indeks non-kluster. (Sebelum SQL Database dan SQL Server 2016 (13.x) batasnya selalu 900 byte.) Indeks pada kolom varchar yang melebihi batas byte dapat dibuat jika data yang ada di kolom tidak melebihi batas pada saat indeks dibuat; namun, tindakan sisipkan atau perbarui berikutnya pada kolom yang menyebabkan ukuran total lebih besar dari batas akan gagal. Kunci indeks indeks berkluster tidak boleh berisi kolom varchar yang memiliki data yang ada di unit alokasi ROW_OVERFLOW_DATA. Jika indeks berkluster dibuat pada kolom varchar dan data yang ada berada di unit alokasi IN_ROW_DATA, tindakan sisipkan atau perbarui berikutnya pada kolom yang akan mendorong data di luar baris akan gagal.

Indeks nonclustered dapat menyertakan kolom non-kunci dalam tingkat daun indeks. Kolom ini tidak dipertimbangkan oleh Mesin Database saat menghitung ukuran kunci indeks . Untuk informasi selengkapnya, lihat Membuat Indeks dengan Kolom yang Disertakan dan Arsitektur dan Panduan Desain Indeks SQL Server.

Catatan

Saat tabel dipartisi, jika kolom kunci partisi belum ada dalam indeks berkluster yang tidak unik, tabel ditambahkan ke indeks oleh Mesin Database. Ukuran gabungan kolom terindeks (tidak menghitung kolom yang disertakan), ditambah kolom partisi tambahan tidak boleh melebihi 1800 byte dalam indeks berkluster yang tidak unik.

Kolom komputasi

Indeks dapat dibuat pada kolom komputasi. Selain itu, kolom komputasi dapat memiliki properti PERSISTED. Ini berarti bahwa Mesin Database menyimpan nilai komputasi dalam tabel, dan memperbaruinya ketika kolom lain tempat kolom komputasi bergantung diperbarui. Mesin Database menggunakan nilai yang dipertahankan ini saat membuat indeks pada kolom, dan ketika indeks dirujuk dalam kueri.

Untuk mengindeks kolom komputasi, kolom komputasi harus deterministik dan tepat. Namun, menggunakan properti PERSISTED memperluas jenis kolom komputasi yang dapat diindeks untuk menyertakan:

Kolom komputasi berdasarkan fungsi Transact-SQL dan CLR dan metode jenis yang ditentukan pengguna CLR yang ditandai deterministik oleh pengguna.
Kolom komputasi berdasarkan ekspresi yang deterministik seperti yang ditentukan oleh Mesin Database tetapi tidak tepat.

Kolom komputasi yang dipertahankan mengharuskan opsi SET berikut diatur seperti yang diperlihatkan di bagian Sebelumnya Opsi SET yang Diperlukan untuk Indeks Yang Difilter.

Batasan UNIQUE atau PRIMARY KEY dapat berisi kolom komputasi selama memenuhi semua kondisi untuk pengindeksan. Secara khusus, kolom komputasi harus deterministik dan tepat atau deterministik dan bertahan. Untuk informasi selengkapnya tentang determinisme, lihat Fungsi Deterministik dan Nondeterministik.

Kolom komputasi yang berasal dari tipe data gambar, ntext, teks, varchar(max), nvarchar(max), varbinary(max), dan xml dapat diindeks baik sebagai kunci atau kolom non-kunci yang disertakan selama jenis data kolom komputasi diizinkan sebagai kolom kunci indeks atau kolom non-kunci. Misalnya, Anda tidak dapat membuat indeks XML utama pada kolom xml komputasi. Jika ukuran kunci indeks melebihi 900 byte, pesan peringatan akan ditampilkan.

Membuat indeks pada kolom komputasi dapat menyebabkan kegagalan operasi sisipkan atau perbarui yang sebelumnya berfungsi. Kegagalan seperti itu dapat terjadi ketika kolom komputasi mengakibatkan kesalahan aritmatika. Misalnya, dalam tabel berikut, meskipun kolom c komputasi menghasilkan kesalahan aritmatika, pernyataan INSERT berfungsi.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

Jika, sebagai gantinya, setelah membuat tabel, Anda membuat indeks pada kolom ckomputasi , pernyataan yang sama INSERT sekarang akan gagal.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

Untuk informasi selengkapnya, lihat Indeks pada Kolom Komputasi.

Kolom yang disertakan dalam indeks

Kolom non-kunci, yang disebut kolom yang disertakan, dapat ditambahkan ke tingkat daun indeks non-klusster untuk meningkatkan performa kueri dengan mencakup kueri. Artinya, semua kolom yang dirujuk dalam kueri disertakan dalam indeks sebagai kolom kunci atau non-kunci. Ini memungkinkan pengoptimal kueri menemukan semua informasi yang diperlukan dari pemindaian indeks; tabel atau data indeks terkluster tidak diakses. Untuk informasi selengkapnya, lihat Membuat Indeks dengan Kolom yang Disertakan dan Arsitektur dan Panduan Desain Indeks SQL Server.

Menentukan opsi indeks

SQL Server 2005 (9.x) memperkenalkan opsi indeks baru dan juga memodifikasi cara opsi ditentukan. Dalam sintaksis kompatibel mundur, WITH option_name setara dengan WITH (option_name = ON). Saat Anda mengatur opsi indeks, aturan berikut berlaku:

Opsi indeks baru hanya dapat ditentukan dengan menggunakan WITH (<option_name> = <ON | OFF>).
Opsi tidak dapat ditentukan dengan menggunakan sintaksis yang kompatibel mundur dan baru dalam pernyataan yang sama. Misalnya, menentukan WITH (DROP_EXISTING, ONLINE = ON) penyebab pernyataan gagal.
Saat Anda membuat indeks XML, opsi harus ditentukan dengan menggunakan WITH (<option_name> = <ON | OFF>).

klausa DROP_EXISTING

Anda dapat menggunakan DROP_EXISTING klausa untuk membangun kembali indeks, menambahkan atau menghilangkan kolom, mengubah opsi, mengubah urutan pengurutan kolom, atau mengubah skema partisi atau grup file.

Jika indeks memberlakukan batasan KUNCI PRIMER atau UNIK dan definisi indeks tidak diubah dengan cara apa pun, indeks dihilangkan dan dibuat ulang mempertahankan batasan yang ada. Namun, jika definisi indeks diubah, pernyataan gagal. Untuk mengubah definisi KUNCI PRIMER atau batasan UNIK, hilangkan batasan dan tambahkan batasan dengan definisi baru.

DROP_EXISTING meningkatkan performa saat Anda membuat ulang indeks berkluster, dengan sekumpulan kunci yang sama atau berbeda, pada tabel yang juga memiliki indeks non-kluster. DROP_EXISTING menggantikan eksekusi DROP INDEX pernyataan pada indeks berkluster lama diikuti dengan eksekusi CREATE INDEX pernyataan untuk indeks berkluster baru. Indeks nonclustered dibangun kembali sekali, dan kemudian hanya jika definisi indeks telah berubah. Klausa DROP_EXISTING tidak membangun kembali indeks nonclustered ketika definisi indeks memiliki nama indeks, kolom kunci dan partisi yang sama, atribut keunikan, dan urutan pengurutan sebagai indeks asli.

Apakah indeks non-kluster dibangun kembali atau tidak, indeks tersebut selalu tetap berada dalam grup file atau skema partisi aslinya dan menggunakan fungsi partisi asli. Jika indeks berkluster dibangun kembali ke grup file atau skema partisi yang berbeda, indeks non-kluster tidak dipindahkan bertepatan dengan lokasi baru indeks berkluster. Oleh karena itu, bahkan indeks non-kluster yang sebelumnya selaras dengan indeks berkluster, indeks tersebut mungkin tidak lagi selaras dengannya. Untuk informasi selengkapnya tentang perataan indeks yang dipartisi, lihat Tabel dan Indeks yang Dipartisi.

Klausa DROP_EXISTING tidak akan mengurutkan data lagi jika kolom kunci indeks yang sama digunakan dalam urutan yang sama dan dengan urutan naik atau turun yang sama, kecuali pernyataan indeks menentukan indeks non-kluster dan opsi ONLINE diatur ke NONAKTIF. Jika indeks berkluster dinonaktifkan, CREATE INDEX WITH DROP_EXISTING operasi harus dilakukan dengan ONLINE diatur ke NONAKTIF. Jika indeks non-kluster dinonaktifkan dan tidak terkait dengan indeks berkluster yang dinonaktifkan, CREATE INDEX WITH DROP_EXISTING operasi dapat dilakukan dengan ONLINE diatur ke NONAKTIF atau AKTIF.

Catatan

Ketika indeks dengan 128 tingkat atau lebih dihilangkan atau dibangun kembali, Mesin Database menuguhkan alokasi halaman aktual, dan kunci terkaitnya, hingga setelah transaksi dilakukan.

Opsi ONLINE

Panduan berikut berlaku untuk melakukan operasi indeks secara online:

Tabel yang mendasar tidak dapat diubah, dipotong, atau dihilangkan saat operasi indeks online sedang dalam proses.
Ruang disk sementara tambahan diperlukan selama operasi indeks.
Operasi online dapat dilakukan pada indeks dan indeks yang dipartisi yang berisi kolom komputasi yang dipertahankan, atau kolom yang disertakan.
low_priority_lock_wait Opsi argumen memungkinkan Anda memutuskan bagaimana operasi indeks dapat dilanjutkan saat diblokir pada kunci Sch-M.

Untuk informasi selengkapnya, lihat Melakukan Operasi Indeks Online.

Sumber

Sumber daya berikut diperlukan untuk operasi pembuatan indeks online yang dapat dilanjutkan:

Ruang tambahan yang diperlukan untuk menjaga indeks tetap dibangun, termasuk waktu ketika indeks sedang dijeda
Throughput log tambahan selama fase pengurutan. Penggunaan ruang log keseluruhan untuk indeks yang dapat dilanjutkan kurang dibandingkan dengan pembuatan indeks online reguler dan memungkinkan pemotongan log selama operasi ini.
Status DDL mencegah modifikasi DDL
Pembersihan hantu diblokir pada indeks dalam build selama durasi operasi baik saat dijeda maupun saat operasi sedang berjalan.

Batasan fungsi saat ini

Fungsionalitas berikut dinonaktifkan untuk operasi pembuatan indeks yang dapat dilanjutkan:

Setelah operasi pembuatan indeks online yang dapat dilanjutkan dijeda, nilai awal MAXDOP tidak dapat diubah
Buat indeks yang berisi:
- Kolom komputasi atau TANDA WAKTU sebagai kolom kunci
- Kolom LOB sebagai kolom yang disertakan untuk pembuatan indeks yang dapat diulang
- Indeks yang difilter

Operasi indeks yang dapat diulang

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

Panduan berikut berlaku untuk operasi indeks yang dapat dilanjutkan:

Pembuatan indeks online ditentukan sebagai dapat diulang menggunakan RESUMABLE = ON opsi .
Opsi RESUMABLE 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.
MAX_DURATION opsi hanya didukung untuk RESUMABLE = ON opsi.
MAX_DURATION untuk opsi RESUMABLE menentukan interval waktu untuk indeks yang sedang dibangun. Setelah waktu ini digunakan, build indeks dijeda atau menyelesaikan eksekusinya. Pengguna memutuskan kapan build 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 kedua indeks 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.
Untuk segera menjeda operasi indeks, Anda dapat menghentikan (Ctrl-C) perintah yang sedang berlangsung, menjalankan perintah UBAH JEDA INDEKS , atau menjalankan KILL <session_id> perintah. Setelah perintah dijeda, perintah dapat dilanjutkan menggunakan perintah ALTER INDEX .
Menjalankan kembali pernyataan asli CREATE INDEX untuk indeks yang dapat dilanjutkan, secara otomatis melanjutkan operasi pembuatan indeks yang dijeda.
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 dari blok awal TRAN ... COMMIT ).
Untuk melanjutkan/membatalkan pembuatan/pembangunan ulang indeks, gunakan sintaks ALTER INDEX T-SQL.
Indeks yang dinonaktifkan tidak didukung.

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 selengkapnya tentang status indeks saat ini dapat diperoleh dari sys.index_resumable_operations. Seperti sebelumnya jika terjadi kegagalan, kesalahan juga akan dikeluarkan.

Untuk menunjukkan bahwa pembuatan indeks dijalankan sebagai operasi yang dapat diulang dan untuk memeriksa status eksekusinya saat ini, lihat sys.index_resumable_operations.

WAIT_AT_LOW_PRIORITY dengan operasi indeks online

Berlaku untuk: Sintaks ini untuk CREATE INDEX saat ini berlaku untuk SQL Server 2022 (16.x), Azure SQL Database, dan Azure SQL Managed Instance saja. Untuk ALTER INDEX, sintaks ini berlaku untuk SQL Server (Dimulai dengan SQL Server 2014 (12.x)) dan Azure SQL Database. Untuk informasi selengkapnya, lihat MENGUBAH INDEKS.

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

Opsi ini WAIT_AT_LOW_PRIORITY memungkinkan DBA untuk mengelola kunci Sch-S dan Sch-M yang diperlukan untuk pembuatan indeks online dan memungkinkan mereka untuk memilih salah satu dari 3 opsi. Dalam semua 3 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 pembuatan 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) bahwa kunci pembuatan 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 MENIT dapat dihilangkan.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

NONE Lanjutkan menunggu kunci dengan prioritas normal (reguler).

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

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

Opsi penguncian baris dan halaman

Saat ALLOW_ROW_LOCKS = ON dan ALLOW_PAGE_LOCK = ON, kunci tingkat baris, halaman, dan tabel diizinkan saat 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 mengakses indeks.

Kunci berurutan

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

Ketidakcocokan sisipan halaman terakhir adalah masalah performa umum yang terjadi ketika sejumlah besar utas bersamaan mencoba menyisipkan baris ke dalam indeks dengan kunci berurutan. Indeks dianggap berurutan ketika kolom kunci utama berisi nilai yang selalu meningkat (atau menurun), seperti kolom identitas atau tanggal yang default ke tanggal/waktu saat ini. Karena kunci yang dimasukkan berurutan, semua baris baru akan disisipkan di akhir struktur indeks - dengan kata lain, pada halaman yang sama. Ini menyebabkan ketidakcocokan untuk halaman dalam memori yang dapat diamati sebagai beberapa utas yang menunggu PAGELATCH_EX untuk halaman yang dimaksud.

Mengaktifkan OPTIMIZE_FOR_SEQUENTIAL_KEY opsi indeks memungkinkan pengoptimalan dalam mesin database yang membantu meningkatkan throughput untuk sisipan konkurensi tinggi ke dalam indeks. Ini ditujukan untuk indeks yang memiliki kunci berurutan dan dengan demikian rentan terhadap ketidakcocokan sisipan halaman terakhir, tetapi mungkin juga membantu indeks yang memiliki titik panas di area lain dari struktur indeks B-Tree.

Catatan

Menampilkan informasi indeks

Untuk mengembalikan informasi tentang indeks, Anda dapat menggunakan tampilan katalog, fungsi sistem, dan prosedur tersimpan sistem.

Pemadatan data

Pemadatan data dijelaskan dalam topik Pemadatan Data. Berikut ini adalah poin-poin penting yang perlu dipertimbangkan:

Pemadatan dapat memungkinkan lebih banyak baris disimpan di halaman, tetapi tidak mengubah ukuran baris maksimum.
Halaman non-daun indeks tidak dikompresi halaman tetapi dapat dikompresi baris.
Setiap indeks nonclustered memiliki pengaturan kompresi individual, dan tidak mewarisi pengaturan kompresi tabel yang mendasar.
Ketika indeks berkluster dibuat pada timbunan, indeks berkluster mewarisi status kompresi timbunan kecuali status kompresi alternatif ditentukan.

Pembatasan berikut berlaku untuk indeks yang dipartisi:

Anda tidak dapat mengubah pengaturan pemadatan satu partisi jika tabel memiliki indeks yang tidak ditandatangani.
Sintaks ALTER INDEX <index> ... REBUILD PARTITION ... membangun kembali partisi indeks yang ditentukan.
ALTER INDEX <index> ... REBUILD WITH ... Sintaks membangun kembali semua partisi indeks.

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

Pemadatan XML

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

Banyak pertimbangan yang sama untuk pemadatan data berlaku untuk kompresi XML. Anda juga harus mengetahui pertimbangan berikut:

Ketika daftar partisi ditentukan, kompresi XML dapat diaktifkan pada partisi individual. Jika daftar partisi tidak ditentukan, semua partisi diatur untuk menggunakan kompresi XML. Saat tabel atau indeks dibuat, pemadatan data XML dinonaktifkan kecuali ditentukan lain. Saat tabel dimodifikasi, pemadatan yang ada dipertahankan kecuali ditentukan lain.
Jika Anda menentukan daftar partisi atau partisi yang berada di luar rentang, kesalahan akan dihasilkan.
Ketika indeks berkluster dibuat pada tumpukan, indeks berkluster mewarisi status kompresi XML dari timbunan kecuali opsi kompresi alternatif ditentukan.
Mengubah pengaturan kompresi XML dari tumpukan mengharuskan semua indeks non-kluster pada tabel dibangun kembali sehingga mereka memiliki penunjuk ke lokasi baris baru di tumpukan.
Anda dapat mengaktifkan atau menonaktifkan pemadatan XML secara online atau offline. Mengaktifkan kompresi pada tumpukan adalah utas tunggal untuk operasi online.
Untuk menentukan status kompresi XML partisi dalam tabel yang dipartisi, kueri xml_compression kolom sys.partitions tampilan katalog.

Izin

ALTER Memerlukan izin pada tabel atau tampilan atau keanggotaan dalam db_ddladmin peran database tetap.

Pembatasan dan batasan

Di Azure Synapse Analytics and Analytics Platform System (PDW), Anda tidak dapat membuat:

Indeks rowstore berkluster atau non-kluster pada tabel gudang data saat indeks penyimpan kolom sudah ada. Perilaku ini berbeda dari SMP SQL Server yang memungkinkan indeks rowstore dan columnstore untuk berdampingan pada tabel yang sama.
Anda tidak dapat membuat indeks pada tampilan.

Metadata

Untuk melihat informasi tentang indeks yang sudah ada, Anda bisa mengkueri tampilan katalog sys.indexes .

Catatan versi

SQL Database tidak mendukung opsi grup file dan aliran file.

Contoh: Semua versi. Menggunakan database AdventureWorks

J. Membuat indeks rowstore nonclustered sederhana

Contoh berikut membuat indeks nonclustered pada VendorID kolom Purchasing.ProductVendor tabel.

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

B. Membuat indeks komposit rowstore nonclustered sederhana

Contoh berikut membuat indeks komposit nonclustered pada SalesQuota kolom Sales.SalesPerson dan SalesYTD tabel.

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

C. Membuat indeks pada tabel di database lain

Contoh berikut membuat indeks berkluster pada VendorID kolom ProductVendor tabel dalam Purchasing database.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

D. Menambahkan kolom ke indeks

Contoh berikut membuat indeks IX_FF dengan dua kolom dari dbo. Tabel FactFinance. Pernyataan berikutnya membangun kembali indeks dengan satu kolom lagi dan mempertahankan nama yang ada.

CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey ASC, DateKey ASC);

-- Rebuild and add the OrganizationKey
CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey, DateKey, OrganizationKey DESC)
  WITH (DROP_EXISTING = ON);

Contoh: SQL Server, Azure SQL Database

E. Membuat indeks nonclustered unik

Contoh berikut membuat indeks nonclustered unik pada Name kolom Production.UnitMeasure tabel dalam AdventureWorks2022 database. Indeks akan memberlakukan keunikan pada data yang disisipkan ke Name dalam kolom.

CREATE UNIQUE INDEX AK_UnitMeasure_Name
  ON Production.UnitMeasure(Name);

Kueri berikut menguji batasan keunikan dengan mencoba menyisipkan baris dengan nilai yang sama seperti yang ada di baris yang sudah ada.

-- Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO

INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
  VALUES ('OC', 'Ounces', GETDATE());

Pesan kesalahan yang dihasilkan adalah:

Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

F. Gunakan opsi IGNORE_DUP_KEY

Contoh berikut menunjukkan efek IGNORE_DUP_KEY opsi dengan menyisipkan beberapa baris ke dalam tabel sementara terlebih dahulu dengan opsi diatur ke ON dan lagi dengan opsi diatur ke OFF. Satu baris disisipkan ke dalam #Test tabel yang sengaja akan menyebabkan nilai duplikat saat pernyataan beberapa baris INSERT kedua dijalankan. Jumlah baris dalam tabel mengembalikan jumlah baris yang disisipkan.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = ON);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

Berikut adalah hasil pernyataan kedua INSERT .

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows
--------------
38

Perhatikan bahwa baris yang disisipkan dari Production.UnitMeasure tabel yang tidak melanggar batasan keunikan berhasil dimasukkan. Peringatan dikeluarkan dan baris duplikat diabaikan, tetapi seluruh transaksi tidak digulung balik.

Pernyataan yang sama dijalankan lagi, tetapi dengan IGNORE_DUP_KEY diatur ke OFF.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = OFF);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

Berikut adalah hasil pernyataan kedua INSERT .

Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows
--------------
1

Perhatikan bahwa tidak ada baris dari Production.UnitMeasure tabel yang disisipkan ke dalam tabel meskipun hanya satu baris dalam tabel yang melanggar UNIQUE batasan indeks.

G. Menggunakan DROP_EXISTING untuk menghilangkan dan membuat ulang indeks

Contoh berikut menghilangkan dan membuat ulang indeks yang sudah ada pada ProductID kolom tabel dalam AdventureWorks2022 database dengan menggunakan DROP_EXISTING Production.WorkOrder opsi . Opsi FILLFACTOR dan PAD_INDEX juga diatur.

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
  ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
      PAD_INDEX = ON,
      DROP_EXISTING = ON);
GO

H. Membuat indeks pada tampilan

Contoh berikut membuat tampilan dan indeks pada tampilan tersebut. Dua kueri disertakan yang menggunakan tampilan terindeks.

-- Set the options to support indexed views
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
  QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

-- Create view with schemabinding
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
  DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
  WITH SCHEMABINDING
AS
  SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
    OrderDate, ProductID, COUNT_BIG(*) AS COUNT
  FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
  WHERE od.SalesOrderID = o.SalesOrderID
  GROUP BY OrderDate, ProductID;
GO

-- Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_V1
  ON Sales.vOrders (OrderDate, ProductID);
GO

-- This query can use the indexed view even though the view is
-- not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
  OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND ProductID BETWEEN 700 AND 800
    AND OrderDate >= CONVERT(DATETIME, '05/01/2002', 101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO

-- This query can use the above indexed view
SELECT OrderDate, SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND DATEPART(mm, OrderDate) = 3
  AND DATEPART(yy, OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

I. Membuat indeks dengan kolom yang disertakan (non-kunci)

Contoh berikut membuat indeks nonclustered dengan satu kolom kunci (PostalCode) dan empat kolom non-kunci (AddressLine1, AddressLine2, City, StateProvinceID). Kueri yang dicakup oleh indeks berikut. Untuk menampilkan indeks yang dipilih oleh pengoptimal kueri, pada menu Kueri di SQL Server Management Studio, pilih Tampilkan Rencana Eksekusi Aktual sebelum menjalankan kueri.

CREATE NONCLUSTERED INDEX IX_Address_PostalCode
  ON Person.Address (PostalCode)
  INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

j. Membuat indeks yang dipartisi

Contoh berikut membuat indeks partisi noncluster pada TransactionsPS1, skema partisi yang ada dalam AdventureWorks2022 database. Contoh ini mengasumsikan sampel indeks yang dipartisi telah diinstal.

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
  ON Production.TransactionHistory (ReferenceOrderID)
  ON TransactionsPS1 (TransactionDate);
GO

K. Membuat indeks yang difilter

Contoh berikut membuat indeks yang difilter pada tabel Production.BillOfMaterials dalam AdventureWorks2022 database. Predikat filter dapat menyertakan kolom yang bukan kolom kunci dalam indeks yang difilter. Predikat dalam contoh ini hanya memilih baris di mana EndDate bukan NULL.

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
  ON Production.BillOfMaterials (ComponentID, StartDate)
  WHERE EndDate IS NOT NULL;

L. Membuat indeks terkompresi

Contoh berikut membuat indeks pada tabel yang tidak dipartisi dengan menggunakan pemadatan baris.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (DATA_COMPRESSION = ROW);
GO

Contoh berikut membuat indeks pada tabel yang dipartisi dengan menggunakan pemadatan baris pada semua partisi indeks.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (DATA_COMPRESSION = ROW);
GO

Contoh berikut membuat indeks pada tabel yang dipartisi dengan menggunakan pemadatan halaman pada partisi 1 kompresi indeks dan baris pada partisi 2 melalui 4 indeks.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (
    DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4)
  );
GO

M. Membuat indeks dengan kompresi XML

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

Contoh berikut membuat indeks pada tabel yang tidak dipartisi dengan menggunakan kompresi XML. Setidaknya satu kolom dalam indeks harus berupa tipe data xml .

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (XML_COMPRESSION = ON);
GO

Contoh berikut membuat indeks pada tabel yang dipartisi dengan menggunakan pemadatan XML pada semua partisi indeks.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (XML_COMPRESSION = ON);
GO

N. Membuat, melanjutkan, menjeda, dan membatalkan operasi indeks yang dapat dilanjutkan

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

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx1 ON test_table (col1) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx2 ON test_table (col2) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx1 ON test_table PAUSE;
ALTER INDEX test_idx2 ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx1 ON test_table RESUME;
ALTER INDEX test_idx2 ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx1 ON test_table ABORT;
ALTER INDEX test_idx2 ON test_table ABORT;

O. CREATE INDEX dengan opsi kunci berprioritas rendah yang berbeda

Contoh berikut menggunakan WAIT_AT_LOW_PRIORITY opsi untuk menentukan strategi yang berbeda untuk menangani pemblokiran.

--Kill this session after waiting 5 minutes
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));
GO

--Kill blocker sessions
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));
GO

Contoh berikut menggunakan RESUMABLE opsi dan menentukan dua MAX_DURATION nilai, yang pertama berlaku untuk ABORT_AFTER_WAIT opsi , yang kedua berlaku untuk opsi .RESUMABLE

--With resumable option; default locking behavior 
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), RESUMABLE = ON, MAX_DURATION = 240 MINUTES);

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

P. Sintaks dasar

Membuat, melanjutkan, menjeda, dan membatalkan operasi indeks yang dapat dilanjutkan

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

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx ON test_table ABORT;

T. Membuat indeks nonclustered pada tabel di database saat ini

Contoh berikut membuat indeks nonclustered pada VendorID kolom ProductVendor tabel.

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

R. Membuat indeks berkluster pada tabel di database lain

Contoh berikut membuat indeks nonclustered pada VendorID kolom ProductVendor tabel dalam Purchasing database.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID
  ON Purchasing..ProductVendor (VendorID);

S. Membuat indeks berkluster yang diurutkan pada tabel

Contoh berikut membuat indeks berkluster yang diurutkan pada c1 kolom T1 dan c2 tabel dalam MyDB database.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 
ORDER (c1, c2);

T. Mengonversi CCI menjadi indeks berkluster yang diurutkan pada tabel

Contoh berikut mengonversi indeks penyimpan kolom berkluster yang ada menjadi indeks penyimpan kolom berkluster yang diurutkan yang dipanggil MyOrderedCCI pada kolom T2 dan c2 tabel dalam MyDB database.c1

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2
ORDER (c1, c2)
WITH (DROP_EXISTING = ON);

Bagikan melalui

BUAT INDEKS (Transact-SQL)

Sintaks

Sintaks untuk SQL Server, Azure SQL Database, Azure SQL Managed Instance

Indeks relasional yang kompatibel mundur

Sintaks untuk Azure Synapse Analytics dan Gudang Data Paralel

Argumen

UNIQUE

CLUSTERED

TIDAK TERKLUSTER

index_name

column

[ ASC | DESC ]

INCLUDE (kolom [ ,... n ] )

WHERE <filter_predicate>

ON partition_scheme_name ( column_name )

ON filegroup_name

AKTIF "default"

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

<object>::=

database_name

schema_name

table_or_view_name

<>relational_index_option::=

PAD_INDEX = { ON | NONAKTIF }

FILLFACTOR = fillfactor

SORT_IN_TEMPDB = { AKTIF | NONAKTIF }

IGNORE_DUP_KEY = { AKTIF | NONAKTIF }

STATISTICS_NORECOMPUTE = { AKTIF | OFF}

STATISTICS_INCREMENTAL = { AKTIF | NONAKTIF }

DROP_EXISTING = { AKTIF | NONAKTIF }

ONLINE = { AKTIF | NONAKTIF }

DAPAT DI-RESUMABLE = { ON | NONAKTIF }

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

ALLOW_ROW_LOCKS = { AKTIF | NONAKTIF }

ALLOW_PAGE_LOCKS = { AKTIF | NONAKTIF }

OPTIMIZE_FOR_SEQUENTIAL_KEY = { AKTIF | NONAKTIF }

MAXDOP = max_degree_of_parallelism

DATA_COMPRESSION

XML_COMPRESSION

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

Keterangan

Indeks berkluster

Indeks non-kluster

Indeks unik

Indeks yang dipartisi

Indeks yang difilter

Opsi SET yang diperlukan untuk indeks yang difilter

Indeks spasial

Indeks XML

Ukuran kunci indeks

Kolom komputasi

Kolom yang disertakan dalam indeks

Menentukan opsi indeks

klausa DROP_EXISTING

Opsi ONLINE

Sumber

Batasan fungsi saat ini

Operasi indeks yang dapat diulang

WAIT_AT_LOW_PRIORITY dengan operasi indeks online

Opsi penguncian baris dan halaman

Kunci berurutan

Menampilkan informasi indeks

Pemadatan data

Pemadatan XML

Izin

Pembatasan dan batasan

Metadata

Catatan versi

Contoh: Semua versi. Menggunakan database AdventureWorks

J. Membuat indeks rowstore nonclustered sederhana

B. Membuat indeks komposit rowstore nonclustered sederhana

C. Membuat indeks pada tabel di database lain

D. Menambahkan kolom ke indeks

Contoh: SQL Server, Azure SQL Database

E. Membuat indeks nonclustered unik

F. Gunakan opsi IGNORE_DUP_KEY

G. Menggunakan DROP_EXISTING untuk menghilangkan dan membuat ulang indeks

H. Membuat indeks pada tampilan

I. Membuat indeks dengan kolom yang disertakan (non-kunci)

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