CREATE INDEX (Transact-SQL)

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

Membuat indeks relasional pada tabel atau tampilan. Juga disebut indeks rowstore karena merupakan indeks pohon B berkluster atau non-kluster. 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

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

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:

  1. Membuat indeks nonkluster 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) 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:

tautan topikIkon Konvensi Sintaks Transact-SQL

Sintaks

Sintaks untuk database SQL Server dan Azure SQL

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 kompatibel mundur

Penting

Struktur sintaks indeks relasional yang kompatibel mundur akan dihapus dalam versi SQL Server mendatang. 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 Parallel Data Warehouse


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 } )
[;]

Catatan

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

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 terkait dalam tabel. Tingkat bawah, atau daun, 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. 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 cukup ruang kosong 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 BERKLUSTER

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

index_name

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

Kolom

Kolom atau kolom yang menjadi dasar indeks. Tentukan dua atau beberapa nama kolom 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 diizinkan 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 jenis 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 urutan 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 nonkluster 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 non-kluster 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 database Azure SQL, 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 dikomputasi 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 jenis 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 yang unik. Indeks yang difilter tidak memperbolehkan 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 jenis 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 UNIQUE, 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 non-kluster, 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 indeks partisi, Tabel Terpartisi, dan Indeks.

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.

ON "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 |partition_scheme_name { filestream_filegroup_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 klausul .

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.

nama_skema

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

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 mendekati kapasitas, menyisakan ruang yang cukup untuk setidaknya satu baris dari ukuran maksimum yang dapat dimiliki indeks, dengan mempertimbangkan kumpulan 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 perantara 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 dari 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 ke kapasitas.

Pengaturan FILLFACTOR 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 = { ON | OFF }

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 pada sekumpulan 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 = { ON | OFF }

Menentukan respons kesalahan saat operasi penyisipan mencoba menyisipkan nilai kunci duplikat ke dalam indeks unik. Opsi IGNORE_DUP_KEY ini hanya berlaku untuk menyisipkan operasi setelah indeks dibuat atau dibangun kembali. 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 = { ON | OFF}

Menentukan apakah statistik distribusi dikomputasi ulang. Defaultnya adalah NONAKTIF.

AKTIF
Statistik kedaluarsa tidak dikomputasi ulang secara otomatis.

TIDAK AKTIF
Pembaruan statistik otomatis diaktifkan.

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

Penting

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

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

STATISTICS_INCREMENTAL = { ON | OFF }

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

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

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

  • Statistik yang dibuat dengan indeks yang tidak selaras dengan partisi 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 = { ON | OFF }

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 ke DROP dan REBUILD indeks yang ada, yang harus memiliki nama yang sama dengan parameter index_name.

TIDAK AKTIF
Menentukan bukan ke DROP dan REBUILD 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 = { ON | OFF }

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 untuk SQL Server 2016.

AKTIF
Kunci tabel jangka panjang tidak ditahan selama operasi indeks. Selama fase utama operasi indeks, hanya kunci Berbagi Niat (IS) yang ditahan pada tabel sumber. Ini memungkinkan kueri atau pembaruan ke 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 nonkluster memperoleh kunci Bersama (S) pada tabel. Ini mencegah pembaruan pada tabel yang mendasar tetapi memungkinkan operasi baca, seperti pernyataan SELECT.

Untuk informasi selengkapnya, lihat Melakukan Operasi Indeks Online.

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

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

Untuk informasi selengkapnya, lihat Cara Kerja Operasi Indeks Online.

DAPAT DIMUAT ULANG = { ON | OFF }

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

Menentukan apakah operasi indeks online dapat diulang.

AKTIF
Operasi indeks dapat diulang.

TIDAK AKTIF
Operasi indeks tidak dapat diulang.

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

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

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

Penting

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

Catatan

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

ALLOW_ROW_LOCKS = { ON | OFF }

Menentukan apakah kunci baris diperbolehkan. Defaultnya adalah AKTIF.

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

TIDAK AKTIF
Kunci baris tidak digunakan.

ALLOW_PAGE_LOCKS = { ON | OFF }

Menentukan apakah kunci halaman diperbolehkan. Defaultnya adalah AKTIF.

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

TIDAK AKTIF
Kunci halaman tidak digunakan.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

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

Menentukan apakah akan mengoptimalkan ketidakcocokan sisipan halaman terakhir atau tidak. Defaultnya adalah NONAKTIF. 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 untuk SQL Server 2016 dan Edisi dan Fitur yang Didukung untuk SQL Server 2017.

DATA_COMPRESSION

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

TIDAK ADA
Indeks atau partisi yang ditentukan tidak dikompresi.

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

PAGE
Indeks atau partisi yang ditentukan dikompresi dengan menggunakan pemadatan halaman.

Untuk informasi selengkapnya tentang pemadatan, lihat Kompresi Data.

XML_COMPRESSION

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

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

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

TIDAK AKTIF
Indeks atau partisi yang ditentukan tidak dikompresi.

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

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

Catatan

XML_COMPRESSIONhanya tersedia mulai dari Pratinjau SQL Server 2022 (16.x), dan Pratinjau Database Azure SQL.

<partition_number_expression> dapat ditentukan dengan cara berikut:

  • Berikan nomor untuk partisi, misalnya: ON PARTITIONS (2).
  • Berikan nomor partisi untuk beberapa partisi individual yang dipisahkan oleh koma, misalnya: ON PARTITIONS (1, 5).
  • 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 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 dihilangkan.

Indeks mendukung properti yang diperluas.

Indeks berkluster

Membuat indeks berkluster pada tabel (heap) atau menghilangkan 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 berkluster yang ada. Untuk informasi selengkapnya tentang indeks berkluster, lihat Membuat Indeks Berkluster dan Panduan Arsitektur dan 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 terlebih dahulu membuat indeks non-kluster pada tabel yang disimpan sebagai tumpukan atau indeks 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.

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

Saat 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 penyisipan mengubah banyak baris tetapi hanya menyebabkan satu duplikat. Jika upaya dilakukan untuk memasukkan data yang indeksnya 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 dipertahankan dengan cara yang sama dengan tabel yang dipartisi, tetapi seperti indeks biasa, indeks 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 mendasarinya. 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 Panduan Arsitektur dan Desain Indeks SQL Server.

Dalam SQL Server, statistik tidak dibuat dengan memindai semua baris dalam tabel saat indeks yang dipartisi dibuat atau dibangun kembali. Sebaliknya, 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.

    Opsi SET Nilai yang diperlukan Nilai server default Default

    Nilai OLE DB dan ODBC
    Default

    nilai DB-Library
    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 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 T-SQL apa pun.

Untuk informasi selengkapnya tentang Indeks yang Difilter, lihat Membuat Indeks Yang Difilter dan Panduan Arsitektur dan 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, BUAT 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 nonkluster dapat menyertakan kolom non-kunci di 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 Panduan Arsitektur dan 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 bertahan ini saat membuat indeks pada kolom, dan saat 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 disertakan:

  • 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 jenis 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 penyisipan atau pembaruan 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 nonkluster 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 berkluster tidak diakses. Untuk informasi selengkapnya, lihat Membuat Indeks dengan Kolom yang Disertakan dan Panduan Arsitektur dan 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 klausul 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 UNIQUE, 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 nonkluster dibangun kembali sekali, dan kemudian hanya jika definisi indeks telah berubah. Klausa DROP_EXISTING tidak membangun kembali indeks non-kluster 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 selalu tetap dalam grup file atau skema partisi asli mereka 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 OFF. 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 tingkat 128 atau lebih dihilangkan atau dibangun kembali, Mesin Database menangguhkan dealokasi halaman aktual, dan kunci terkaitnya, sampai 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 bertahan, atau kolom yang disertakan.
  • low_priority_lock_wait Opsi argumen memungkinkan Anda memutuskan bagaimana operasi indeks dapat dilanjutkan ketika diblokir pada kunci Sch-M. Ini saat ini hanya didukung di Azure SQL Database dan Azure SQL Managed Instance.

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 lebih sedikit dibandingkan dengan pembuatan indeks online reguler dan memungkinkan pemotokan log selama operasi ini.
  • Status DDL mencegah modifikasi DDL
  • Pembersihan ghost 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 Database Azure SQL

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 kembali.
  • 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 keduanya mengindeks yang asli dan yang baru dibuat memerlukan ruang disk dan perlu diperbarui selama operasi DML. Jika MAX_DURATION opsi dihilangkan, operasi indeks akan berlanjut hingga selesai atau sampai kegagalan terjadi.
  • 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 T-SQL ALTER INDEX

Catatan

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

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 hanya berlaku untuk Azure SQL Database dan Azure SQL Managed Instance. Untuk ALTER INDEX, sintaks ini berlaku untuk SQL Server (Dimulai dengan SQL Server 2014 (12.x)) dan database Azure SQL. Untuk informasi selengkapnya, lihat MENGUBAH INDEKS.

Sintaks low_priority_lock_wait memungkinkan untuk menentukan WAIT_AT_LOW_PRIORITY perilaku. WAIT_AT_LOW_PRIORITYhanya 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 [MINUTES]

Waktu tunggu (nilai bilangan bulat yang ditentukan dalam menit) yang dibuat indeks online mengunci akan menunggu dengan prioritas rendah saat menjalankan perintah DDL. Jika operasi diblokir untuk waktu tersebut MAX_DURATION , tindakan yang ditentukan ABORT_AFTER_WAIT akan dijalankan. MAX_DURATION waktu selalu dalam hitungan menit, dan kata MINUTES dapat dihilangkan.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

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 Hentikan 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 Database Azure SQL

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

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

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 nonkluster memiliki pengaturan kompresi individual, dan tidak mewarisi pengaturan pemadatan tabel yang mendasar.
  • Ketika indeks berkluster dibuat pada tumpukan, indeks berkluster mewarisi status kompresi timbunan kecuali status kompresi alternatif ditentukan.

Pembatasan berikut berlaku untuk indeks yang dipartisi:

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

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

Pemadatan XML

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

Banyak pertimbangan yang sama untuk kompresi data berlaku untuk pemadatan 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 pemadatan XML. Saat tabel atau indeks dibuat, pemadatan data XML dinonaktifkan kecuali ditentukan lain. Saat tabel dimodifikasi, kompresi 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 heap 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 terkluster atau non-kluster pada tabel gudang data saat indeks penyimpan kolom sudah ada. Perilaku ini berbeda dari SQL Server SMP yang memungkinkan indeks penyimpanan baris dan penyimpan kolom untuk berdampingan pada tabel yang sama.
  • Anda tidak dapat membuat indeks pada tampilan.

Metadata

Untuk menampilkan 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 nonkluster sederhana

Contoh berikut membuat indeks nonkluster 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 nonkluster sederhana

Contoh berikut membuat indeks komposit nonkluster 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 nonkluster unik

Contoh berikut membuat indeks nonkluster unik pada Name kolom Production.UnitMeasure tabel dalam AdventureWorks2012 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 sekali 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. Hitungan 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 disisipkan. 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 AdventureWorks2012 database dengan menggunakan DROP_EXISTINGProduction.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 nonkluster dengan satu kolom kunci (PostalCode) dan empat kolom non-kunci (AddressLine1, AddressLine2, City, StateProvinceID). Kueri yang dicakup oleh indeks mengikuti. 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 nonkluster pada TransactionsPS1, skema partisi yang ada dalam AdventureWorks2012 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 AdventureWorks2012 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 indeks dan pemadatan 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 pemadatan XML

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

Contoh berikut membuat indeks pada tabel yang tidak dipartisi dengan menggunakan pemadatan 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 Database Azure SQL

-- 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 RESUMABLE opsi .

--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 Database Azure SQL

-- 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 nonkluster pada tabel di database saat ini

Contoh berikut membuat indeks nonkluster 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 nonkluster 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);

Lihat juga