Advantages of Using Managed Code to Create Database Objects
In addition to the Transact-SQL programming language, you can use .NET Framework languages to create database objects and retrieve and update data for SQL Server databases. In Visual Basic or Visual C# projects, you can create stored procedures, triggers, aggregates, user-defined functions, and user-defined types. Database objects that are written in managed code are called SQL Server Common Language Run-time objects or SQL CLR objects.
Note
By default, the common language runtime (CLR) integration feature is turned off in SQL Server. To use SQL Server project items, you must enable CLR integration. To do this, use the clr enabled option of the sp_configure stored procedure. For more information, see the clr enabled Option topic on the Microsoft Web site.
The following list is a summary of the advantages to using a .NET Framework language instead of Transact-SQL:
Enhanced programming model .NET Framework languages offer constructs and capabilities previously unavailable to SQL Server developers.
Enhanced Safety and Security Managed code runs in a common language runtime environment hosted by the database engine. This enables .NET Framework database objects to be safer and more secure than the extended stored procedures available in earlier versions of SQL Server. When you use database objects created in Visual Basic or Visual C# the code-access security of those languages is combined with the user-based permissions in SQL Server.
User-Defined Types and Aggregates User-defined types and user-defined aggregates are two managed database objects which expand the storage and querying capabilities of SQL Server.
Common Development Environment Database development is integrated into the Microsoft Visual Studio development environment. Developers use the same tools for developing and debugging database objects and scripts as they use to write middle-tier or client-tier .NET Framework components and services.
Better Performance Some functions, such as those that run mathematical operations on every row in a database, might perform better when they are compiled assemblies that are built from a Visual Basic or Visual C# project than when they are written in Transact-SQL, which is interpreted code. For example, performance improvements will be achieved for functions, especially those that perform integer operations. However, stored procedures that only access data will not perform better.
Language Richness Visual Basic or Visual C# provide capabilities that are not available in Transact-SQL, such as arrays, sophisticated exception handling, and reusability of code.
Reusability of Code A library of managed assemblies can be created and distributed more easily than a Transact-SQL script can be distributed.
Extensibility Using Visual Basic or Visual C# you can create two database objects that cannot be created by using Transact-SQL: aggregates and user-defined types.
Leverage Existing Skills You can use and enhance your skills in the languages and development environment in which you are already experienced to create database objects.
Richer developer experience When you develop database objects by using the SQL Server project template, you have complete integration with the project system. This includes building, debugging, and deployment to multiple servers.
Stability and reliability The database objects that you create using Visual Basic or Visual C# are more secure, stable, robust, and reliable than extended stored procedures, which might produce memory leaks or other problems that reduce the performance and reliability of the server. When you run stored procedures that were created in Visual Basic or Visual C# memory management and threading are not performed by the stored procedure and are therefore handled more robustly.
Stored Procedures and Triggers
Stored procedures are a precompiled collection of programming statements that perform operations in the database, and are stored under a name and processed as a unit. For more information about stored procedures, see the SQL Server documentation.
A trigger is a special kind of stored procedure that is activated when you modify data in a specified table using one or more of the data modification operations: UPDATE, INSERT, or DELETE. You can also create database-level triggers that fire in response to Data Definition Language (DDL) statements. These triggers can be used to perform administrative tasks in the database such as auditing and regulating database operations. For more information about triggers, see the SQL Server documentation.
Development of stored procedures and triggers is enhanced by the language richness of Visual Basic and Visual C#, especially when you are implementing the complex procedural logic that is required to enforce business rules. In addition, the .NET Framework contains many libraries. Of particular interest are those that enable you to manage many aspects of cryptography, the extensive math libraries, and the external access to Web services, files, and business-to-business communication systems.
Functions
Functions operate on one or more values to return either a scalar value or a table. For more information about the types of functions that the Transact-SQL programming language provides, see the SQL Server documentation.
Like stored procedures and triggers, the development of functions is enhanced by the language richness of Visual Basic and Visual C#, and by access to the many libraries that the .NET Framework contains.
Aggregates
Aggregate functions are used to summarize all the data in a table. They perform a calculation on a set of values and return a single scalar value. For more information about the aggregate functions that are provided by the Transact-SQL programming language, see the SQL Server documentation.
To supplement those aggregate functions, you can define new aggregates that perform more complex arithmetic functions. For example, you can perform a calculation on the data in many rows and return one value or create a concatenated string.
User-Defined Types
Types specify the nature of data. For information about the set of system data types supplied with SQL Server, see the SQL Server documentation.
Using Visual Basic and Visual C#, you can define new types so that you are no longer limited to the predefined types that are supplied with SQL Server. You can create simple types such as postal codes or more complex types for parsing the information returned from a credit card transaction. Also, when you are working with user-defined types, data can be interpreted and manipulated on both the SQL client and SQL Server; by using ADO.NET, you can download an assembly that contains a type definition from the SQL Server and use it to examine data on the SQL client.
See Also
Tasks
How to: Create and Run a SQL Server Stored Procedure by using Common Language Run-time Integration
How to: Create and Run a SQL Server Trigger by using Common Language Run-time Integration
How to: Create and Run a SQL Server Aggregate by using Common Language Run-time Integration
How to: Create and Run a SQL Server User-Defined Type by using Common Language Run-time Integration
Walkthrough: Creating a Stored Procedure in Managed Code
How to: Debug a SQL Server CLR Integration Stored Procedure
Reference
Attributes for SQL Server CLR Integration Database Projects and Database Objects