BUAT INDEKS (Transact-SQL)

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics AnalyticsPlatform 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, Azure SQL Database, dan Azure SQL Managed Instance.

Untuk informasi tentang panduan desain indeks, lihat panduan desain indeks SQL Server.

Contoh:

1. Membuat indeks nonclustered pada tabel atau tampilan

   CREATE INDEX index1 ON schema1.table1 (column1);
   

2. Membuat indeks berkluster pada tabel dan menggunakan nama 3 bagian untuk tabel

   CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
   

3. 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), di Azure SQL Database, dan di Azure SQL Managed Instance, 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:

• BUAT INDEKS XML
• MEMBUAT INDEKS SPASIAL
• CREATE COLUMNSTORE INDEX

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.

Mesin Database tidak mengizinkan pembuatan indeks unik pada kolom yang sudah menyertakan nilai duplikat, apakah diatur ke atau tidak IGNORE_DUP_KEY ke ON. Jika ini dicoba, Mesin Database menampilkan pesan kesalahan. Nilai duplikat harus dihapus sebelum indeks unik dapat dibuat pada kolom atau kolom.

Batasan UNIQUENULL diperlakukan sebagai nilai. Jika kolom dapat diubah ke null dan UNIQUE batasan ada pada kolom, paling banyak satu baris dengan NULL diperbolehkan.

CLUSTERED

Membuat indeks di mana urutan pengurutan yang ditentukan untuk kolom kunci indeks menentukan urutan halaman dalam struktur indeks pada disk. Baris pada halaman di bagian bawah, atau daun, tingkat indeks berkluster selalu berisi semua kolom tabel. Baris pada halaman di tingkat atas indeks hanya berisi kolom kunci.

Tabel hanya dapat memiliki satu indeks berkluster. Jika indeks berkluster ada pada tabel, indeks tersebut berisi semua data dalam tabel. Tabel tanpa indeks berkluster disebut timbunan.

Tampilan dengan indeks berkluster unik disebut tampilan terindeks. Tampilan terindeks hanya dapat memiliki satu indeks berkluster. 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 ketika indeks berkluster dibuat, yang merupakan operasi intensif sumber daya jika tabel besar.

Jika CLUSTERED tidak ditentukan, indeks non-kluster dibuat.

Catatan

Karena indeks berkluster berisi semua data dalam tabel, 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 di mana urutan pengurutan yang ditentukan untuk kolom kunci indeks menentukan urutan halaman dalam struktur indeks pada disk. Tidak seperti indeks berkluster, baris pada halaman di tingkat daun indeks non-kluster hanya berisi kolom kunci indeks. Secara opsional, subset kolom non-kunci dapat disertakan menggunakan INCLUDE klausa.

Setiap tabel dapat memiliki hingga 999 indeks nonclustered, terlepas dari bagaimana indeks dibuat: baik secara implisit dengan PRIMARY KEY batasan dan UNIQUE , 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 terindeks tidak dapat menyertakan kolom ntext, teks, atau gambar , meskipun tidak direferensikan 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 INCLUDE daftar dan tidak dapat digunakan secara bersamaan sebagai kolom kunci dan non-kunci. Indeks berkluster selalu secara implisit 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), di Azure SQL Database, dan di Azure SQL Managed Instance, jika salah satu kolom non-kunci yang ditentukan adalah varchar(max), nvarchar(max), atau jenis data varbinary(max), indeks dapat dibangun atau dibangun kembali menggunakan ONLINE opsi .

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 selama jenis data kolom komputasi diizinkan sebagai kolom yang disertakan. Untuk informasi selengkapnya, lihat Indeks pada kolom terhitung.

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 tipe data yang ditentukan pengguna (UDT), kolom tipe data spasial, atau kolom tipe data hierarkis . Perbandingan dengan NULL literal yang menggunakan operator perbandingan tidak diizinkan. 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 UNIQUE indeks, 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 dipetakan. Skema partisi harus ada dalam database dengan menjalankan CREATE PARTITION SCHEME atau ALTER PARTITION SCHEME. column_name menentukan kolom partisi untuk indeks. 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 atau skema tabel atau tampilan yang dipartisi dan harus dibatasi, seperti dalam ON "default" atau ON [default]. Jika "default" ditentukan, QUOTED_IDENTIFIER opsi harus untuk ON sesi saat ini. Ini adalah pengaturan default. Untuk informasi selengkapnya, lihat MENGATUR QUOTED_IDENTIFIER.

Catatan

Dalam konteks CREATE INDEX, "default" dan [default] jangan tunjukkan grup file default database. Mereka menunjukkan grup file atau skema partisi yang digunakan oleh tabel dasar atau tampilan. Ini berbeda dari CREATE TABLE, di mana "default" dan [default] menempatkan 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.

Untuk membuat indeks pada tampilan, tampilan harus ditentukan dengan SCHEMABINDING. Indeks berkluster unik harus dibuat pada tampilan sebelum indeks non-kluster dibuat. Untuk informasi selengkapnya tentang tampilan terindeks, lihat Komentar.

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 nama database saat ini, atau <database_name>tempdb dan <object_name> dimulai dengan # atau ##. Jika nama skema dbo, <schema_name> dapat dihilangkan.

< >relational_index_option::=

Menentukan opsi yang akan digunakan saat Anda membuat indeks.

PAD_INDEX = { ON | NONAKTIF }

Menentukan padding indeks. Defaultnya adalah OFF.

• AKTIF

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

• TIDAK AKTIF

  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. Ini juga terjadi jika PAD_INDEX diatur ke ON tetapi faktor pengisian tidak ditentukan.

Opsi PAD_INDEX ini hanya berguna ketika FILLFACTOR ditentukan, karena PAD_INDEX menggunakan persentase yang ditentukan oleh FILLFACTOR. Jika persentase yang ditentukan 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 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 fill_factor , gunakan kolom dalam tampilan katalog sys.indexes .

Penting

Membuat indeks dengan FILLFACTOR kurang dari 100 meningkatkan jumlah ruang penyimpanan yang ditempati data karena Mesin Database mendistribusikan ulang data sesuai dengan faktor pengisian saat membuat atau membangun ulang indeks.

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 OFF kecuali untuk Azure SQL Database Hyperscale. Untuk semua operasi build indeks di Hyperscale, SORT_IN_TEMPDB selalu ON kecuali build indeks yang dapat diulang digunakan. Untuk build indeks yang dapat diulang, SORT_IN_TEMPDB selalu OFF.

• AKTIF

  Hasil pengurutan menengah yang digunakan untuk membangun indeks disimpan di tempdb. Ini mungkin 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.

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 OFF.

• AKTIF

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

• TIDAK AKTIF

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

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

Untuk melihat pengaturan IGNORE_DUP_KEY untuk indeks, gunakan kolom ignore_dup_key di tampilan katalog sys.indexes.

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

STATISTICS_NORECOMPUTE = { AKTIF | OFF}

Menentukan apakah statistik dikomputasi ulang. Defaultnya adalah OFF.

• 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 klausa.

Peringatan

Jika Anda menonaktifkan komputasi ulang statistik otomatis dengan mengatur STATISTICS_NORECOMPUTE = ON, Anda mungkin mencegah pengoptimal kueri memilih rencana eksekusi optimal untuk kueri yang melibatkan tabel.

Mengatur STATISTICS_NORECOMPUTE ke ON tidak mencegah pembaruan statistik indeks yang terjadi selama operasi pembangunan ulang indeks.

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

STATISTICS_INCREMENTAL = { AKTIF | NONAKTIF }

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

Ketika ON, statistik yang dibuat adalah statistik per partisi. Ketika OFF, pohon statistik dijatuhkan dan SQL Server menghitung ulang statistik. Defaultnya adalah OFF.

Jika statistik per partisi tidak didukung, opsi diabaikan dan peringatan dihasilkan. Statistik inkremental tidak didukung dalam kasus 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 OFF.

• 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 OFF.

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 niat bersama (IS) yang ditahan pada tabel sumber. Ini memungkinkan kueri atau pembaruan pada tabel dan indeks yang mendasarinya untuk melanjutkan. Pada awal operasi, kunci bersama (S) ditahan pada objek sumber untuk waktu yang singkat. Pada akhir operasi, untuk waktu yang singkat, kunci bersama (S) diperoleh pada objek jika indeks non-kluster sedang dibuat. Penguncian modifikasi skema (Sch-M) diperoleh ketika indeks berkluster dibuat atau dihilangkan secara online dan ketika indeks berkluster atau non-kluster sedang dibangun kembali. ONLINE tidak dapat diatur ke ON saat indeks sedang dibuat pada tabel sementara lokal.

  Catatan

  Anda dapat menggunakan opsi WAIT_AT_LOW_PRIORITY untuk mengurangi atau menghindari pemblokiran selama operasi indeks online. Untuk informasi selengkapnya, 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, spasial, atau XML, atau membangun kembali atau menghilangkan indeks non-kluster, memperoleh penguncian 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 awalnya memperoleh kunci bersama (S) pada tabel. Ini mencegah modifikasi definisi tabel yang mendasar, tetapi memungkinkan membaca dan memodifikasi data dalam tabel saat build indeks sedang berlangsung.

Untuk informasi selengkapnya, lihat Melakukan operasi indeks secara online dan Panduan untuk 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
• Indeks penyimpan kolom berkluster di SQL Server 2017 (14.x)) dan versi yang lebih lama
• Indeks penyimpan kolom nonclustered di SQL Server 2016 (13.x)) dan versi yang lebih lama
• 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)), di Azure SQL Database, dan di Azure SQL Managed Instance, ketika tabel berisi kolom varchar(max) atau varbinary(max), indeks berkluster yang berisi kolom lain dapat dibangun atau dibangun kembali menggunakan ONLINE opsi .
• Indeks non-kluster pada tabel dengan indeks penyimpan kolom berkluster

Untuk informasi selengkapnya, lihat Cara kerja operasi indeks online.

DAPAT DI-RESUMABLE = { ON | NONAKTIF }

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

Menentukan apakah operasi indeks online dapat diulang. Untuk informasi selengkapnya, lihat Operasi indeks yang dapat dilanjutkan dan Pertimbangan indeks yang dapat dilanjutkan.

• 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 2019 (15.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance

Menentukan berapa lama, dalam menit, operasi indeks yang dapat dilanjutkan dijalankan sebelum dijeda.

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 2019 (15.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance

Menentukan apakah akan mengoptimalkan atau tidak untuk menghindari ketidakcocokan sisipan halaman terakhir. Defaultnya adalah OFF. Lihat bagian Kunci berurutan untuk informasi selengkapnya.

MAXDOP = max_degree_of_parallelism

Mengambil alih tingkat maksimum opsi konfigurasi paralelisme untuk operasi indeks. Untuk informasi selengkapnya, lihat Mengonfigurasi tingkat maksimum Opsi Konfigurasi Server paralelisme. Gunakan MAXDOP untuk membatasi tingkat paralelisme dan konsumsi sumber daya yang dihasilkan untuk operasi build indeks.

max_degree_of_parallelism dapat berupa:

• 1

  Menekan pembuatan rencana paralel.

• >1

  Membatasi tingkat paralelisme maksimum yang digunakan dalam operasi indeks paralel ke angka yang ditentukan atau kurang berdasarkan beban kerja sistem saat ini.

• 0 (default)

  Menggunakan tingkat paralelisme yang ditentukan di tingkat grup server, database, atau beban kerja, kecuali dikurangi 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. Ini tidak berlaku untuk indeks penyimpan kolom.

• BARIS

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

• PAGE

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

• PENYIMPAN KOLOM

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

  Hanya berlaku untuk indeks penyimpan kolom, termasuk penyimpan kolom nonclustered dan indeks penyimpan kolom berkluster.

• COLUMNSTORE_ARCHIVE

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

  Hanya berlaku untuk indeks penyimpan kolom, termasuk penyimpan kolom nonclustered dan indeks penyimpan kolom berkluster. COLUMNSTORE_ARCHIVE lebih lanjut memadatkan partisi yang ditentukan ke ukuran yang lebih kecil. Ini dapat digunakan untuk pengarsipan, atau untuk situasi lain yang memerlukan ukuran penyimpanan yang lebih kecil dan mampu lebih banyak waktu untuk penyimpanan dan pengambilan.

Untuk informasi selengkapnya tentang pemadatan, lihat Pemadatan data.

XML_COMPRESSION

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

Menentukan opsi pemadatan XML untuk 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 menggunakan kompresi XML.

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

Menentukan partisi yang diterapkan pengaturan DATA_COMPRESSION atau XML_COMPRESSION . Jika indeks tidak dipartisi, ON PARTITIONS argumen 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 kunci 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

Saat membuat rencana kueri untuk pernyataan tersebut CREATE INDEX , pengoptimal kueri mungkin memilih untuk memindai indeks lain alih-alih melakukan pemindaian tabel. Operasi pengurutan mungkin dihilangkan dalam beberapa situasi. Pada komputer multiprosesor, CREATE INDEX dapat menggunakan paralelisme untuk 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.

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

Indeks dapat dibuat pada tabel sementara. Ketika tabel dihilangkan atau keluar dari cakupan, indeks akan dihilangkan.

Indeks berkluster dibangun pada variabel tabel saat batasan kunci utama ditambahkan. Demikian pula, indeks nonclustered dibangun pada variabel tabel saat batasan unik ditambahkan. Ketika variabel tabel keluar dari cakupan, indeks 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 indeks dan panduan desain SQL Server.

Indeks non-kluster

Dimulai dengan SQL Server 2016 (13.x), di Azure SQL Database, dan di Azure SQL Managed Instance, 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 menjadi indeks penyimpan kolom berkluster. Anda juga tidak perlu menghilangkan indeks non-kluster saat Anda membangun kembali indeks penyimpan kolom berkluster.

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 atau dimodifikasi. Operasi yang akan menghasilkan nilai kunci duplikat digulung balik, dan Mesin Database mengembalikan pesan kesalahan. Ini berlaku bahkan jika operasi penambahan atau modifikasi data mengubah banyak baris tetapi hanya menyebabkan satu duplikat. Jika upaya dilakukan untuk menyisipkan baris ketika ada indeks unik dengan IGNORE_DUP_KEY opsi diatur ke ON, baris yang melanggar indeks unik diabaikan.

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 hanya gunakan 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 sertaarsitektur dan panduan desain indeks SQL Server.

Saat indeks dibuat atau dibangun kembali, kueri mengoptimalkan pembaruan statistik pada indeks. Untuk indeks yang dipartisi, pengoptimal kueri menggunakan algoritma pengambilan sampel default alih-alih memindai semua baris dalam tabel untuk indeks yang tidak dipartisi. 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 terjadi:

• Anda membuat indeks yang difilter.

• Pernyataan INSERT, , DELETEUPDATE, atau MERGE memodifikasi data dalam indeks yang difilter.

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

  SET opsi	Nilai yang diperlukan	Nilai server default	Nilai OLE DB dan ODBC default	Nilai Pustaka DB default
  ANSI_NULLS	ON	ON	ON	OFF
  ANSI_PADDING	ON	ON	ON	OFF
  ANSI_WARNINGS 1	ON	ON	ON	OFF
  ARITHABORT	ON	ON	OFF	OFF
  CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
  NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
  QUOTED_IDENTIFIER	ON	ON	ON	OFF

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

SET Jika opsi salah, kondisi berikut dapat terjadi:

• Gagal membuat indeks yang difilter.
• Mesin Database menghasilkan kesalahan dan mengembalikan INSERTpernyataan , , UPDATE, DELETEatau 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 yang difilter dan arsitektur indeks dan panduan desain SQL Server.

Indeks spasial

Untuk informasi tentang indeks spasial, lihat Gambaran umum MEMBUAT INDEKS SPASIAL dan 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, operasi sisipkan atau perbarui berikutnya pada kolom yang menyebabkan ukuran total lebih besar dari batas gagal. Kunci indeks indeks berkluster tidak boleh berisi kolom varchar yang memiliki data yang ada di ROW_OVERFLOW_DATA unit alokasi. Jika indeks berkluster dibuat pada kolom varchar dan data yang ada berada di IN_ROW_DATA unit alokasi, operasi penyisipan atau pembaruan berikutnya pada kolom yang akan mendorong data dari baris gagal.

Indeks nonclustered dapat menyertakan kolom non-kunci (disertakan) 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 indeks dan panduan desain 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 PERSISTED properti 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 berikut SET 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 mungkin terjadi ketika kolom komputasi menghasilkan kesalahan aritmatika.

Misalnya, dalam tabel berikut, meskipun ekspresi kolom c komputasi tampaknya mengakibatkan kesalahan aritmatika saat baris disisipkan, INSERT pernyataan berfungsi.

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

Namun, jika Anda membuat indeks pada kolom ckomputasi , pernyataan yang sama INSERT 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 terhitung.

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. Hal ini memungkinkan pengoptimal kueri untuk mendapatkan semua informasi yang diperlukan dari pemindaian atau pencarian indeks yang tidak terdaftar; tabel atau data indeks terkluster tidak diakses. Untuk informasi selengkapnya, lihat Membuat indeks dengan kolom yang disertakan dan arsitektur indeks dan panduan desain 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 PRIMARY KEY batasan atau UNIQUE 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 atau UNIQUE batasanPRIMARY KEY, 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 jika indeks non-kluster 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 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 ONLINE opsi diatur ke OFF. Jika indeks berkluster dinonaktifkan, CREATE INDEX WITH DROP_EXISTING operasi harus dilakukan dengan ONLINE diatur ke OFF. 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 OFF atau ON.

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. Untuk informasi selengkapnya, lihat alokasi yang ditangguhkan.

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.
• WAIT_AT_LOW_PRIORITY Opsi argumen memungkinkan Anda memutuskan bagaimana operasi indeks dilanjutkan ketika menunggu Sch-M kunci. Untuk informasi selengkapnya, lihat WAIT_AT_LOW_PRIORITY

Untuk informasi selengkapnya, lihat Melakukan operasi indeks secara online.

Operasi indeks yang dapat diulang

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

Anda dapat membuat operasi pembuatan indeks online dapat diulang. Itu berarti bahwa build indeks dapat dihentikan dan kemudian dimulai ulang dari titik di mana ia berhenti. Untuk menjalankan build indeks sebagai dapat diulang, tentukan RESUMABLE = ON opsi .

Panduan berikut berlaku untuk operasi indeks yang dapat dilanjutkan:

• Untuk menggunakan opsi RESUMABLE, Anda juga harus menggunakan opsi ONLINE.
• 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.
• Opsi MAX_DURATION dapat ditentukan dalam dua konteks:
  • MAX_DURATION RESUMABLE untuk opsi menentukan interval waktu untuk indeks yang sedang dibangun kembali. Setelah waktu ini berlalu, dan jika pembangunan ulang indeks masih berjalan, maka akan dijeda. Anda memutuskan kapan pembangunan ulang untuk indeks yang dijeda dapat dilanjutkan. Waktu dalam menit untuk MAX_DURATION harus lebih besar dari 0 menit dan kurang dari atau sama dengan satu minggu (7 * 24 * 60 = 10080 menit). Jeda panjang dalam operasi indeks mungkin sangat berdampak pada performa DML pada tabel tertentu serta kapasitas disk database karena indeks asli dan indeks yang baru dibuat memerlukan ruang disk dan perlu diperbarui oleh operasi DML. Jika opsi MAX_DURATION dihilangkan, operasi indeks berlanjut hingga selesai atau sampai kegagalan terjadi.
  • MAX_DURATION untuk opsi WAIT_AT_LOW_PRIORITY menentukan waktu untuk menunggu menggunakan kunci prioritas rendah jika operasi indeks diblokir, sebelum mengambil tindakan. Untuk informasi selengkapnya, lihat WAIT_AT_LOW_PRIORITY dengan operasi indeks online.
• Untuk segera menjeda operasi indeks, Anda dapat menjalankan perintah ALTER INDEX PAUSE, atau menjalankan perintah KILL <session_id>.
• Menjalankan kembali pernyataan asli CREATE INDEX dengan parameter yang sama melanjutkan operasi build indeks yang dijeda. Anda juga dapat melanjutkan operasi build indeks yang dijeda dengan menjalankan ALTER INDEX RESUME pernyataan.
• Perintah ABORT mematikan sesi yang menjalankan build indeks dan membatalkan operasi indeks. Anda tidak dapat melanjutkan operasi indeks yang telah dibatalkan.

Operasi indeks yang dapat diulang berjalan hingga selesai, dijeda, atau gagal. Jika operasi dijeda, kesalahan dikeluarkan yang menunjukkan bahwa operasi dijeda dan pembuatan indeks tidak selesai. Jika operasi gagal, kesalahan juga dikeluarkan.

Untuk melihat apakah operasi indeks dijalankan sebagai operasi yang dapat diulang dan untuk memeriksa status eksekusinya saat ini, gunakan tampilan katalog sys.index_resumable_operations.

Sumber

Sumber daya berikut diperlukan untuk operasi indeks yang dapat dilanjutkan:

• Ruang tambahan yang diperlukan untuk menjaga indeks tetap dibangun, termasuk waktu saat build 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.
• Pernyataan DDL yang mencoba mengubah tabel yang terkait dengan indeks yang sedang dibuat saat operasi indeks dijeda tidak diizinkan.
• Pembersihan hantu diblokir pada indeks dalam build selama durasi operasi baik saat dijeda maupun saat operasi sedang berjalan.
• Jika tabel berisi kolom LOB, build indeks berkluster yang dapat dilanjutkan memerlukan penguncian modifikasi skema (Sch-M) di awal operasi.

Batasan fungsi saat ini

Operasi pembuatan indeks yang dapat dilanjutkan memiliki batasan berikut:

• Setelah operasi pembuatan indeks online yang dapat dilanjutkan dijeda, nilai MAXDOP awal tidak dapat diubah.
• Opsi SORT_IN_TEMPDB = ON tidak didukung untuk operasi indeks yang dapat diulang.
• Perintah DDL dengan RESUMABLE = ON tidak dapat dijalankan di dalam transaksi eksplisit.
• Anda tidak dapat membuat indeks yang dapat diulang yang berisi:
  • Kolom komputasi atau timestamp (rowversion) sebagai kolom kunci.
  • Kolom LOB sebagai kolom yang disertakan.
• Operasi indeks yang dapat diulang tidak didukung untuk:
  • Perintah ALTER INDEX REBUILD ALL
  • Perintah ALTER TABLE REBUILD
  • Indeks penyimpanan kolom
  • Indeks yang difilter
  • Indeks yang dinonaktifkan

WAIT_AT_LOW_PRIORITY dengan operasi indeks online

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

Saat Anda tidak menggunakan opsi , WAIT_AT_LOW_PRIORITY semua transaksi pemblokiran aktif yang menahan kunci pada tabel atau indeks harus diselesaikan agar operasi pembuatan indeks dimulai dan selesai. Ketika operasi indeks online dimulai dan sebelum selesai, operasi ini perlu memperoleh kunci bersama (S) atau modifikasi skema (Sch-M) pada tabel dan menahannya untuk waktu yang singkat. Meskipun kunci ditahan hanya untuk waktu yang singkat, kunci mungkin secara signifikan memengaruhi throughput beban kerja, meningkatkan latensi kueri, atau menyebabkan waktu habis eksekusi.

Untuk menghindari masalah ini, opsi WAIT_AT_LOW_PRIORITY memungkinkan Anda mengelola perilaku kunci S atau Sch-M yang diperlukan agar operasi indeks online dimulai dan selesai, memilih dari tiga opsi. Dalam semua kasus, jika selama waktu tunggu yang ditentukan oleh MAX_DURATION = n [minutes] tidak ada pemblokiran yang melibatkan operasi indeks, operasi indeks segera dilanjutkan.

WAIT_AT_LOW_PRIORITY membuat operasi indeks online menunggu menggunakan kunci prioritas rendah, memungkinkan operasi lain menggunakan kunci prioritas normal untuk melanjutkan sementara itu. WAIT_AT_LOW_PRIORITY Menghilangkan opsi setara dengan WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = waktu [MINUTES]

Waktu tunggu (nilai bilangan bulat yang ditentukan dalam menit) yang menunggu operasi indeks online menggunakan kunci prioritas rendah. 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 ]

• NONE: Lanjutkan menunggu kunci dengan prioritas normal.
• SELF: Keluar dari operasi indeks online yang saat ini sedang dijalankan, tanpa mengambil tindakan apa pun. Opsi SELF tidak dapat digunakan saat MAX_DURATION adalah 0.
• BLOCKERS: Matikan semua transaksi pengguna yang memblokir operasi indeks online sehingga operasi dapat dilanjutkan. Opsi BLOCKERS mengharuskan prinsipal menjalankan pernyataan CREATE INDEX atau ALTER INDEX untuk memiliki izin ALTER ANY CONNECTION.

Anda dapat menggunakan peristiwa yang diperluas berikut untuk memantau operasi indeks yang menunggu kunci dengan prioritas rendah:

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

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.

Peringatan

Tidak disarankan untuk menonaktifkan kunci baris atau halaman pada indeks. Masalah terkait konkurensi mungkin terjadi, dan fungsionalitas tertentu mungkin tidak tersedia. Misalnya, indeks tidak dapat diatur ulang saat ALLOW_PAGE_LOCKS diatur ke OFF.

Kunci berurutan

Berlaku untuk: SQL Server 2019 (15.x) dan versi yang lebih baru, di Azure SQL Database, dan di Azure SQL Managed Instance.

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 disisipkan berurutan, semua baris baru 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 untuk memperoleh kait untuk halaman yang dimaksud. Jenis tunggu yang sesuai adalah PAGELATCH_EX.

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

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.

Pemadatan data

Untuk informasi selengkapnya tentang kompresi data, lihat Pemadatan data.

Berikut ini adalah poin-poin penting yang perlu dipertimbangkan dalam konteks operasi build indeks saat pemadatan data digunakan:

• 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.

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

Pemadatan XML

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

Banyak pertimbangan kompresi 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, gunakan xml_compression kolom sys.partitions tampilan katalog.

Statistik indeks

Saat indeks rowstore dibuat, Mesin Database juga membuat statistik pada kolom kunci indeks. Nama objek statistik dalam tampilan katalog sys.stats cocok dengan nama indeks. Untuk indeks non-partisi, statistik dibuat menggunakan pemindaian penuh data. Untuk indeks yang dipartisi, statistik dibangun menggunakan algoritma pengambilan sampel default.

Saat indeks penyimpan kolom dibuat, Mesin Database juga membuat objek statistik di sys.stats . Objek statistik ini tidak berisi data statistik seperti histogram dan vektor kepadatan. Ini digunakan saat membuat klon database dengan membuat skrip database. Pada saat itu, DBCC SHOW_STATISTICS perintah dan UPDATE STATISTICS ... WITH STATS_STREAM digunakan untuk mendapatkan metadata penyimpan kolom seperti segmen, kamus, dan ukuran penyimpanan delta dan menambahkannya ke statistik pada indeks penyimpan kolom. Metadata ini diperoleh secara dinamis pada waktu kompilasi kueri untuk database reguler, tetapi disediakan oleh objek statistik untuk klon database. Perintah PERBARUI STATISTIK tidak didukung untuk objek statistik pada indeks penyimpan kolom dalam skenario lain.

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

• Azure SQL Database tidak mendukung grup file selain PRIMARY.
• Azure SQL Database dan Azure SQL Managed Instance tidak mendukung opsi FILESTREAM.
• Indeks penyimpan kolom tidak tersedia sebelum SQL Server 2012 (11.x).
• Operasi indeks yang dapat dilanjutkan tersedia mulai dari SQL Server 2017 (14.x), di Azure SQL Database, dan di Azure SQL Managed Instance.

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 SalesYTD dan Sales.SalesPerson 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 Production.WorkOrder database dengan menggunakan AdventureWorks2022DROP_EXISTING 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 2019 (15.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance

-- 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 2019 (15.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance

-- 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 c2 dan T1 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 c1 dan c2 tabel dalam T2 database.MyDB

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

Konten terkait

• Arsitektur dan Panduan Desain Indeks SQL Server
• Melakukan Operasi Indeks Online
• Indeks dan UBAH TABEL
• UBAH INDEKS
• MEMBUAT FUNGSI PARTISI
• MEMBUAT SKEMA PARTISI
• MEMBUAT INDEKS SPASIAL
• CREATE STATISTICS
• CREATE TABLE
• BUAT INDEKS XML
• Jenis Data
• DBCC SHOW_STATISTICS
• HILANGKAN INDEKS
• Indeks XML (SQL Server)
• sys.indexes
• sys.index_columns
• sys.xml_indexes
• EVENTDATA