Mendaftarkan Jenis User-Defined di SQL Server

Berlaku untuk:yes SQL Server (semua versi yang didukung)

Untuk menggunakan jenis yang ditentukan pengguna (UDT) di Microsoft SQL Server, Anda harus mendaftarkannya. Mendaftarkan UDT melibatkan pendaftaran perakitan 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 jenis yang dibuat, Anda dapat menggunakan UDT di SQL Transaksi 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:

  1. Buat proyek Database baru di simpul bahasa Visual Basic atau Visual C#.

  2. Tambahkan referensi ke database SQL Server yang akan berisi UDT.

  3. Tambahkan kelas Jenis yang Ditentukan Pengguna .

  4. Tulis kode untuk mengimplementasikan UDT.

  5. Dari menu Build , pilih Sebarkan. Ini mendaftarkan assembly dan membuat jenis dalam database SQL Server.

Menggunakan SQL Transact untuk Menyebarkan UDT

Sintaks transact-SQL CREATE ASSEMBLY 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 MEMBUAT ASSEMBLY (Transact-SQL) dan CREATE TYPE (Transact-SQL).

Menggunakan CREATE ASSEMBLY

Sintaks CREATE ASSEMBLY mendaftarkan assembly dalam database tempat Anda ingin menggunakan UDT. Setelah assembly terdaftar, ia tidak memiliki dependensi.

Membuat beberapa versi rakitan yang sama dalam database tertentu tidak diperbolehkan. Namun, dimungkinkan untuk membuat beberapa versi perakitan 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 set izin AMAN atau EXTERNAL_ACCESS, assembly diperiksa untuk memastikan bahwa itu dapat diverifikasi dan mengetik aman. Jika Anda menghilangkan menentukan set izin, aman diasumsikan. Kode dengan set izin TIDAK AMAN tidak dicentang. Untuk informasi selengkapnya tentang kumpulan izin perakitan, lihat Mendesain Rakitan.

Contoh

Pernyataan SQL Transact 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 assembly dimuat ke dalam database, Anda kemudian dapat membuat jenis menggunakan pernyataan TRANSACT-SQL CREATE TYPE. 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 SQL Transact 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, yang 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 di 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 mengolahnya kembali. Ini menyalin file .dll ke server dan mengikat kembali ke assembly baru. Untuk sintaks lengkapnya, lihat ALTER 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 TAMBAHKAN FILE dalam sintaks ALTER ASSEMBLY tidak ada di 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.

assembly_id
Pengidentifikasi yang ditentukan untuk perakitan. 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 diberikan nilai 1. Jika ada beberapa objek yang terkait dengan assembly_id yang sama, maka setiap nilai file_id berikutnya akan 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 menjadi 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 Assembly

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 mungkin 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 Konversi Mata Uang dan UDT Mata Uang

UDT Mata Uang dalam database sampel AdventureWorks memberikan contoh yang berguna dari cara yang direkomendasikan untuk menyusun UDT dan fungsi terkait. Mata Uang UDT 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 menentukan kelas . Fungsi yang mengimplementasikan konversi mata uang dari satu budaya ke budaya lainnya diimplementasikan sebagai fungsi eksternal bernama ConvertCurrency, dan fungsi ini terletak di rakitan 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

Jenis yang Ditentukan Pengguna CLR