Bagikan melalui

Menggunakan kolom jarang

  • Artikel

Berlaku untuk: SQL Server 2016 (13.x) dan Azure SQL Database Azure SQL Managed Instance yang lebih baru

Kolom jarang adalah kolom biasa yang memiliki penyimpanan yang dioptimalkan untuk nilai null. Kolom jarang mengurangi persyaratan ruang untuk nilai null dengan biaya lebih banyak overhead untuk mengambil nilai non-NULL. Pertimbangkan untuk menggunakan kolom jarang saat ruang yang disimpan setidaknya 20 persen hingga 40 persen. Kolom jarang dan kumpulan kolom ditentukan dengan menggunakan pernyataan CREATE TABLE atau ALTER TABLE .

Kolom jarang dapat digunakan dengan kumpulan kolom dan indeks yang difilter:

  • Kumpulan kolom

    Pernyataan INSERT, UPDATE, dan DELETE dapat mereferensikan kolom jarang berdasarkan nama. Namun, Anda juga dapat menampilkan dan bekerja dengan semua kolom jarang 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 jarang memiliki banyak baris 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 dengan untuk 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:

geografi
geometri
gambar
ntext

text
timestamp
jenis data yang ditentukan pengguna

Estimasi penghematan ruang berdasarkan jenis data

Kolom jarang memerlukan lebih banyak ruang penyimpanan untuk nilai non-NULL daripada ruang yang diperlukan untuk data identik yang tidak ditandai SPARSE. Tabel berikut ini memperlihatkan penggunaan ruang untuk setiap jenis data. Kolom Persentase NULL menunjukkan persentase data yang harus NULL untuk penghematan ruang bersih 40 persen.

Tipe Data Panjang Tetap

Jenis Data Byte nonsparse Byte jarang Persentase NULL
bit 0,125 5 98%
kecil 1 5 86%
smallint 2 6 76%
int 4 8 64%
bigint 8 12 52%
real 4 8 64%
float 8 12 52%
smallmoney 4 8 64%
uang 8 12 52%
smalldatetime 4 8 64%
datetime 8 12 52%
pengidentifikasi unik 16 20 43%
date 3 7 69%

Jenis Data Presisi-Panjang Dependen

Jenis Data Byte nonsparse Byte jarang Persentase NULL
datetime2(0) 6 10 57%
datetime2(7) 8 12 52%
time(0) 3 7 69%
time(7) 5 9 60%
datetimetoffset(0) 8 12 52%
datetimetoffset (7) 10 14 49%
desimal/numerik(1,s) 5 9 60%
desimal/numerik(38,s) 17 21 42%
vardecimal(p,s) Gunakan jenis desimal sebagai perkiraan konservatif.

Jenis data Dengan Panjang Dependen Data

Jenis Data Byte nonsparse Byte jarang Persentase NULL
aql_variant Bervariasi dengan jenis data yang mendasarinya
varchar atau karakter 2* 4* 60%
nvarchar atau nchar 2* 4*+ 60%
varbinary atau biner 2* 4* 60%
xml 2* 4* 60%
hierarchyid 2* 4* 60%

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

Overhead Dalam Memori diperlukan untuk pembaruan ke kolom jarang

Saat mendesain tabel dengan kolom jarang, perlu diingat bahwa tambahan 2 byte overhead diperlukan untuk setiap kolom jarang non-null dalam tabel saat baris sedang diperbarui. 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 didorong dari baris.

Pertimbangkan contoh tabel yang memiliki 600 kolom jarang jenis bigint. Jika ada 571 kolom non-null, maka ukuran total pada disk adalah 571 * 12 = 6852 byte. Setelah menyertakan overhead baris tambahan dan header kolom 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 jarang non-null). Dalam contoh ini, termasuk overhead tambahan - 2 * 571 = 1142 byte - meningkatkan ukuran baris pada 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 untuk menggunakan kolom jarang

Kolom jarang bisa dari jenis data SQL Server apa pun dan berperilaku seperti kolom lain dengan batasan berikut:

  • Kolom jarang harus dapat diubah ke null dan tidak boleh memiliki properti ROWGUIDCOL atau IDENTITY. Kolom jarang tidak boleh dari jenis data berikut: teks, ntext, gambar, tanda waktu, jenis data yang ditentukan pengguna, geometri, atau geografi; atau memiliki atribut FILESTREAM.

  • Kolom jarang tidak boleh memiliki nilai default.

  • Kolom jarang tidak dapat terikat ke aturan.

  • Meskipun kolom komputasi dapat berisi kolom jarang, kolom komputasi 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 atau timbunan berkluster. Namun, kolom jarang dapat digunakan sebagai kunci partisi dari indeks nonclustered.

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

  • Kolom jarang tidak kompatibel 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 didorong dari baris.

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

Teknologi SQL Server yang mendukung kolom jarang

Bagian ini menjelaskan bagaimana kolom jarang 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 jarang. 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.

  • Penggabungan replikasi

    Replikasi penggabungan tidak mendukung kolom jarang atau kumpulan kolom.

  • Pelacakan perubahan

    Pelacakan perubahan mendukung kolom jarang dan kumpulan 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.

  • Properti jarang 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 kolom dan ProductionLocation untuk semua dokumen produksi. Grup Pemasaran menginginkan MarketingSurveyGroup kolom untuk dokumen pemasaran. Kode dalam contoh ini membuat tabel yang menggunakan kolom jarang, menyisipkan dua baris ke dalam tabel, lalu memilih data dari tabel.

Catatan

Tabel ini hanya memiliki lima kolom untuk mempermudah tampilan dan pembacaan. Mendeklarasikan kolom jarang menjadi nullable bersifat opsional jika opsi ANSI_NULL_DFLT_ON diatur. Saat SET ANSI_DEFAULTS AKTIF, SET ANSI_NULL_DFLT_ON diaktifkan. ANSI_DEFAULTS AKTIF secara default untuk sebagian besar penyedia koneksi. Untuk informasi selengkapnya, lihat MENGATUR ANSI_DEFAULTS.

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 mengembalikan tataan hasil biasa.

SELECT * FROM DocumentStore ;

Berikut set 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 set hasilnya.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

Lihat juga