在 SQL Server 中註冊使用者定義型別

CREATE ASSEMBLY 語法會在要使用 UDT 的資料庫中註冊組件。一旦註冊此組件，它就不具有相依性。

不允許在給定資料庫內建立同一組件的多個版本。但是，根據給定資料庫中的文化特性建立相同組件的多個版本是可行的。SQL Server 會根據 SQL Server 執行個體內所註冊的不同名稱來區分某個組件的多個文化特性版本。如需詳細資訊，請參閱 .NET Framework SDK 中的＜建立和使用強式名稱的組件＞。

當使用 SAFE 或 EXTERNAL_ACCESS 權限集合執行 CREATE ASSEMBLY 時，系統會檢查組件，以確定它可進行驗證且型別是安全的。如果您省略指定使用權限集合，則會假設為 SAFE。系統不會檢查使用 UNSAFE 權限集合的程式碼。如需有關組件權限集合的詳細資訊，請參閱＜設計組件＞。

範例

USE AdventureWorks2008R2;
CREATE ASSEMBLY Point
FROM '\\ShareName\Projects\Point\bin\Point.dll' 
WITH PERMISSION_SET = SAFE;

USE AdventureWorks2008R2;
CREATE ASSEMBLY Point
FROM 0xfeac4 … 21ac78;

一旦將組件載入資料庫，即可使用 Transact-SQL CREATE TYPE 陳述式建立此型別。這樣會將該型別加入該資料庫的可用型別清單。該型別具有資料庫範圍，而且只能在建立它的資料庫中使用。如果資料庫中已存在 UDT，則 CREATE TYPE 陳述式會失敗並出現錯誤。

如需詳細資訊，請參閱＜CREATE TYPE (Transact-SQL)＞。

範例

CREATE TYPE dbo.Point 
EXTERNAL NAME Point.[Point];

下列 Transact-SQL 必須以下列順序執行。首先，必須卸除參考 Point UDT 的資料表，然後是型別，最後是組件。

DROP TABLE dbo.Points;
DROP TYPE dbo.Point;
DROP ASSEMBLY Point;

如果有相依物件存在 (如具有 UDT 資料行定義的資料表)，則 DROP TYPE 陳述式會失敗。如果資料庫中有使用 WITH SCHEMABINDING 子句所建立的函數、預存程序或觸發程序，而且這些常式使用使用者定義型別的變數或參數，該陳述式也會失敗。您必須先卸除所有相依物件，然後再執行 DROP TYPE 陳述式。

下列 Transact-SQL 查詢會在AdventureWorks2008R2 資料庫中找到使用 UDT 的所有資料行和參數。

USE Adventureworks2008R2;
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;

當您變更並重新編譯 UDT 組件中的原始程式碼之後，可以使用 ALTER ASSEMBLY 陳述式。它會將此 .dll 檔案複製到伺服器，並重新繫結至新的組件。如需完整的語法，請參閱＜ALTER ASSEMBLY (Transact-SQL)＞。

下列 Transact-SQL ALTER ASSEMBLY 陳述式會從指定的磁碟位置重新載入 Point.dll 組件。

ALTER ASSEMBLY Point
FROM '\\Projects\Point\bin\Point.dll';

ALTER ASSEMBLY 語法中的 ADD FILE 子句不存在於 CREATE ASSEMBLY 中。您可以使用它來加入原始程式碼，或與組件相關聯的任何其他檔案。這些檔案會從原始位置複製，並儲存在資料庫的系統資料表中。如此可在您需要重新建立或記載 UDT 的目前版本時，確保您一定有原始程式碼或其他檔案可用。

下列 Transact-SQL ALTER ASSEMBLY 陳述式會對 Point UDT 加入 Point.cs 類別原始程式碼。這會複製 Point.cs 檔案中包含的文字，並將它儲存在名為 PointSource 的資料庫中。

ALTER ASSEMBLY Point
ADD FILE FROM '\\Projects\Point\Point.cs' AS PointSource;

組件資訊儲存在安裝該組件之資料庫的 sys.assembly_files 資料表中。sys.assembly_files 資料表包含下列資料行。

• assembly_id
  為組件定義的識別項。此號碼會指派給與同一組件相關的所有物件。

• name
  物件的名稱。

• file_id
  識別每一個物件的數字，其中會將 1 的值指派給與給定 assembly_id 相關聯的第一個物件。如果有多個物件與相同 assembly_id 相關聯，則每一個後續 file_id 值都會遞增 1。

• content
  組件或檔案的十六進位表示法。

您可以使用 CAST 或 CONVERT 函式，將 content 資料行的內容轉換為可讀取的文字。下列查詢會將 Point.cs 檔案的內容轉換為可讀取的文字，並在 WHERE 子句中使用此名稱，以將結果集限制為單一資料列。

SELECT CAST(content AS varchar(8000)) 
  FROM sys.assembly_files 
  WHERE name='PointSource';

如果您將結果複製並貼至文字編輯器中，將會看到原始檔案中的分行符號及空格均已保留。

AdventureWorks2008R2 範例資料庫中的 Currency UDT 提供值得採用的實用範例，可以將 UDT 及其關聯的函數進行結構化處理。Currency UDT 可依據特定文化特性的貨幣系統來處理貨幣，並允許儲存不同貨幣類型，如美元、歐元等。UDT 類別會將文化特性名稱顯示為字串，而將貨幣金額顯示為 decimal 資料類型。所有必要的序列化方法都包含在定義類別的組件中。實作從一種文化特性至另一種文化特性之貨幣轉換的函式，會實作成名為 ConvertCurrency 的外部函式，且此函式位於另一組件中。ConvertCurrency 函式藉由從 AdventureWorks2008R2 資料庫中的資料表擷取轉換比率來完成其作業。如果轉換比率的來源應該變更，或應該對現有程式碼進行其他變更，則可輕鬆修改組件，而不會影響 Currency UDT。

藉由安裝 Common Language Runtime (CLR) 範例，可找到 Currency UDT 及 ConvertCurrency 函數的程式碼清單。如需詳細資訊，請參閱＜安裝 SQL Server 範例和範例資料庫的考量＞。

根據定義，UDT 屬於單一資料庫的範圍。因此，在一個資料庫內定義的 UDT 無法用於其他資料庫中的資料行定義。為了在多個資料庫中使用 UDT，您必須在每個資料庫內的相同組件上執行 CREATE ASSEMBLY 及 CREATE TYPE 陳述式。具有相同名稱、強式名稱、文化特性、版本、使用權限集合及二進位內容的組件，會視為相同組件。

一旦在兩個資料庫中登錄 UDT 並可存取 UDT，您便可從某個資料庫轉換 UDT 值，以用於另一資料庫。在下列情況下，可以跨資料庫使用相同的 UDT：

• 呼叫不同資料庫中定義的預存程序。

• 查詢不同資料庫中定義的資料表。

• 從某個資料庫資料表 UDT 資料行選取 UDT 資料，並將其插入具有相同 UDT 資料行的第二個資料庫中。

在這些情況下，伺服器所需的任何轉換都會自動發生。您將無法使用 Transact-SQL CAST 或 CONVERT 函數明確地執行轉換。

請注意，當 SQL Server Database Engine 在 tempdb 系統資料庫中建立工作資料表時，您不需要採取任何動作來使用 UDT。這包括處理包含 UDT 以及以透明方式使用 tempdb 的資料指標、資料表變數及使用者定義的資料表值函式。不過，如果您在 tempdb 中明確建立可定義 UDT 資料行的暫存資料表，則必須在 tempdb 中使用與使用者資料庫相同的方式註冊 UDT。