Implement User-defined Functions in SQL Server 2005 with Managed Code
One of the excellent features of SQL Server 2005 is its integration with the .NET common language runtime (CLR), which extends SQL Server's capability in several important ways. The integration enables developers to create database objects such as stored procedures, user-defined functions, and triggers by using modern object-oriented languages such as VB.NET and C#. This article explains how to create user-defined functions using C#. It then tests the user-defined function using the SQL Server integration features of Visual Studio 2005. Finally, it creates a Windows Forms client application to test the user-defined function.
Although Transact-SQL (T-SQL), the existing data-access and manipulation language, is well suited for set-oriented, data-access operations, it also has limitations. Microsoft designed it more than a decade ago, and it is a procedural language rather than an object-oriented language. The integration of the .NET CLR with SQL Server enables the development of stored procedures, user-defined functions, triggers, aggregates, and user-defined types using any of the .NET languages. The SQL Server engine hosts the CLR in process. All managed code that executes in the server runs within the confines of the CLR. The managed code accesses the database using ADO.NET and the new SQL Server .NET Data Provider.
User-defined functions that return scalar types must return a .NET data type that can be implicitly converted to a SQL Server data type. Scalar functions written with the .NET Framework can significantly outperform T-SQL in certain scenarios because, unlike T-SQL, .NET functions are created using compiled code. User-defined functions can also return table types, in which case the function must return a result set.
Before learning to create user-defined functions, read the following section to gain an understanding of the advantages that CLR integration with SQL Server offers.
Advantages of CLR Integration
In previous versions of SQL Server, database programmers were limited to using T-SQL when writing code on the server side. With CLR integration, database developers can now perform tasks that were impossible or difficult to achieve with T-SQL alone. Both Visual Basic .NET and C# offer full support for arrays, structured exception handling, and collections. With these languages, developers can leverage CLR integration to write code that has more complex logic and is more suited for computation tasks. Visual Basic .NET and C# offer object-oriented capabilities such as encapsulation, inheritance, and polymorphism. Related code can now be easily organized into classes and namespaces.
Managed code is better suited than T-SQL for number crunching and complicated execution logic, and it features extensive support for many complex tasks, including string handling and regular expressions. With the functionality found in the .NET Framework Base Class Library (BCL), database developers now have access to thousands of pre-built classes and routines, which can be accessed easily from any stored procedure, trigger, or user-defined function. The BCL includes classes that provide functionality for improved string functioning, advanced math operations, file access, cryptography, and more. While many of these classes are available for use from within SQL CLR code, those that are not appropriate for server-side use (for example, windowing classes) are not available.
One of the benefits of managed code is type safety. Before managed code is executed, the CLR performs several checks to ensure that the code is safe to run. This process is known as verification. For example, it checks the code to ensure that memory that has not be been written to is not read. The CLR also prevents buffer overflows. By default, both Visual Basic .NET and C# always produce safe code. However, C# programmers have the option of using the unsafe keyword to produce unsafe code that, for example, directly accesses memory.
What Is an Assembly?
An assembly is the unit of deployment for .NET objects with the database. To create objects, you must write managed code that implements the functionalities of the user-defined function. Once the code is written, you compile it into a .NET assembly and then deploy it onto SQL Server. You can accomplish this in one of the following two ways:
- The most common and simple way is by using Visual Studio 2005 to create a new SQL Server Project. After creating the project, you then can very easily create the user-defined function and deploy it onto SQL Server with the click of a button, thereby eliminating the manual registration step.
- Create a Visual Studio Class library project and compile the class library into an assembly. Once the assembly is created, you then can register the assembly with SQL Server and then associate the definition of the user-defined function with a specific method in the assembly.
The second approach is a manual step and the first approach is an automated process that eliminates most of the steps. The following sections look at each of these methods.