sp_tableoption (T-SQL)

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Mengatur nilai opsi untuk tabel yang ditentukan pengguna. sp_tableoptiondapat digunakan untuk mengontrol perilaku tabel dalam baris dengan varchar(maks), nvarchar(max), varbinary(max), xml, teks, ntext, gambar, atau kolom jenis besar yang ditentukan pengguna.

Penting

Fitur teks dalam baris akan dihapus di versi SQL Server yang akan datang. Untuk menyimpan data bernilai besar, kami sarankan Anda menggunakan jenis data varchar(max), nvarchar(max) dan varbinary(max ).

Konvensi sintaks transact-SQL

Sintaks

sp_tableoption
    [ @TableNamePattern = ] N'TableNamePattern'
    , [ @OptionName = ] 'OptionName'
    , [ @OptionValue = ] 'OptionValue'
[ ; ]

Argumen

[ @TableNamePattern = ] N'TableNamePattern'

Nama tabel database yang memenuhi syarat atau tidak memenuhi syarat. @TableNamePattern adalah nvarchar(776), tanpa default. Jika nama tabel yang sepenuhnya memenuhi syarat, termasuk nama database, disediakan, nama database harus menjadi nama database saat ini. Opsi tabel untuk beberapa tabel tidak dapat diatur secara bersamaan.

[ @OptionName = ] 'OptionName'

Nama opsi tabel. @OptionName adalah varchar(35), dan bisa menjadi salah satu nilai berikut.

Nilai Deskripsi
table lock on bulk load Saat dinonaktifkan (default), ini menyebabkan proses pemuatan massal pada tabel yang ditentukan pengguna untuk mendapatkan kunci baris. Saat diaktifkan, ini menyebabkan proses pemuatan massal pada tabel yang ditentukan pengguna untuk mendapatkan kunci pembaruan massal.
insert row lock Tidak lagi didukung.

Opsi ini tidak berpengaruh pada perilaku penguncian SQL Server dan hanya disertakan untuk kompatibilitas skrip dan prosedur yang ada.
text in row Ketika OFF atau 0 (dinonaktifkan, default), itu tidak mengubah perilaku saat ini, dan tidak ada BLOB berturut-turut.

Ketika ditentukan dan @OptionValue (ONdiaktifkan) atau nilai bilangan bulat dari 24 melalui 7000, string teks baru, ntext, atau gambar disimpan langsung di baris data. Semua data BLOB (objek besar biner: teks, ntext, atau gambar) yang ada diubah menjadi teks dalam format baris saat nilai BLOB diperbarui. Untuk informasi selengkapnya, lihat Keterangan.
large value types out of row 1 = varchar(max), nvarchar(max), varbinary(max), xml, dan kolom tipe besar yang ditentukan pengguna (UDT) dalam tabel disimpan di luar baris, dengan pointer 16-byte ke akar.

0 = varchar(max), nvarchar(max), varbinary(max), xml, dan nilai UDT besar disimpan langsung di baris data, hingga batas 8.000 byte dan selama nilai dapat pas dalam rekaman. Jika nilai tidak pas dalam rekaman, penunjuk disimpan secara berurutan dan sisanya disimpan di luar baris di ruang penyimpanan LOB. 0 adalah nilai default.

Jenis besar yang ditentukan pengguna (UDT) berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.

TEXTIMAGE_ON Gunakan opsi CREATE TABLE untuk menentukan lokasi penyimpanan jenis data besar.
format penyimpanan vardecimal Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru.

Ketika TRUE, , ONatau 1, tabel yang ditunjuk diaktifkan untuk format penyimpanan vardecimal . Ketika FALSE, , OFFatau 0, tabel tidak diaktifkan untuk format penyimpanan vardecimal . format penyimpanan vardecimal hanya dapat diaktifkan ketika database diaktifkan untuk format penyimpanan vardecimal dengan menggunakan sp_db_vardecimal_storage_format. Dalam SQL Server 2008 (10.0.x) dan yang lebih baru, format penyimpanan vardecimal tidak digunakan lagi. Gunakan ROW pemadatan sebagai gantinya. Untuk informasi selengkapnya, lihat Pemadatan data. 0 adalah nilai default.

[ @OptionValue = ] 'OptionValue'

Menentukan apakah @OptionName diaktifkan (TRUE, , ONatau 1) atau dinonaktifkan (FALSE, OFF, atau 0 ). @OptionValue adalah varchar(12), tanpa default. @OptionValue tidak peka huruf besar/kecil.

Untuk opsi teks dalam baris, nilai opsi yang valid adalah 0, , ON, OFFatau bilangan bulat dari 24 melalui 7000. Saat @OptionValue adalah ON, batasnya default menjadi 256 byte.

Mengembalikan nilai kode

0 (berhasil) atau nomor kesalahan (kegagalan).

Keterangan

sp_tableoption hanya dapat digunakan untuk mengatur nilai opsi untuk tabel yang ditentukan pengguna. Untuk menampilkan properti tabel, gunakan OBJECTPROPERTY atau kueri sys.tables.

Opsi teks dalam baris dapat diaktifkan atau dinonaktifkan sp_tableoption hanya pada tabel yang berisi kolom teks. Jika tabel tidak memiliki kolom teks, SQL Server akan menimbulkan kesalahan.

Saat opsi teks dalam baris diaktifkan, parameter @OptionValue memungkinkan pengguna menentukan ukuran maksimum yang akan disimpan dalam baris untuk BLOB. Defaultnya adalah 256 byte, tetapi nilai dapat berkisar dari 24 hingga 7000 byte.

string teks, ntext, atau gambar disimpan di baris data jika kondisi berikut berlaku:

  • Teks dalam baris diaktifkan.
  • Panjang string lebih pendek dari batas yang ditentukan dalam @OptionValue.
  • Ada cukup ruang yang tersedia di baris data.

Saat string BLOB disimpan di baris data, membaca dan menulis string teks, ntext, atau gambar dapat secepat membaca atau menulis karakter dan string biner. SQL Server tidak perlu mengakses halaman terpisah untuk membaca atau menulis string BLOB.

Jika string teks, ntext, atau gambar lebih besar dari batas yang ditentukan atau ruang yang tersedia di baris, pointer disimpan di baris sebagai gantinya. Kondisi untuk menyimpan string BLOB dalam baris tetap berlaku: Harus ada cukup ruang di baris data untuk menahan penunjuk.

String blob dan pointer yang disimpan dalam baris tabel diperlakukan mirip dengan string panjang variabel. SQL Server hanya menggunakan jumlah byte yang diperlukan untuk menyimpan string atau pointer.

String BLOB yang ada tidak segera dikonversi saat teks dalam baris pertama kali diaktifkan. String hanya dikonversi saat diperbarui. Demikian juga, ketika batas opsi teks dalam baris ditingkatkan, string teks, ntext, atau gambar yang sudah ada di baris data tidak dikonversi untuk mematuhi batas baru hingga waktu diperbarui.

Catatan

Menonaktifkan teks dalam opsi baris atau mengurangi batas opsi akan memerlukan konversi semua BLOB; oleh karena itu, prosesnya bisa panjang, tergantung pada jumlah string BLOB yang harus dikonversi. Tabel dikunci selama proses konversi.

Variabel tabel, termasuk fungsi yang mengembalikan variabel tabel, secara otomatis mengaktifkan opsi teks dalam baris dengan batas sebaris default 256. Opsi ini tidak dapat diubah.

Opsi teks dalam baris mendukung fungsi TEXTPTR, WRITETEXT, UPDATETEXT, dan READTEXT. Pengguna dapat membaca bagian BLOB dengan fungsi SUBSTRING(), tetapi harus ingat bahwa penunjuk teks dalam baris memiliki batas durasi dan angka yang berbeda dari penunjuk teks lainnya.

Untuk mengubah tabel dari format penyimpanan vardecimal kembali ke format penyimpanan desimal normal, database harus berada dalam model pemulihan SIMPLE. Mengubah mode pemulihan akan memutus rantai log untuk tujuan pencadangan, oleh karena itu Anda harus membuat cadangan database lengkap setelah menghapus format penyimpanan vardecimal dari tabel.

Jika Anda mengonversi kolom tipe data LOB yang ada (teks, ntext, atau gambar) ke jenis nilai besar kecil ke menengah (varchar(maks), nvarchar(maks), atau varbinary(maks)), dan sebagian besar pernyataan tidak mereferensikan kolom jenis nilai besar di lingkungan Anda, pertimbangkan untuk mengubah large_value_types_out_of_row untuk 1 mendapatkan performa optimal. Saat nilai opsi large_value_types_out_of_row diubah, nilai varchar(maks) yang ada, nvarchar(max), varbinary(max), dan xml tidak segera dikonversi. Penyimpanan string diubah saat diperbarui nanti. Setiap nilai baru yang disisipkan ke dalam tabel disimpan sesuai dengan opsi tabel yang berlaku. Untuk hasil langsung, buat salinan data lalu repopulasi tabel setelah mengubah pengaturan large_value_types_out_of_row atau memperbarui setiap kolom jenis nilai besar kecil ke menengah ke dirinya sendiri sehingga penyimpanan string diubah dengan opsi tabel berlaku. Pertimbangkan untuk membangun kembali indeks pada tabel setelah pembaruan atau repopulasi untuk mengembun tabel.

Izin

Untuk menjalankan sp_tableoption memerlukan ALTER izin pada tabel.

Contoh

J. Menyimpan data XML dari baris

Contoh berikut menentukan bahwa data xml dalam HumanResources.JobCandidate tabel disimpan di luar baris.

USE AdventureWorks2022;
GO
EXEC sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;

B. Mengaktifkan format penyimpanan vardecimal pada tabel

Contoh berikut memodifikasi Production.WorkOrderRouting tabel untuk menyimpan jenis data desimal dalam format penyimpanan vardecimal .

USE master;
GO
-- The database must be enabled for vardecimal storage format
-- before a table can be enabled for vardecimal storage format
EXEC sp_db_vardecimal_storage_format 'AdventureWorks2022', 'ON';
GO
USE AdventureWorks2022;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting',
   'vardecimal storage format', 'ON';

Konten terkait