Menggunakan kumpulan kolom

Artikel
06/16/2023

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

Tabel yang menggunakan kolom jarang dapat menunjuk kumpulan kolom untuk mengembalikan semua kolom jarang dalam tabel. Kumpulan kolom adalah representasi XML yang tidak ditata yang menggabungkan semua kolom jarang tabel ke dalam output terstruktur. Kumpulan kolom seperti kolom terhitung di tempat kumpulan kolom tidak disimpan secara fisik dalam tabel. Kumpulan kolom berbeda dari kolom terhitung di bahwa kumpulan kolom dapat diperbarui secara langsung.

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 jarang 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.
Tampilan yang dipartisi yang menyertakan tabel yang berisi kumpulan kolom dapat diperbarui ketika tampilan yang dipartisi 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 jarang non-NULL dalam baris melebihi batas ini, kueri atau operasi DML akan menghasilkan kesalahan.
Untuk informasi tentang data yang dikembalikan oleh COLUMNS_UPDATED fungsi, 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 untuk 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.

Peringatan

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 kolom jarang dapat dilakukan dengan menggunakan nama kolom individual, atau dengan mereferensikan nama kumpulan kolom dan menentukan nilai kolom yang ditetapkan dengan menggunakan format XML dari kumpulan kolom. Kolom jarang dapat muncul dalam urutan apa pun di kolom XML.

Saat nilai kolom jarang disisipkan atau diperbarui dengan menggunakan kumpulan kolom XML, nilai yang disisipkan ke dalam kolom jarang yang mendasar dikonversi secara implisit dari jenis 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.

Menggunakan jenis data sql_variant

Jenis tanggal sql_variant dapat menyimpan beberapa jenis 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	Presisi	Sisik
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, besar, kecil, 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, smallmoney	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 lokal default. 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 dalam representasi XML yang disediakan untuk kolom yang diatur 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 kolom kumpulan kolom, dan menghormati operasi DENY pada kolom jarang yang mendasar. 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 SELECT, INSERT, UPDATE, DELETE, dan REFERENCES pada kolom set kolom tidak disebarluaskan ke kolom anggota yang mendasar dari 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 kumpulan kolom mengharuskan pengguna memiliki izin yang sesuai pada kolom kumpulan kolom, dan juga izin yang sesuai pada semua kolom jarang 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 atau kolom jarang mengatur default keamanan ke objek induknya.

Contoh

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

J. Membuat tabel yang memiliki kumpulan kolom

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

Catatan

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. Menyisipkan data ke tabel menggunakan nama kolom jarang

Contoh berikut menyisipkan dua baris ke dalam tabel yang dibuat di Contoh A. Contoh menggunakan nama kolom jarang dan tidak mereferensikan 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 mengembalikan kolom XML dengan nilai gabungan kolom jarang. Ini tidak mengembalikan kolom jarang satu per satu.

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

Penting

Pernyataan UPDATE yang menggunakan kumpulan kolom memperbarui semua kolom jarang dalam tabel. Kolom jarang yang tidak dirujuk diperbarui ke 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 berikutnya

Menggunakan Kolom Jarang

Bagikan melalui

Menggunakan kumpulan kolom

Panduan untuk menggunakan kumpulan kolom

Panduan untuk memilih data dari kumpulan kolom

Menyisipkan atau mengubah data dalam kumpulan kolom

Menggunakan jenis data sql_variant

Keamanan

Contoh

J. Membuat tabel yang memiliki kumpulan kolom

B. Menyisipkan data ke tabel menggunakan nama kolom jarang

C. Menyisipkan data ke tabel dengan menggunakan nama kumpulan kolom

D. Amati hasil kumpulan kolom saat SELECT * digunakan

E. Amati hasil pemilihan kolom yang ditetapkan menurut nama

F. Amati hasil pemilihan kolom jarang menurut nama

G. Memperbarui tabel dengan menggunakan kumpulan kolom

Langkah berikutnya

Saran dan Komentar

Sumber Daya Tambahan: