Membuat Indeks Cluster dan Non-Clustered di SQL Server

Di SQL Server, ada dua jenis indeks; Indeks berkerumun dan tidak berkerumun. Baik indeks berkerumun maupun indeks non-klaster memiliki struktur fisik yang sama. Selain itu, keduanya disimpan di SQL Server sebagai struktur B-Tree.

Indeks tergugus:

Daftar berkerumun adalah jenis indeks tertentu yang mengatur ulang penyimpanan fisik catatan dalam tabel. Dalam SQL Server, indeks digunakan untuk mempercepat operasi database, yang menghasilkan kinerja tinggi. Oleh karena itu, tabel hanya dapat memiliki satu indeks berkerumun, yang biasanya dilakukan pada kunci utama. Node daun indeks berkerumun berisi "Halaman data". Sebuah tabel hanya dapat memiliki satu indeks berkerumun.

Mari kita buat indeks berkerumun untuk mendapatkan pemahaman yang lebih baik. Pertama-tama, kita perlu membuat database.

Pembuatan database

Untuk membuat database. Klik kanan “Database” di penjelajah objek, dan pilih "Database baru" pilihan. Ketik nama database dan klik ok. Basis data telah dibuat seperti yang ditunjukkan pada gambar di bawah ini.

Sekarang kita akan membuat tabel bernama "Karyawan" dengan kunci utama menggunakan tampilan desain. Kita dapat melihat pada gambar di bawah ini kami telah menetapkan terutama ke file bernama "ID" dan kami belum membuat indeks apa pun di tabel.

Anda juga dapat membuat tabel dengan menjalankan kode berikut.

GUNAKAN [test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo]. [Karyawan] ([ID] [int] IDENTITAS (1,1) BUKAN NULL, [Dep_ID] [int] NULL, [Nama] [ varchar] (200) NULL, [email] [varchar] (250) NULL, [city] [varchar] (250) NULL, [alamat] [varchar] (500) NULL, CONSTRAINT [Primary_Key_ID] PRIMARY KEY CLUSTERED ([ID ] ASC) DENGAN (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO

Outputnya adalah sebagai berikut.

Kode di atas telah membuat tabel bernama "Karyawan" dengan bidang ID, pengenal unik sebagai kunci utama. Sekarang di tabel ini, indeks berkerumun akan secara otomatis dibuat pada ID kolom karena kendala kunci utama. Jika Anda ingin melihat semua indeks pada tabel, jalankan prosedur tersimpan “Sp_helpindex”. Jalankan kode berikut untuk melihat semua indeks pada tabel bernama "Karyawan". Prosedur penyimpanan ini menggunakan nama tabel sebagai parameter input.

GUNAKAN tes LAKUKAN sp_helpindex Karyawan

Outputnya adalah sebagai berikut.

Cara lain untuk melihat indeks tabel adalah dengan pergi ke "Tabel" di penjelajah objek. Pilih tabel dan keluarkan. Di folder indexes, Anda dapat melihat semua indeks yang relevan dengan tabel spesifik tersebut seperti yang ditunjukkan pada gambar di bawah ini.

Karena ini adalah indeks berkerumun sehingga urutan logis dan fisik indeks akan sama. Ini berarti jika sebuah record memiliki ID 3, maka record tersebut akan disimpan di baris ketiga tabel. Demikian pula, jika catatan kelima memiliki id 6, itu akan disimpan di 5^th lokasi meja. Untuk memahami urutan record, Anda perlu menjalankan skrip berikut.

GUNAKAN [test] GO SET IDENTITY_INSERT [dbo]. [Employee] ON INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) NILAI ( 8, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N'895 E 7th St Saint Paul Mn 551063852') INSERT [dbo]. [Karyawan] ([ID ], [ID_ID], [Nama], [email], [kota], [alamat]) NILAI (9, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ' , N'895 E 7th St Saint Paul Mn 551063852 ') INSERT [dbo]. [Karyawan] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (10, 7, N'Pilar Ackaerman ', N'[email protected]', N'ATLANTA ', N'5813 Eastern Ave Hyattsville Md 207822201') INSERT [dbo]. [Karyawan] ([ID], [Dep_ID] , [Nama], [email], [kota], [alamat]) NILAI (11, 1, N'Aaaronboy Gutierrez ', N'[email protected]', N'HILLSBORO ', N'5840 Ne Cornell Rd Hillsboro Or 97124') INSERT [dbo].[Karyawan] ([ID], [Dep_ID], [Nama], [email], [kota], [alamat]) NILAI (12, 2, N'Aabdi Maghsoudi' , N'[email protected] ', N'BRENTWOOD ', N'987400 Nebraska Medical Center Omaha Ne 681987400') INSERT [dbo]. [Karyawan] ([ID], [Dep_ID], [Name], [email], [city], [address]) NILAI (13, 3, N'Aabharana, Sahni', N'[email protected]', N'HYATTSVILLE', N'2 Barlo Circle Suite A Dillsburg Pa 170191') INSERT [dbo].[Karyawan] ([ ID], [ID_ID], [Nama], [email], [kota], [alamat]) NILAI (14, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191') INSERT [dbo]. [Karyawan] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (1 , 1, N'Aaaronboy Gutierrez ', N'[email protected]', N'HILLSBORO ', N'5840 Ne Cornell Rd Hillsboro Atau 97124') INSERT [dbo]. [Karyawan] ([ID], [ Dep_ID], [Nama], [email], [kota], [alamat]) NILAI (2, 2, N'Aabdi Maghsoudi ', N'[email protected]', N'BRENTWOOD ', N'987400 Nebraska Medical Center Omaha Ne 681987400 ') INSERT [dbo]. [Karyawan] ([ID], [Dep_ID], [Name], [email], [city], [address]) NILAI (3, 3, N'Aabharana, Sahni ' , N'[email protected] ', N'HYATTSVILLE', N'2 Barlo Circle Suite A Dillsburg Pa 170191 ') INSERT [dbo]. [Karyawan] ([ID], [Dep_ID], [Name], [email], [kota], [alamat]) NILAI (4, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191 ') MASUKKAN [dbo]. [Karyawan] ([ID], [Dep_ID], [Nama], [email], [kota], [alamat]) NILAI (5, 4, N'Aabish Mughal', N ' [email protected] ', N'OMAHA', N'2975 Crouse Lane Burlington Nc 272150000 ') INSERT [dbo]. [Karyawan] ([ID], [Dep_ID], [Name], [email], [city] , [alamat]) NILAI (6, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') MASUKKAN [dbo]. [Karyawan ] ([ID], [Dep_ID], [Name], [email], [city], [address]) NILAI (7, 5, N'Aabram Howell ', N'[email protected]', N 'DILLSBURG', N'868 York Ave Atlanta Ga 303102750 ') INSERT [dbo]. [Karyawan] ([ID], [Dep_ID], [Nama], [email], [kota], [alamat]) NILAI (15 , 4, N'Aabish Mughal ', N'abish_mughal @ gmail.com ', N'OMAHA', N'2975 Crouse Lane Burlington Nc 272150000 ') MASUKKAN [dbo]. [Karyawan] ([ID], [ID_DEP], [Nama], [email], [kota], [ alamat]) NILAI (16, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') MASUKKAN [dbo]. [Karyawan] ( [ID], [ID_dep], [Nama], [email], [kota], [alamat]) NILAI (17, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') INSERT [dbo]. [Karyawan] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (18, 6 , N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N'895 E 7th St Saint Paul Mn 551063852') INSERT [dbo]. [Karyawan] ([ID], [ Dep_ID], [Nama], [email], [kota], [alamat]) NILAI (19, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N' 895 E 7th St Saint Paul Mn 551063852 ') INSERT [dbo]. [Karyawan] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (20, 7, N 'Pilar Ackaerman', N'[email protected] ', N'ATLANTA ', N'5813 Eastern Ave Hyattsville Md 207822201') SET IDENTITY_INSERT [dbo]. [Karyawan] MATI

Meskipun catatan disimpan di kolom "Id" dalam urutan nilai acak. Namun karena indeks berkerumun pada kolom id. Rekaman secara fisik disimpan dalam urutan nilai di kolom id. Untuk memverifikasi ini kita perlu menjalankan kode berikut.

Pilih * dari test.dbo.Employee

Outputnya adalah sebagai berikut.

Kita dapat melihat pada gambar di atas, catatan telah diambil dalam urutan nilai menaik di kolom id.

Indeks berkerumun yang disesuaikan

Anda juga dapat membuat indeks berkerumun kustom. Karena kita hanya dapat membuat satu indeks berkerumun jadi kita perlu menghapus yang sebelumnya. Untuk menghapus indeks, jalankan kode berikut.

GUNAKAN [test] GO ALTER TABLE [dbo]. [Karyawan] BATAS BATAS [Primary_Key_ID] DENGAN (ONLINE = OFF) GO

Outputnya adalah sebagai berikut.

Sekarang untuk membuat indeks, jalankan kode berikut di jendela kueri. Indeks ini telah dibuat pada lebih dari satu kolom sehingga disebut indeks komposit.

GUNAKAN [test] GO CREATE CLUSTERED INDEX [ClusteredIndex-20191128-173307] AKTIF [dbo]. [Karyawan] ([ID] ASC, [Dep_ID] ASC) DENGAN (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

Outputnya adalah sebagai berikut

Kami telah membuat indeks berkerumun khusus pada ID dan Dep_ID. Ini akan mengurutkan baris menurut Id dan kemudian menurut Dep_Id. Untuk melihatnya, jalankan kode berikut. Hasilnya adalah urutan ID dan kemudian By Dep_id.

PILIH [ID], [ID_dep], [Nama], [email], [kota], [alamat] DARI [tes]. [Dbo]. [Karyawan]

Outputnya adalah sebagai berikut.

Indeks tidak berkerumun:

Indeks non-cluster adalah jenis indeks tertentu di mana urutan logis indeks tidak cocok dengan urutan fisik baris yang disimpan di disk. Node daun dari indeks non-cluster tidak berisi halaman data melainkan berisi informasi tentang baris indeks. Sebuah tabel dapat memiliki hingga 249 indeks. Secara default, pembatasan Unique Key membuat Indeks Nonclustered. Dalam operasi baca, indeks yang tidak berkerumun lebih lambat dari indeks berkerumun. Indeks non-cluster memiliki salinan data dari kolom yang diindeks yang disimpan secara berurutan bersama dengan referensi ke baris data aktual; petunjuk ke daftar berkerumun jika ada. Oleh karena itu, sebaiknya pilih hanya kolom yang digunakan dalam indeks daripada menggunakan *. Dengan cara ini data dapat diambil langsung dari indeks duplikat. Indeks berkerumun juga digunakan untuk memilih kolom yang tersisa jika dibuat.

Sintaks yang digunakan untuk membuat indeks nonclustered mirip dengan indeks berkerumun. Namun, kata kuncinya “NONCLUSTERED” digunakan sebagai pengganti “TERKELOMPOK” dalam kasus indeks non-cluster. Jalankan skrip berikut untuk membuat indeks tidak berkerumun.

GUNAKAN [test] GO SET ANSI_PADDING ON GO BUAT INDEKS NONCLUSTERED [NonClusteredIndex-20191129-104230] ON [dbo]. [Karyawan] ([Nama] ASC) DENGAN (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF , ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

Outputnya akan seperti berikut.

Catatan tabel diurutkan menurut indeks berkerumun jika telah dibuat. Indeks non-cluster baru ini akan mengurutkan tabel menurut definisinya dan akan disimpan di alamat fisik yang terpisah. Script di atas akan membuat indeks pada kolom "NAMA" pada tabel Karyawan. Indeks ini akan mengurutkan tabel dalam urutan menaik dari kolom "Nama". Data tabel dan indeks akan disimpan di lokasi yang berbeda, seperti yang kami katakan sebelumnya. Sekarang jalankan skrip berikut untuk melihat dampak dari indeks non cluster baru.

pilih Nama dari Karyawan

Outputnya adalah sebagai berikut.

Kita dapat melihat pada gambar di atas bahwa kolom Name pada tabel Employee telah ditampilkan dengan urutan menaik dari kolom name, walaupun kita belum menyebutkan klausa “Order by ASC” dengan klausa select. Ini karena indeks non-cluster pada kolom "Nama" yang dibuat di tabel Karyawan. Sekarang jika kueri ditulis untuk mengambil Nama, email, kota, dan alamat orang tertentu. Basis data pertama-tama akan mencari nama spesifik itu di dalam indeks, dan kemudian mengambil data yang relevan yang akan mengurangi waktu pengambilan kueri, terutama ketika datanya sangat besar.

pilih Name, email, city, address from Employee where name='Aaaronboy Gutierrez'

Kesimpulan

Dari pembahasan di atas, kita mengetahui bahwa clustered index hanya bisa satu sedangkan non clustered index bisa banyak. Indeks berkerumun lebih cepat dibandingkan dengan indeks non-cluster. Indeks berkerumun tidak mengkonsumsi ruang penyimpanan ekstra sedangkan indeks non-cluster membutuhkan memori ekstra untuk menyimpannya. Jika kita menerapkan batasan kunci utama pada tabel indeks cluster secara otomatis dibuat di atasnya. Selain itu, jika kita menerapkan batasan kunci unik pada kolom mana pun, indeks non-clustered secara otomatis dibuat di atasnya. Indeks tidak berkerumun lebih cepat dibandingkan dengan indeks berkerumun untuk operasi penyisipan dan pembaruan. Tabel mungkin tidak memiliki indeks non-cluster.