Advantages of Using Managed Code to Create Database Objects

Article
11/16/2012

You can use .NET Framework languages in addition to the Transact-SQL programming language to create database objects and retrieve and update data for Microsoft SQL Server 2005 databases. In Visual Basic, Visual C#, or Visual C++ projects, you can create stored procedures, triggers, aggregates, user-defined functions, and user-defined types.

Note

By default, the common language runtime (CLR) integration feature is off in Microsoft SQL Server. It must be enabled in order to use SQL Server project items. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure. For more information, see Enabling CLR Integration.

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 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.
User-Defined Types and Aggregates User-defined types and user-defined aggregates are two new 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, Visual C#, 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, Visual C#, and 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, Visual C#, 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, Visual C#, 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, Visual C#, or Visual C++, memory management and threading are not performed by the stored procedure and are therefore handled more robustly.
Security When you use database objects created in Visual Basic, Visual C#, or Visual C++, the code-access security of those languages is combined with the user-based permissions in SQL Server.

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. 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, Visual C#, 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, Visual C#, 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, Visual C#, 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.

Share via

Advantages of Using Managed Code to Create Database Objects

Stored Procedures and Triggers

Functions

Aggregates

User-Defined Types

See Also

Tasks

Concepts

Reference

Other Resources

Additional resources