Mendaftarkan Jenis User-Defined di SQL Server
Berlaku untuk:
SQL Server
Untuk menggunakan jenis yang ditentukan pengguna (UDT) di Microsoft SQL Server, Anda harus mendaftarkannya. Mendaftarkan UDT melibatkan pendaftaran assembly dan membuat jenis dalam database tempat Anda ingin menggunakannya. UDT dicakup ke satu database, dan tidak dapat digunakan dalam beberapa database kecuali rakitan dan UDT yang identik terdaftar di setiap database. Setelah rakitan UDT terdaftar dan jenisnya dibuat, Anda dapat menggunakan UDT di Transact-SQL dan dalam kode klien. Untuk informasi selengkapnya, lihat Jenis User-Defined CLR.
Menggunakan Visual Studio untuk Menyebarkan UDT
Cara termudah untuk menyebarkan UDT Anda adalah dengan menggunakan Microsoft Visual Studio. Namun, untuk skenario penyebaran yang lebih kompleks dan fleksibilitas terbesar, gunakan Transact-SQL seperti yang dibahas nanti dalam topik ini.
Ikuti langkah-langkah berikut untuk membuat dan menyebarkan UDT menggunakan Visual Studio:
Buat proyek Database baru di simpul bahasa Visual Basic atau Visual C# .
Tambahkan referensi ke database SQL Server yang akan berisi UDT.
Tambahkan kelas Jenis yang Ditentukan Pengguna .
Tulis kode untuk mengimplementasikan UDT.
Dari menu Build , pilih Sebarkan. Ini mendaftarkan assembly dan membuat jenis dalam database SQL Server.
Menggunakan Transact-SQL untuk Menyebarkan UDT
Sintaks RAKITAN CREATE Transact-SQL digunakan untuk mendaftarkan assembly dalam database tempat Anda ingin menggunakan UDT. Ini disimpan secara internal dalam tabel sistem database, bukan secara eksternal dalam sistem file. Jika UDT bergantung pada rakitan eksternal, UDT juga harus dimuat ke dalam database. Pernyataan CREATE TYPE digunakan untuk membuat UDT dalam database tempat pernyataan tersebut akan digunakan. Untuk informasi selengkapnya, lihat CREATE ASSEMBLY (Transact-SQL) dan CREATE TYPE (Transact-SQL).
Menggunakan CREATE ASSEMBLY
Sintaks CREATE ASSEMBLY mendaftarkan assembly dalam database tempat Anda ingin menggunakan UDT. Setelah rakitan terdaftar, rakitan tidak memiliki dependensi.
Membuat beberapa versi rakitan yang sama dalam database tertentu tidak diperbolehkan. Namun, dimungkinkan untuk membuat beberapa versi rakitan yang sama berdasarkan budaya dalam database tertentu. SQL Server membedakan beberapa versi budaya perakitan dengan nama yang berbeda seperti yang terdaftar dalam instans SQL Server. Untuk informasi selengkapnya, lihat "Membuat dan Menggunakan Rakitan Strong-Named" di SDK .NET Framework.
Ketika CREATE ASSEMBLY dijalankan dengan kumpulan izin AMAN atau EXTERNAL_ACCESS, rakitan diperiksa untuk memastikan bahwa itu dapat diverifikasi dan jenisnya aman. Jika Anda menghilangkan menentukan set izin, MAKA AMAN akan diasumsikan. Kode dengan set izin TIDAK AMAN tidak dicentang. Untuk informasi selengkapnya tentang kumpulan izin perakitan, lihat Merancang Rakitan.
Contoh
Pernyataan Transact-SQL berikut mendaftarkan rakitan Point di SQL Server dalam database AdventureWorks, dengan izin AMAN yang ditetapkan. Jika klausa WITH PERMISSION_SET dihilangkan, rakitan terdaftar dengan kumpulan izin AMAN.
USE AdventureWorks;
CREATE ASSEMBLY Point
FROM '\\ShareName\Projects\Point\bin\Point.dll'
WITH PERMISSION_SET = SAFE;
Pernyataan Transact-SQL berikut mendaftarkan perakitan menggunakan <argumen assembly_bits> dalam klausa FROM. Nilai varbinary ini mewakili file sebagai aliran byte.
USE AdventureWorks;
CREATE ASSEMBLY Point
FROM 0xfeac4 ... 21ac78
Menggunakan CREATE TYPE
Setelah rakitan dimuat ke dalam database, Anda kemudian dapat membuat jenis menggunakan pernyataan CREATE TYPE Transact-SQL. Ini menambahkan jenis ke daftar jenis yang tersedia untuk database tersebut. Jenis memiliki cakupan database dan jenis hanya dapat digunakan dalam database tempatnya dibuat. Jika UDT sudah ada dalam database, pernyataan CREATE TYPE gagal dengan kesalahan.
Catatan
Sintaks CREATE TYPE juga digunakan untuk membuat jenis data alias SQL Server asli, dan dimaksudkan untuk menggantikan sp_addtype sebagai sarana pembuatan jenis data alias. Beberapa argumen opsional dalam sintaks CREATE TYPE mengacu pada pembuatan UDT, dan tidak berlaku untuk membuat jenis data alias (seperti jenis dasar).
Untuk informasi selengkapnya, lihat CREATE TYPE (Transact-SQL).
Contoh
Pernyataan Transact-SQL berikut membuat jenis Titik . NAMA EKSTERNAL ditentukan menggunakan sintaks penamaan dua bagian AssemblyName. UDTName.
CREATE TYPE dbo.Point
EXTERNAL NAME Point.[Point];
Menghapus UDT dari Database
Pernyataan DROP TYPE menghapus UDT dari database saat ini. Setelah UDT dihilangkan, Anda dapat menggunakan pernyataan DROP ASSEMBLY untuk menghilangkan rakitan dari database.
Pernyataan DROP TYPE tidak dijalankan dalam situasi berikut:
Tabel dalam database yang berisi kolom yang ditentukan menggunakan UDT.
Fungsi, prosedur tersimpan, atau pemicu yang menggunakan variabel atau parameter UDT, dibuat dalam database dengan klausa WITH SCHEMABINDING.
Contoh
Transact-SQL berikut harus dijalankan dalam urutan berikut. Pertama tabel yang mereferensikan Point UDT harus dihilangkan, lalu jenisnya, dan akhirnya assembly.
DROP TABLE dbo.Points;
DROP TYPE dbo.Point;
DROP ASSEMBLY Point;
Menemukan Dependensi UDT
Jika ada objek dependen, seperti tabel dengan definisi kolom UDT, pernyataan DROP TYPE gagal. Ini juga gagal jika ada fungsi, prosedur tersimpan, atau pemicu yang dibuat dalam database menggunakan klausa WITH SCHEMABINDING, jika rutinitas ini menggunakan variabel atau parameter dari jenis yang ditentukan pengguna. Anda harus terlebih dahulu menghilangkan semua objek dependen, lalu menjalankan pernyataan DROP TYPE.
Kueri Transact-SQL berikut menemukan semua kolom dan parameter yang menggunakan UDT dalam database AdventureWorks .
USE Adventureworks;
SELECT o.name AS major_name, o.type_desc AS major_type_desc
, c.name AS minor_name, c.type_desc AS minor_type_desc
, at.assembly_class
FROM (
SELECT object_id, name, user_type_id, 'SQL_COLUMN' AS type_desc
FROM sys.columns
UNION ALL
SELECT object_id, name, user_type_id, 'SQL_PROCEDURE_PARAMETER'
FROM sys.parameters
) AS c
JOIN sys.objects AS o
ON o.object_id = c.object_id
JOIN sys.assembly_types AS at
ON at.user_type_id = c.user_type_id;
Mempertahankan UDT
Anda tidak dapat mengubah UDT setelah dibuat dalam database SQL Server, meskipun Anda dapat mengubah assembly yang menjadi dasar jenisnya. Dalam kebanyakan kasus, Anda harus menghapus UDT dari database dengan pernyataan TRANSACT-SQL DROP TYPE, membuat perubahan pada rakitan yang mendasar, dan memuat ulang menggunakan pernyataan ALTER ASSEMBLY. Anda kemudian perlu membuat ulang UDT dan objek dependen apa pun.
Contoh
Pernyataan ALTER ASSEMBLY digunakan setelah Anda membuat perubahan pada kode sumber di rakitan UDT Anda dan mengkompilasinya kembali. Ini menyalin file .dll ke server dan mengikat kembali ke assembly baru. Untuk sintaks lengkapnya, lihat MENGUBAH ASSEMBLY (Transact-SQL).
Pernyataan Transact-SQL ALTER ASSEMBLY berikut memuat ulang rakitan Point.dll dari lokasi yang ditentukan pada disk.
ALTER ASSEMBLY Point
FROM '\\Projects\Point\bin\Point.dll'
Menggunakan ALTER ASSEMBLY untuk Menambahkan Kode Sumber
Klausa ADD FILE dalam sintaks ALTER ASSEMBLY tidak ada dalam CREATE ASSEMBLY. Anda dapat menggunakannya untuk menambahkan kode sumber atau file lain yang terkait dengan assembly. File disalin dari lokasi aslinya dan disimpan dalam tabel sistem dalam database. Ini memastikan bahwa Anda selalu memiliki kode sumber atau file lain jika Anda perlu membuat ulang atau mendokumentasikan versi UDT saat ini.
Pernyataan Transact-SQL ALTER ASSEMBLY berikut menambahkan kode sumber kelas Point.cs untuk Point UDT. Ini menyalin teks yang terkandung dalam file Point.cs dan menyimpannya di database dengan nama "PointSource".
ALTER ASSEMBLY Point
ADD FILE FROM '\\Projects\Point\Point.cs' AS PointSource;
Informasi rakitan disimpan dalam tabel sys.assembly_files dalam database tempat assembly telah diinstal. Tabel sys.assembly_files berisi kolom berikut ini.
assembly_id
Pengidentifikasi yang ditentukan untuk rakitan. Angka ini ditetapkan ke semua objek yang berkaitan dengan rakitan yang sama.
nama
Nama objek.
file_id
Angka yang mengidentifikasi setiap objek, dengan objek pertama yang terkait dengan assembly_id tertentu diberikan nilai 1. Jika ada beberapa objek yang terkait dengan assembly_id yang sama, maka setiap nilai file_id berikutnya bertambah 1.
konten
Representasi heksadesimal dari assembly atau file.
Anda dapat menggunakan fungsi CAST atau CONVERT untuk mengonversi konten kolom konten menjadi teks yang dapat dibaca. Kueri berikut mengonversi konten file Point.cs menjadi teks yang dapat dibaca, menggunakan nama dalam klausa WHERE untuk membatasi tataan hasil ke satu baris.
SELECT CAST(content AS varchar(8000))
FROM sys.assembly_files
WHERE name='PointSource';
Jika Anda menyalin dan menempelkan hasilnya ke editor teks, Anda akan melihat bahwa hentian baris dan spasi yang ada di aslinya telah dipertahankan.
Mengelola UDT dan Rakitan
Saat merencanakan implementasi UDT Anda, pertimbangkan metode mana yang diperlukan dalam rakitan UDT itu sendiri, dan metode mana yang harus dibuat dalam rakitan terpisah dan diimplementasikan sebagai fungsi yang ditentukan pengguna atau prosedur tersimpan. Memisahkan metode menjadi rakitan terpisah memungkinkan Anda memperbarui kode tanpa memengaruhi data yang dapat disimpan dalam kolom UDT dalam tabel. Anda dapat memodifikasi rakitan UDT tanpa menghilangkan kolom UDT dan objek dependen lainnya hanya ketika definisi baru dapat membaca nilai sebelumnya dan tanda tangan jenis tidak berubah.
Memisahkan kode prosedural yang dapat berubah dari kode yang diperlukan untuk mengimplementasikan UDT sangat menyederhanakan pemeliharaan. Termasuk hanya kode yang diperlukan agar UDT berfungsi, dan menjaga definisi UDT Anda sesederhana mungkin, mengurangi risiko bahwa UDT itu sendiri mungkin perlu dihilangkan dari database untuk revisi kode atau perbaikan bug.
Fungsi UDT Mata Uang dan Konversi Mata Uang
UDT Mata Uang dalam database sampel AdventureWorks memberikan contoh yang berguna dari cara yang direkomendasikan untuk menyusun UDT dan fungsi terkait. UDT Mata Uang digunakan untuk menangani uang berdasarkan sistem moneter budaya tertentu, dan memungkinkan penyimpanan berbagai jenis mata uang, seperti dolar, euro, dan sebagainya. Kelas UDT mengekspos nama budaya sebagai string, dan jumlah uang sebagai jenis data desimal . Semua metode serialisasi yang diperlukan terkandung dalam perakitan yang mendefinisikan kelas . Fungsi yang mengimplementasikan konversi mata uang dari satu budaya ke budaya lain diimplementasikan sebagai fungsi eksternal bernama ConvertCurrency, dan fungsi ini terletak di perakitan terpisah. Fungsi ConvertCurrency melakukan pekerjaannya dengan mengambil tingkat konversi dari tabel dalam database AdventureWorks . Jika sumber tingkat konversi harus pernah berubah, atau jika harus ada perubahan lain pada kode yang ada, perakitan dapat dengan mudah dimodifikasi tanpa memengaruhi UDT Mata Uang .
Daftar kode untuk fungsi UDT Mata Uang dan ConvertCurrency dapat ditemukan dengan menginstal sampel runtime bahasa umum (CLR).
Menggunakan UDT Di Seluruh Database
UDT berdasarkan definisi yang dilingkup ke satu database. Oleh karena itu, UDT yang ditentukan dalam satu database tidak dapat digunakan dalam definisi kolom di database lain. Untuk menggunakan UDT dalam beberapa database, Anda harus menjalankan pernyataan CREATE ASSEMBLY dan CREATE TYPE di setiap database pada rakitan yang identik. Rakitan dianggap identik jika mereka memiliki nama yang sama, nama yang kuat, budaya, versi, kumpulan izin, dan konten biner.
Setelah UDT terdaftar dan dapat diakses di kedua database, Anda dapat mengonversi nilai UDT dari satu database untuk digunakan di database lain. UDT identik dapat digunakan di seluruh database dalam skenario berikut:
Memanggil prosedur tersimpan yang ditentukan dalam database yang berbeda.
Mengkueri tabel yang ditentukan dalam database yang berbeda.
Memilih data UDT dari satu kolom UDT tabel database dan menyisipkannya ke database kedua dengan kolom UDT yang identik.
Dalam situasi ini, konversi apa pun yang diperlukan oleh server terjadi secara otomatis. Anda tidak dapat melakukan konversi secara eksplisit menggunakan fungsi Transact-SQL CAST atau CONVERT.
Perhatikan bahwa Anda tidak perlu mengambil tindakan apa pun untuk menggunakan UDT saat SQL Server Mesin Database membuat tabel kerja dalam database sistem tempdb. Ini termasuk penanganan kursor, variabel tabel, dan fungsi bernilai tabel yang ditentukan pengguna yang mencakup UDT dan yang secara transparan menggunakan tempdb. Namun, jika Anda secara eksplisit membuat tabel sementara di tempdb yang menentukan kolom UDT, maka UDT harus didaftarkan dengan tempdb dengan cara yang sama seperti untuk database pengguna.
Lihat juga
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk