An Overview of SQL Server 2005 - I

An Overview of SQL Server 2005 - I

for the Database Developer

.NET Framework Integration
With the release of Microsoft SQL Server 2005 Beta 2, database programmers can now take full advantage of the Microsoft® .NET Framework class library and modern programming languages to implement functionality within the server. Using common language runtime (CLR) integration, you can code your stored procedures, functions, and triggers in the .NET Framework language of your choice. Microsoft® Visual Basic® .NET and the C# programming language both offer object-oriented constructs, structured exception handling, arrays, namespaces, and classes. In addition, the .NET Framework provides thousands of classes and methods that have extensive built-in capabilities that you can easily use on the server-side. Many tasks that were awkward or difficult to perform in Transact-SQL can be better accomplished by using managed code; additionally, two new types of database objects—aggregates and user-defined types—are available. You can now better use the knowledge and skills that you have already acquired to write in-process code. In short, SQL Server 2005 Beta 2 enables you to extend the database server to more easily perform appropriate computation and operations on the back end.

This integration between SQL Server and the CLR provides several major benefits:

· Enhanced programming model: Programming languages that are compatible with the .NET Framework are in many respects richer than Transact-SQL, offering constructs and capabilities that were previously not available to SQL developers.

· Enhanced safety and security: Managed code runs in a CLR environment, hosted by the database engine. This allows .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: Two new database objects that expand the storage and querying capabilities of SQL Server are enabled by hosting the CLR.

· Common development environment: Database development is integrated into the Microsoft® Visual Studio® 2005 development environment. You can use the same tools for developing and debugging database objects and scripts that you use to write middle-tier or client-tier .NET Framework components and services.

· Performance and scalability: Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.



You can capitalize on the CLR integration to write code that has more complex logic and is more suited for computation tasks, by using languages such as Visual Basic .NET and C#. In addition, Visual Basic .NET and C# offer object-oriented capabilities such as encapsulation, inheritance, and polymorphism. You can now easily organize related code into classes and namespaces, which means you can more easily organize and maintain your code investments when you are working with large amounts of code. This ability to logically and physically organize code into assemblies and namespaces is a huge benefit, and will allow you to better find and relate different pieces of code in a large database implementation.

Managed code is more efficient than Transact-SQL at processing numbers and managing complicated execution logic, and provides extensive support for string handling, regular expressions, and so on. Also, with the functionality that is available in the .NET Framework class library, you now have full access to thousands of pre-built classes and routines that you can access easily from any stored procedure, trigger, or user-defined function. Everything from improved string handling functions, math functions, date operations, access to system resources, advanced encryption algorithms, file access, image processing, and XML data manipulation is easily accessible from managed stored procedures, functions, triggers, and aggregates.

One of the major benefits of managed code is type safety. Before managed code is executed, the CLR performs several checks through a process known as "verification" to ensure that the code is safe to run. For example, the code is checked to ensure that memory is not read that has not been written to.