CLR 使用者定義型別CLR User-Defined Types

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Server 可讓您能夠建立針對.NET Framework common language runtime (CLR) 建立的組件來進行程式設計的資料庫物件。gives you the ability to create database objects that are programmed against an assembly created in the .NET Framework common language runtime (CLR). 資料庫物件可充分運用 CLR 所提供的豐富程式設計模型,包括觸發程序、預存程序、函數、彙總函式和類型等。Database objects that can take advantage of the rich programming model provided by the CLR include triggers, stored procedures, functions, aggregate functions, and types.


SQL ServerSQL Server 中,執行 CLR 程式碼的功能預設會設定為 OFF。The ability to execute CLR code is set to OFF by default in SQL ServerSQL Server. 來啟用 CLR,請使用sp_configure系統預存程序。The CLR can be enabled by using the sp_configure system stored procedure.

開頭SQL Server 2005 (9.x)SQL Server 2005 (9.x),您可以使用使用者定義型別 (Udt) 來擴充純量類型系統的伺服器,好讓 CLR 物件儲存在SQL ServerSQL Server資料庫。Beginning with SQL Server 2005 (9.x)SQL Server 2005 (9.x), you can use user-defined types (UDTs) to extend the scalar type system of the server, enabling storage of CLR objects in a SQL ServerSQL Server database. UDT 可以包含多個元素並可以具有行為,使其有別於由單一 SQL ServerSQL Server 系統資料類型組成的傳統別名資料類型。UDTs can contain multiple elements and can have behaviors, differentiating them from the traditional alias data types which consist of a single SQL ServerSQL Server system data type.

因為系統將 UDT 當做一個整體來進行存取,所以它們使用的複雜資料類型可能會對效能產生負面影響。Because UDTs are accessed by the system as a whole, their use for complex data types may negatively impact performance. 通常使用傳統資料列及資料表可以對複雜資料進行最佳模型化。Complex data is generally best modeled using traditional rows and tables. SQL ServerSQL Server 中的 UDT 適用於以下項目:UDTs in SQL ServerSQL Server are well suited to the following:

  • 日期、時間、貨幣及擴充的數值類型Date, time, currency, and extended numeric types

  • Geospatial 應用程式Geospatial applications

  • 編碼或加密的資料Encoded or encrypted data

SQL ServerSQL Server 中開發 UDT 的過程包含下列步驟:The process of developing UDTs in SQL ServerSQL Server consists of the following steps:

  1. 程式碼,並建置定義 UDT 的組件。Code and build the assembly that defines the UDT. UDT 是使用會產生可驗證程式碼之 .NET Framework Common Language Runtime (CLR) 支援的任何語言所定義。UDTs are defined using any of the languages supported by the.NET Framework common language runtime (CLR) that produce verifiable code. 這包括 Visual C# 和 Visual Basic .NET。This includes Visual C# and Visual Basic .NET. 資料會公開為 .NET Framework 類別或結構的欄位及屬性,並且其行為是由類別或結構的方法所定義。The data is exposed as fields and properties of a .NET Framework class or structure, and behaviors are defined by methods of the class or structure.

  2. 註冊組件。Register the assembly. 可以部署 Udt,透過 Visual Studio 使用者介面,在資料庫專案,或使用Transact-SQLTransact-SQLCREATE ASSEMBLY 陳述式,它將會複製到資料庫中包含的類別或結構之組件。UDTs can be deployed through the Visual Studio user interface in a database project, or by using the Transact-SQLTransact-SQL CREATE ASSEMBLY statement, which copies the assembly containing the class or structure into a database.

  3. SQL Server 中建立 UDT。Create the UDT in SQL Server. 一旦將組件載入主機資料庫,您會使用Transact-SQLTransact-SQLCREATE TYPE 陳述式來建立 UDT 並公開為 UDT 的成員類別或結構的成員。Once an assembly is loaded into a host database, you use the Transact-SQLTransact-SQL CREATE TYPE statement to create a UDT and expose the members of the class or structure as members of the UDT. UDT 僅存在於單一資料庫的內容中,並且一旦註冊後,就不再與建立它們的外部檔案具有相依性。UDTs exist only in the context of a single database, and, once registered, have no dependencies on the external files from which they were created.


    SQL Server 2005 (9.x)SQL Server 2005 (9.x) 之前,不支援從 .NET Framework 組件建立的 UDT。Before SQL Server 2005 (9.x)SQL Server 2005 (9.x), UDTs created from .NET Framework assemblies were not supported. 不過,您仍然可以使用SQL ServerSQL Server使用的別名資料型別sp_addtypeHowever, you can still use SQL ServerSQL Server alias data types by using sp_addtype. CREATE TYPE 語法可用來建立原生 SQL ServerSQL Server 使用者定義資料類型及 UDT。The CREATE TYPE syntax can be used for creating both native SQL ServerSQL Server user-defined data types and UDTs.

  4. 建立資料表、 變數或參數使用 UDT開頭SQL Server 2005 (9.x)SQL Server 2005 (9.x),使用者定義型別可用來當做資料表的資料行定義中的變數Transact-SQLTransact-SQL批次,或做為引數的Transact-SQLTransact-SQL函式或預存程序。Create tables, variables, or parameters using the UDT Beginning with SQL Server 2005 (9.x)SQL Server 2005 (9.x), a user-defined type can be used as the column definition of a table, as a variable in a Transact-SQLTransact-SQL batch, or as an argument of a Transact-SQLTransact-SQL function or stored procedure.

本節內容In This Section

建立使用者定義型別Creating a User-Defined Type
描述如何建立 UDT。Describes how to create UDTs.

在 SQL Server 中註冊使用者定義型別Registering User-Defined Types in SQL Server
描述如何在 SQL ServerSQL Server 中註冊及管理 UDT。Describes how to register and manage UDTs in SQL ServerSQL Server.

在 SQL Server 中使用使用者定義型別Working with User-Defined Types in SQL Server
描述如何使用 UDT 建立查詢。Describes how to create queries using UDTs.

存取 ADO.NET 中的使用者定義型別Accessing User-Defined Types in ADO.NET
描述如何在 ADO.NET 中,使用 .NET Framework Data Provider for SQL ServerSQL Server 來處理 UDT。Describes how to work with UDTs using the .NET Framework Data Provider for SQL ServerSQL Server in ADO.NET.