Bagikan melalui

Menggunakan kumpulan kolom

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru Azure SQL DatabaseAzure SQL Managed Instance SQL databasedi Microsoft Fabric

Tabel yang menggunakan kolom jarang dapat menunjuk kumpulan kolom untuk mengembalikan semua kolom jarang dalam tabel. Kumpulan kolom adalah representasi XML yang tidak bertipe yang menggabungkan semua kolom jarang dari sebuah tabel ke dalam keluaran yang terstruktur. Kumpulan kolom mirip dengan kolom terhitung karena kumpulan kolom tidak disimpan secara fisik dalam tabel. Kumpulan kolom berbeda dari kolom terhitung dalam hal kumpulan kolom langsung dapat diperbarui.

Anda harus mempertimbangkan untuk menggunakan kumpulan kolom ketika jumlah kolom dalam tabel besar, dan mengoperasikannya satu per satu rumit. Aplikasi mungkin melihat beberapa peningkatan performa saat memilih dan menyisipkan data dengan menggunakan kumpulan kolom pada tabel yang memiliki banyak kolom. Namun, performa kumpulan kolom dapat dikurangi ketika banyak indeks ditentukan pada kolom dalam tabel. Ini karena jumlah memori yang diperlukan untuk rencana eksekusi meningkat.

Untuk menentukan kumpulan kolom, gunakan *<column_set_name>* FOR ALL_SPARSE_COLUMNS kata kunci dalam pernyataan CREATE TABLE atau ALTER TABLE .

Panduan untuk menggunakan kumpulan kolom

Saat Anda menggunakan kumpulan kolom, pertimbangkan panduan berikut:

  • Kolom jarang dan kumpulan kolom dapat ditambahkan sebagai bagian dari pernyataan yang sama.

  • Kumpulan kolom tidak dapat ditambahkan ke tabel jika tabel tersebut sudah berisi kolom jarang.

  • Kolom kumpulan kolom tidak dapat diubah atau diganti namanya. Untuk mengubah kumpulan kolom, Anda harus menghapus dan membuat ulang kolom sparse dan kumpulan kolom. Kolom dengan kata kunci SPARSE dapat ditambahkan dan dihilangkan dari tabel.

  • Kumpulan kolom dapat ditambahkan ke tabel yang tidak menyertakan kolom jarang apa pun. Jika kolom jarang kemudian ditambahkan ke tabel, kolom tersebut akan muncul di kumpulan kolom.

  • Hanya satu kumpulan kolom yang diizinkan per tabel.

  • Kumpulan kolom bersifat opsional dan tidak diperlukan untuk menggunakan kolom jarang.

  • Batasan atau nilai default tidak dapat ditentukan pada kumpulan kolom.

  • Kolom komputasi tidak boleh berisi kolom kumpulan kolom.

  • Kueri terdistribusi tidak didukung pada tabel yang berisi kumpulan kolom.

  • Replikasi tidak mendukung kumpulan kolom.

  • Mengubah tangkapan data tidak mendukung kumpulan kolom.

  • Kumpulan kolom tidak dapat menjadi bagian dari jenis indeks apa pun. Ini termasuk indeks XML, indeks teks lengkap, dan tampilan terindeks. Kumpulan kolom tidak dapat ditambahkan sebagai kolom yang disertakan dalam indeks apa pun.

  • Kumpulan kolom tidak dapat digunakan dalam ekspresi filter indeks yang difilter atau statistik yang difilter.

  • Saat tampilan menyertakan kumpulan kolom, kumpulan kolom muncul dalam tampilan sebagai kolom XML.

  • Kumpulan kolom tidak dapat disertakan dalam definisi tampilan terindeks.

  • Pandangan terpartisi yang menyertakan tabel yang berisi kumpulan kolom dapat diperbarui ketika pandangan terpartisi menentukan kolom jarang berdasarkan nama. Tampilan yang dipartisi tidak dapat diperbarui saat mereferensikan kumpulan kolom.

  • Pemberitahuan kueri yang merujuk ke kumpulan kolom tidak diizinkan.

  • Data XML memiliki batas ukuran 2 GB. Jika data gabungan dari semua kolom sparse non-NULL dalam satu baris melebihi batas ini, kueri atau operasi DML akan menghasilkan kesalahan.

  • Untuk informasi tentang data yang dikembalikan oleh fungsi COLUMNS_UPDATED, lihat Menggunakan Kolom Jarang.

Panduan untuk memilih data dari kumpulan kolom

Pertimbangkan panduan berikut untuk memilih data dari kumpulan kolom:

  • Secara konseptual, kumpulan kolom adalah jenis kolom XML komputasi yang dapat diperbarui yang menggabungkan sekumpulan kolom relasional yang mendasar ke dalam satu representasi XML. Kumpulan kolom hanya mendukung properti ALL_SPARSE_COLUMNS. Properti ini digunakan untuk menggabungkan semua nilai non-NULL dari semua kolom jarang pada baris tertentu.

  • Di editor tabel SQL Server Management Studio, kumpulan kolom ditampilkan sebagai bidang XML yang dapat diedit. Tentukan kumpulan kolom dalam format:

    <column_name_1>value1</column_name_1><column_name_2>value2</column_name_2>...

    Contoh nilai kumpulan kolom adalah sebagai berikut:

    <sparseProp1>10</sparseProp1><sparseProp3>20</sparseProp3>  

    <DocTitle>Bicycle Parts List</DocTitle><Region>West</Region>

  • Kolom jarang yang berisi nilai null dihilangkan dari representasi XML untuk kumpulan kolom.

Warning

Menambahkan kumpulan kolom mengubah perilaku SELECT * kueri. Kueri akan mengembalikan kumpulan kolom sebagai kolom XML dan tidak mengembalikan kolom jarang individual. Perancang skema dan pengembang perangkat lunak harus berhati-hati untuk tidak merusak aplikasi yang ada. Kolom jarang individu masih dapat dikueri berdasarkan nama dalam pernyataan SELECT.

Menyisipkan atau mengubah data dalam kumpulan kolom

Manipulasi data pada kolom jarang dapat dilakukan dengan menggunakan nama kolom individual, atau dengan mengacu pada nama kumpulan kolom dan menentukan nilai kumpulan kolom tersebut dengan menggunakan format XML dari kumpulan kolom. Kolom yang jarang digunakan dapat muncul dalam urutan apa saja di kolom XML.

Saat nilai kolom jarang disisipkan atau diperbarui dengan menggunakan kumpulan kolom XML, nilai yang disisipkan ke dalam kolom jarang dasar dikonversi secara implisit dari tipe data xml. Dalam kasus sebagian besar jenis data numerik, termasuk bigint, int, smallint, tinyint, bit, float, dan real, nilai kosong dalam XML untuk kolom dikonversi ke string kosong. Ini menyebabkan nol disisipkan ke dalam kolom, seperti yang diperlihatkan dalam contoh berikut. Namun, substitusi ke 0 tidak berlaku untuk jenis data numerik dan desimal , nilai-nilai ini harus ditentukan atau akan menyebabkan kesalahan konversi.

CREATE TABLE t (i int SPARSE, cs xml column_set FOR ALL_SPARSE_COLUMNS);  
GO  
INSERT t(cs) VALUES ('<i/>');  
GO  
SELECT i FROM t;  
GO

Dalam contoh ini, tidak ada nilai yang ditentukan untuk kolom i, tetapi nilai 0 disisipkan.

Gunakan tipe data sql_variant

Jenis data sql_variant dapat menyimpan beberapa tipe data yang berbeda, seperti int, char, dan date. Kumpulan kolom menghasilkan informasi jenis data seperti informasi skala, presisi, dan lokal yang terkait dengan nilai sql_variant sebagai atribut di kolom XML yang dihasilkan. Jika Anda mencoba memberikan atribut ini dalam pernyataan XML yang dibuat khusus sebagai input untuk operasi sisipkan atau perbarui pada kumpulan kolom, beberapa atribut ini diperlukan dan beberapa di antaranya diberi nilai default. Tabel berikut mencantumkan jenis data dan nilai default yang dihasilkan server saat nilai tidak disediakan.

Jenis data localeID* sqlCompareOptions sqlCollationVersion SqlSortId Panjang maksimum Precision Scale
char, varchar, biner -1 'Default' 0 0 8000 Tidak berlaku** Tidak berlaku
nvarchar -1 'Default' 0 0 4000 Tidak berlaku Tidak berlaku
desimal, mengambang, nyata Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku 18 0
bilangan bulat, bilangan bulat besar, bilangan bulat sangat kecil, bilangan bulat kecil Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku
datetime2 Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku 7
offset tanggalwaktu Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku 7
datetime, date, smalldatetime Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku
uang, uang kecil Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku
time Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku Tidak berlaku 7

* localeID -1 berarti lokasi bawaan. Lokal Inggris adalah 1033.

** Tidak berlaku = Tidak ada nilai yang dihasilkan untuk atribut ini selama operasi pilih pada kumpulan kolom. Menghasilkan kesalahan ketika nilai ditentukan untuk atribut ini oleh pemanggil melalui representasi XML yang disediakan untuk satu set kolom dalam operasi sisipkan atau perbarui.

Keamanan

Model keamanan untuk kumpulan kolom berfungsi mirip dengan model keamanan yang ada antara tabel dan kolom. Kumpulan kolom dapat divisualisasikan sebagai tabel mini dan operasi pilih seperti SELECT * operasi pada tabel mini ini. Tapi, hubungan antara kolom yang diatur ke kolom jarang adalah hubungan pengelompokan alih-alih kontainer secara ketat. Model keamanan memeriksa keamanan pada kumpulan kolom, dan menghormati operasi DENY pada kolom jarang dasar. Karakteristik tambahan dari model keamanan adalah sebagai berikut:

  • Izin keamanan dapat diberikan dan dicabut dari kolom kumpulan kolom, mirip dengan kolom lain dalam tabel.

  • IZIN GRANT atau REVOKE untuk SELECT, INSERT, UPDATE, DELETE, dan REFERENCES pada satu set kolom tidak disebarluaskan ke kolom anggota yang mendasari set tersebut. Ini hanya berlaku untuk penggunaan kolom kumpulan kolom. Izin TOLAK pada kumpulan kolom merambat ke kolom jarang yang mendasar dari tabel.

  • Menjalankan pernyataan SELECT, INSERT, UPDATE, dan DELETE pada kolom set kolom mengharuskan pengguna memiliki izin yang sesuai pada kolom set tersebut, dan juga izin yang sesuai pada semua kolom sebaran dalam tabel. Karena kumpulan kolom mewakili semua kolom jarang dalam tabel, Anda harus memiliki izin pada semua kolom jarang, dan ini termasuk kolom jarang yang mungkin tidak Anda ubah.

  • Menjalankan pernyataan REVOKE pada kolom jarang atau kumpulan kolom mengembalikan pengaturan keamanan ke objek induknya.

Examples

Dalam contoh berikut, tabel dokumen berisi kumpulan kolom DocID umum dan Title. Grup Produksi menginginkan kolom ProductionSpecification dan ProductionLocation untuk semua dokumen produksi. Grup Pemasaran menginginkan sebuah MarketingSurveyGroup kolom untuk dokumen pemasaran.

A. Membuat tabel yang memiliki kumpulan kolom

Contoh berikut membuat tabel yang menggunakan kolom jarang data dan menyertakan kumpulan kolom SpecialPurposeColumns. Contoh menyisipkan dua baris ke dalam tabel, lalu memilih data dari tabel.

Note

Tabel ini hanya memiliki lima kolom untuk mempermudah tampilan dan pembacaan.

USE AdventureWorks2022;  
GO  
  
CREATE TABLE DocumentStoreWithColumnSet  
    (DocID int PRIMARY KEY,  
     Title varchar(200) NOT NULL,  
     ProductionSpecification varchar(20) SPARSE NULL,  
     ProductionLocation smallint SPARSE NULL,  
     MarketingSurveyGroup varchar(20) SPARSE NULL,  
     MarketingProgramID int SPARSE NULL,  
     SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);  
GO

B. Masukkan data ke dalam tabel menggunakan nama-nama kolom jarang

Contoh-contoh berikut menyisipkan dua baris ke dalam tabel yang dibuat di Contoh A. Contoh-contoh tersebut menggunakan nama kolom jarang dan tidak mengacu pada kumpulan kolom.

INSERT DocumentStoreWithColumnSet (DocID, Title, ProductionSpecification, ProductionLocation)  
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);  
GO  
  
INSERT DocumentStoreWithColumnSet (DocID, Title, MarketingSurveyGroup)  
VALUES (2, 'Survey 2142', 'Men 25 - 35');  
GO

C. Menyisipkan data ke tabel dengan menggunakan nama kumpulan kolom

Contoh berikut menyisipkan baris ketiga ke dalam tabel yang dibuat dalam contoh A. Kali ini nama kolom jarang tidak digunakan. Sebagai gantinya, nama kumpulan kolom digunakan, dan sisipan menyediakan nilai untuk dua dari empat kolom jarang dalam format XML.

INSERT DocumentStoreWithColumnSet (DocID, Title, SpecialPurposeColumns)  
VALUES (3, 'Tire Spec 2', '<ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>');  
GO

D. Amati hasil kumpulan kolom saat SELECT * digunakan

Contoh berikut memilih semua kolom dari tabel yang berisi kumpulan kolom. Ini menghasilkan kolom XML dengan nilai gabungan dari kolom jarang. Ini tidak mengembalikan kolom jarang secara individual.

SELECT DocID, Title, SpecialPurposeColumns FROM DocumentStoreWithColumnSet ;

Berikut set hasilnya.

DocID  Title        SpecialPurposeColumns  
1      Tire Spec 1  <ProductionSpecification>AXZZ217</ProductionSpecification><ProductionLocation>27</ProductionLocation>  
2      Survey 2142  <MarketingSurveyGroup>Men 25 - 35</MarketingSurveyGroup>  
3      Tire Spec 2  <ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>

E. Amati hasil pemilihan kolom yang ditetapkan menurut nama

Karena departemen Produksi tidak tertarik dengan data pemasaran, contoh ini menambahkan klausul WHERE untuk membatasi output. Contoh menggunakan nama kumpulan kolom.

SELECT DocID, Title, SpecialPurposeColumns  
FROM DocumentStoreWithColumnSet  
WHERE ProductionSpecification IS NOT NULL ;

Berikut set hasilnya.

DocID  Title        SpecialPurposeColumns  
1      Tire Spec 1  <ProductionSpecification>AXZZ217</ProductionSpecification><ProductionLocation>27</ProductionLocation>  
3      Tire Spec 2  <ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>

F. Amati hasil pemilihan kolom jarang menurut nama

Saat tabel berisi kumpulan kolom, Anda masih bisa mengkueri tabel dengan menggunakan nama kolom individual seperti yang diperlihatkan dalam contoh berikut.

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

Berikut set hasilnya.

DocID  Title        ProductionSpecification ProductionLocation`  
1      Tire Spec 1  AXZZ217                 27`  
3      Tire Spec 2  AXW9R411                38`

G. Memperbarui tabel dengan menggunakan kumpulan kolom

Contoh berikut memperbarui rekaman ketiga dengan nilai baru untuk kedua kolom jarang yang digunakan oleh baris tersebut.

UPDATE DocumentStoreWithColumnSet  
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification><ProductionLocation>38</ProductionLocation>'  
WHERE DocID = 3 ;  
GO

Important

Pernyataan UPDATE yang menggunakan kumpulan kolom memperbarui semua kolom jarang dalam tabel. Kolom jarang yang tidak dirujuk diperbarui menjadi NULL.

Contoh berikut memperbarui rekaman ketiga, tetapi hanya menentukan nilai salah satu dari dua kolom yang diisi. Kolom ProductionLocation kedua tidak disertakan dalam UPDATE pernyataan dan diperbarui ke NULL.

UPDATE DocumentStoreWithColumnSet  
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification>'  
WHERE DocID = 3 ;  
GO

Langkah selanjutnya

  • Last updated on