SQL Server CLR 整合簡介Introduction to SQL Server CLR Integration

做為 Microsoft .NET Framework 核心的 Common Language Runtime (CLR),提供了所有 .NET Framework 程式碼的執行環境。The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. CLR 中執行的程式碼稱為 Managed 程式碼。Code that runs within the CLR is referred to as managed code. CLR 提供程式執行所需的各種功能及服務,包括 just-in-time (JIT) 編譯、配置及管理記憶體、強制使用型別安全性、例外處理、執行緒管理及安全性。The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

利用 Microsoft SQL Server 中裝載的 CLR (稱為 CLR 整合),您能夠以 Managed 程式碼撰寫預存程序、觸發程序、使用者定義函式、使用者定義型別及使用者定義彙總。With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. 因為 Managed 程式碼在執行前會編譯成原生程式碼,所以在部分案例中可大幅提升效能。Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

Managed 程式碼使用程式碼存取安全性 (CAS)、程式碼連結及應用程式定義域,以防止組件執行某些作業。Managed code uses Code Access Security (CAS), code links, and application domains to prevent assemblies from performing certain operations. SQL Server 使用 CAS 協助保護 Managed 程式碼,並防止損害作業系統或資料庫伺服器。SQL Server uses CAS to help secure the managed code and prevent compromise of the operating system or database server.

本節的目的是僅提供以 SQL Server CLR 整合進行程式設計快速入門所需的足夠資訊,並未包含廣泛資訊。This section is meant to provide only enough information to get started programming with SQL Server CLR integration, and is not meant to be comprehensive. 如需詳細資訊,請參閱您所使用之 SQL Server 版本的《SQL Server 線上叢書》版本。For more detailed information, see the version of SQL Server Books Online for the version of SQL Server you are using.

SQL Server 線上叢書SQL Server Books Online

啟用 CLR 整合Enabling CLR Integration

預設會在 Microsoft SQL Server 中停用 Common Language Runtime (CLR) 整合功能,且為了使用 CLR 整合所實作的物件,必須啟用這個功能。The common language runtime (CLR) integration feature is off by default in Microsoft SQL Server, and must be enabled in order to use objects that are implemented using CLR integration. 若要啟用使用 Transact-SQL 進行 CLR 整合,請使用 clr enabled 預存程序的 sp_configure 選項,如下所示:To enable CLR integration using Transact-SQL, use the clr enabled option of the sp_configure stored procedure as shown:

sp_configure 'clr enabled', 1  
GO  
RECONFIGURE  
GO  

您可以將 clr enabled 選項設定為 0,藉以停用 CLR 整合。You can disable CLR integration by setting the clr enabled option to 0. 停用 CLR 整合時,SQL Server 會停止執行所有 CLR 常式,並卸載所有應用程式定義域。When you disable CLR integration, SQL Server stops executing all CLR routines and unloads all application domains.

如需詳細資訊,請參閱您所使用之 SQL Server 版本的《SQL Server 線上叢書》版本。For more detailed information, see the version of SQL Server Books Online for the version of SQL Server you are using.

SQL Server 線上叢書SQL Server Books Online

部署 CLR 組件Deploying a CLR Assembly

一旦 CLR 方法已經在測試伺服器上測試並驗證,您就可以使用部署指令碼,將它們散發至實際執行伺服器 (Production Server)。Once the CLR methods have been tested and verified on the test server, they can be distributed to production servers using a deployment script. 您可以手動產生部署指令碼,也可以使用 SQL Server Management Studio 來產生部署指令碼。The deployment script can be generated manually, or by using SQL Server Management Studio. 如需詳細資訊,請參閱您所使用之 SQL Server 版本的《SQL Server 線上叢書》版本。For more detailed information, see the version of SQL Server Books Online for the version of SQL Server you are using.

SQL Server 線上叢書SQL Server Books Online

  1. 部署 CLR 資料庫物件Deploying CLR Database Objects

CLR 整合安全性CLR Integration Security

Microsoft SQL Server 與 Microsoft .NET Framework Common Language Runtime (CLR) 的整合安全性模型,可管理及保護在 SQL Server 內執行之不同類型 CLR 及非 CLR 物件的存取權。The security model of the Microsoft SQL Server integration with the Microsoft .NET Framework common language runtime (CLR) manages and secures access between different types of CLR and non-CLR objects running within SQL Server. 這些物件可由 Transact-SQL 陳述式或在伺服器中執行的其他 CLR 物件呼叫。These objects may be called by a Transact-SQL statement or another CLR object running in the server.

如需詳細資訊,請參閱您所使用之 SQL Server 版本的《SQL Server 線上叢書》版本。For more detailed information, see the version of SQL Server Books Online for the version of SQL Server you are using.

SQL Server 線上叢書SQL Server Books Online

偵錯 CLR 組件Debugging a CLR Assembly

Microsoft SQL Server 支援在資料庫中偵錯 Transact-SQL 及 Common Language Runtime (CLR) 物件。Microsoft SQL Server provides support for debugging Transact-SQL and common language runtime (CLR) objects in the database. 偵錯可跨語言運作:使用者可以從 Transact-SQL 無接縫地進入 CLR 物件,反之亦然。Debugging works across languages: users can step seamlessly into CLR objects from Transact-SQL, and vice versa.

如需詳細資訊,請參閱您所使用之 SQL Server 版本的《SQL Server 線上叢書》版本。For more detailed information, see the version of SQL Server Books Online for the version of SQL Server you are using.

SQL Server 線上叢書SQL Server Books Online

另請參閱See Also

在 Managed 程式碼中建立 SQL Server 2005 物件Creating SQL Server 2005 Objects In Managed Code
程式碼存取安全性和 ADO.NETCode Access Security and ADO.NET
ADO.NET Managed 提供者和 DataSet 開發人員中心ADO.NET Managed Providers and DataSet Developer Center