Menggunakan kolom jarang

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru Azure SQL Databasedatabase Azure SQL Managed InstanceSQL di Microsoft Fabric

Kolom sparse adalah kolom biasa yang memiliki penyimpanan yang dioptimalkan untuk nilai-nilai null. Kolom Sparse menghemat ruang untuk nilai null dengan konsekuensi adanya lebih banyak beban kerja untuk mengakses nilai non-NULL. Pertimbangkan untuk menggunakan kolom jarang saat penghematan ruang setidaknya 20 persen hingga 40 persen. Kolom jarang dan kumpulan kolom ditentukan dengan menggunakan pernyataan CREATE TABLE atau ALTER TABLE .

Kolom jarang digunakan dapat digunakan dengan kumpulan kolom dan indeks yang difilter dengan kriteria tertentu.

• Kumpulan kolom

  Pernyataan INSERT, UPDATE, dan DELETE dapat mereferensikan kolom jarang berdasarkan nama. Namun, Anda juga dapat menampilkan dan bekerja dengan semua kolom jarang dari sebuah tabel yang digabungkan ke dalam satu kolom XML. Kolom ini disebut kumpulan kolom. Untuk informasi selengkapnya tentang kumpulan kolom, lihat Menggunakan Kumpulan Kolom.

• Indeks yang difilter

  Karena kolom berkepadatan rendah memiliki banyak baris yang bernilai null, kolom tersebut sangat sesuai untuk indeks yang difilter. Indeks yang difilter pada kolom jarang hanya dapat mengindeks baris yang memiliki nilai terisi. Ini menciptakan indeks yang lebih kecil dan lebih efisien. Untuk informasi selengkapnya, lihat Membuat Indeks Terfilter.

Kolom jarang dan indeks yang difilter memungkinkan aplikasi, seperti Layanan Windows SharePoint, untuk menyimpan dan mengakses sejumlah besar properti yang ditentukan pengguna dengan menggunakan SQL Server secara efisien.

Properti Kolom Jarang

Kolom jarang memiliki karakteristik berikut:

• Mesin Database SQL Server menggunakan kata kunci SPARSE dalam definisi kolom untuk mengoptimalkan penyimpanan nilai di kolom tersebut. Oleh karena itu, ketika nilai kolom adalah NULL untuk baris apa pun dalam tabel, nilai tidak memerlukan penyimpanan.

• Tampilan katalog untuk tabel yang memiliki kolom jarang sama seperti tabel biasa. Tampilan sys.columns katalog berisi baris untuk setiap kolom dalam tabel dan menyertakan kumpulan kolom jika ditentukan.

• Kolom jarang adalah properti lapisan penyimpanan, bukan tabel logis. SELECT ... INTO Oleh karena itu pernyataan tidak menyalin properti kolom jarang ke dalam tabel baru.

• Fungsi COLUMNS_UPDATED mengembalikan nilai varbinary untuk menunjukkan semua kolom yang diperbarui selama tindakan DML. Bit yang dikembalikan oleh fungsi COLUMNS_UPDATED adalah sebagai berikut:

  • Saat kolom jarang diperbarui secara eksplisit, bit yang sesuai untuk kolom jarang tersebut diatur ke 1, dan bit untuk kumpulan kolom diatur ke 1.

  • Saat kumpulan kolom diperbarui secara eksplisit, bit untuk kumpulan kolom diatur ke 1, dan bit untuk semua kolom jarang dalam tabel tersebut diatur ke 1.

  • Untuk operasi sisipan, semua bit diatur ke 1.

  Untuk informasi selengkapnya tentang kumpulan kolom, lihat Menggunakan Kumpulan Kolom.

Jenis data berikut tidak dapat ditentukan sebagai SPARSE:

Estimasi penghematan ruang berdasarkan jenis data

Kolom bertipis memerlukan lebih banyak penyimpanan untuk nilai non-NULL dibandingkan dengan data identik yang tidak ditandai SPARSE. Tabel berikut ini memperlihatkan penggunaan ruang untuk setiap jenis data. Kolom Persentase NULL menunjukkan persentase data yang harus berstatus NULL untuk mencapai penghematan bersih ruang sebesar 40 persen.

Tipe Data Panjang Tetap

Jenis Data Dengan Panjang Tergantung Presisi

Jenis Data dengan Panjang Bergantung pada Data

*Panjangnya sama dengan rata-rata data yang terkandung dalam jenis, ditambah 2 atau 4 byte.

Overhead memori diperlukan untuk pembaruan ke kolom jarang terisi

Saat mendesain tabel dengan kolom jarang, perlu diingat bahwa diperlukan overhead tambahan sebesar 2 byte untuk setiap kolom jarang non-null dalam tabel saat baris sedang diperbarui. Sebagai akibat dari persyaratan memori tambahan ini, pembaruan dapat gagal secara tiba-tiba dengan kesalahan 576 ketika ukuran baris total, termasuk overhead memori ini, melebihi 8019, dan tidak ada kolom yang dapat dipindahkan dari baris.

Pertimbangkan contoh tabel yang memiliki 600 kolom bertipe bigint yang jarang. Jika ada 571 kolom non-null, maka ukuran total pada disk adalah 571 * 12 = 6852 byte. Setelah menyertakan overhead baris tambahan dan header kolom yang jarang, ini meningkat menjadi sekitar 6895 byte. Halaman ini masih memiliki sekitar 1124 byte yang tersedia pada disk. Ini dapat memberi kesan bahwa kolom tambahan dapat berhasil diperbarui. Namun, selama pembaruan, ada overhead tambahan dalam memori yaitu 2*(jumlah kolom sparce yang tidak bernilai null). Dalam contoh ini, termasuk overhead tambahan - 2 * 571 = 1142 byte - meningkatkan ukuran baris di disk menjadi sekitar 8037 byte. Ukuran ini melebihi ukuran maksimum yang diizinkan sebesar 8019 byte. Karena semua kolom adalah jenis data dengan panjang tetap, kolom tidak dapat didorong dari baris. Akibatnya, pembaruan gagal dengan kesalahan 576.

Pembatasan dalam penggunaan kolom jarang

Kolom dengan sifat jarang dapat menggunakan jenis data SQL Server apa pun dan berfungsi seperti kolom lainnya dengan batasan berikut:

• Kolom jarang harus bernilai null dan tidak boleh memiliki properti ROWGUIDCOL atau IDENTITY. Kolom jarang tidak boleh dari jenis data berikut: text, ntext, image, timestamp, jenis data yang ditentukan pengguna, geometry, atau geography; atau memiliki atribut FILESTREAM.

• Kolom jarang tidak boleh memiliki nilai default.

• Kolom jarang tidak dapat terikat ke aturan.

• Meskipun kolom terhitung dapat berisi kolom sparse, kolom terhitung tidak dapat ditandai sebagai SPARSE.

• Masker data dapat ditentukan pada kolom jarang, tetapi tidak pada kolom jarang yang merupakan bagian dari kumpulan kolom.

• Kolom jarang tidak dapat menjadi bagian dari indeks berkluster atau indeks kunci primer yang unik. Namun, kolom komputasi yang bertahan dan tidak bertahan yang ditentukan pada kolom jarang dapat menjadi bagian dari kunci berkluster.

• Kolom jarang tidak dapat digunakan sebagai kunci partisi dari indeks berkluster atau tumpukan. Namun, kolom tersebar dapat digunakan sebagai kunci partisi dari indeks nonclustered.

• Kolom jarang tidak dapat menjadi bagian dari jenis tabel yang ditetapkan oleh pengguna, yang digunakan dalam variabel tabel dan parameter bernilai tabel.

• Kolom jarang tidak cocok dengan kompresi data. Oleh karena itu kolom jarang tidak dapat ditambahkan ke tabel terkompresi, juga tidak boleh ada tabel yang berisi kolom jarang yang dikompresi.

• Mengubah kolom dari jarang menjadi tidak jarang, atau tidak jarang menjadi jarang, memerlukan perubahan format penyimpanan kolom. Mesin Database SQL Server menggunakan prosedur berikut untuk menyelesaikan perubahan ini:

  1. Menambahkan kolom baru ke tabel dalam ukuran dan format penyimpanan baru.

  2. Untuk setiap baris dalam tabel, perbarui dan salin nilai yang disimpan di kolom lama ke kolom baru.

  3. Menghapus kolom lama dari skema tabel.

  4. Membangun kembali tabel (jika tidak ada indeks berkluster) atau membangun kembali indeks berkluster untuk mengklaim kembali ruang yang digunakan oleh kolom lama.

  Catatan

  Langkah 2 dapat gagal ketika ukuran data dalam baris melebihi ukuran baris maksimum yang diizinkan. Ukuran ini mencakup ukuran data yang disimpan di kolom lama dan data yang diperbarui yang disimpan di kolom baru. Batas ini adalah 8060 byte untuk tabel yang tidak berisi kolom jarang atau 8018 byte untuk tabel yang berisi kolom jarang. Kesalahan ini dapat terjadi bahkan jika semua kolom yang memenuhi syarat telah dipindahkan dari baris.

• Saat Anda mengubah kolom non-jarang menjadi kolom jarang, kolom jarang akan menggunakan lebih banyak ruang untuk nilai yang tidak null. Ketika baris mendekati batas ukuran baris maksimum, operasi dapat gagal.

Teknologi SQL Server yang mendukung kolom sparse

Bagian ini menjelaskan bagaimana kolom sparsi didukung dalam teknologi SQL Server berikut:

• Replikasi Transaksional

  Replikasi transaksional mendukung kolom jarang, tetapi tidak mendukung kumpulan kolom, yang dapat digunakan dengan kolom jarang. Untuk informasi selengkapnya tentang kumpulan kolom, lihat Menggunakan Kumpulan Kolom.

  Replikasi atribut SPARSE ditentukan oleh opsi skema yang ditentukan dengan menggunakan sp_addarticle atau dengan menggunakan kotak dialog Properti Artikel di SQL Server Management Studio. Versi SQL Server yang lebih lama tidak mendukung kolom sparse. Jika Anda harus mereplikasi data ke versi yang lebih lama, tentukan bahwa atribut SPARSE tidak boleh direplikasi.

  Untuk tabel yang diterbitkan, Anda tidak dapat menambahkan kolom jarang baru ke tabel atau mengubah properti jarang kolom yang sudah ada. Jika operasi seperti itu diperlukan, hilangkan dan buat ulang publikasi.

• Replikasi penggabungan

  Replikasi penggabungan tidak mendukung kolom sparse atau kumpulan kolom.

• Pelacakan perubahan

  Pelacakan perubahan mendukung kolom tipis dan himpunan kolom. Saat kumpulan kolom diperbarui dalam tabel, pelacakan perubahan memperlakukan ini sebagai pembaruan ke seluruh baris. Tidak ada pelacakan perubahan terperinci yang disediakan untuk mendapatkan kumpulan kolom jarang yang tepat yang diperbarui melalui operasi pembaruan kumpulan kolom. Jika kolom jarang diperbarui secara eksplisit melalui pernyataan DML, pelacakan perubahan pada kolom tersebut akan berfungsi secara biasa dan dapat mengidentifikasi kumpulan kolom yang diubah.

• Mengubah pengambilan data

  Mengubah tangkapan data mendukung kolom jarang, tetapi tidak mendukung kumpulan kolom.

• Sifat jarang dari sebuah kolom tidak dipertahankan saat tabel disalin.

Contoh

Dalam contoh ini, tabel dokumen berisi set umum yang memiliki kolom DocID dan Title. Grup Produksi menginginkan ProductionSpecification dan ProductionLocation kolom untuk semua dokumen produksi. Grup Pemasaran menginginkan kolom MarketingSurveyGroup untuk dokumen pemasaran. Kode dalam contoh ini membuat tabel yang menggunakan kolom jarang, menyisipkan dua baris ke dalam tabel, lalu memilih data dari tabel.

USE AdventureWorks2022;  
GO  
  
CREATE TABLE DocumentStore  
    (DocID int PRIMARY KEY,  
     Title varchar(200) NOT NULL,  
     ProductionSpecification varchar(20) SPARSE NULL,  
     ProductionLocation smallint SPARSE NULL,  
     MarketingSurveyGroup varchar(20) SPARSE NULL ) ;  
GO  
  
INSERT DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)  
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);  
GO  
  
INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)  
VALUES (2, 'Survey 2142', 'Men 25 - 35');  
GO  

Untuk memilih semua kolom dari tabel menghasilkan set hasil biasa.

SELECT * FROM DocumentStore ;  

Berikut adalah hasilnya.

DocID Title ProductionSpecification ProductionLocation MarketingSurveyGroup

1 Tire Spec 1 AXZZ217 27 NULL

2 Survey 2142 NULL NULL Men 25-35

Karena departemen Produksi tidak tertarik dengan data pemasaran, mereka ingin menggunakan daftar kolom yang hanya mengembalikan kolom yang menarik, seperti yang diperlihatkan dalam kueri berikut.

SELECT DocID, Title, ProductionSpecification, ProductionLocation   
FROM DocumentStore   
WHERE ProductionSpecification IS NOT NULL ;  

Berikut hasilnya.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

Lihat juga

• Menggunakan kumpulan kolom
• BUAT TABEL (Transact-SQL)
• ALTER TABLE (Transact-SQL)
• sys.columns (Transact-SQL)