SQL Server Performance Forum – Threads Archive

.Net not fully supported in SQL 2005

All, I thought you might be interested in the following. I’m working on a major project using SQL 2005 using the CLR hosted environment. The issue in a nutshell is that SQL Server 2005 only supports a subset of the .Net Framework (one of the biggest selling points of SQL 2005) . The development community are not being told of these short falls in SQL and are inadvertently using features of the system which still contain bugs – which then MS will refuse to support. Actually, it’s worse. They won;t support your SQL server until the offending c# code accessing the untested classes are removed. MS as it stands don’t publicly tell you which classes are not suppotred, only the assemblies that contain the classes. In these assemblies there are supported and unsupported classes – so as a developer you have no way of telling if your SQL server is supported if you use CLR – until it goes wrong!
This is how MS sell SQL Server… www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx Note: You cannot take full advantage of the MS .Net Framework Class – only a subset is supported!. CLR/.NET Framework Integration
With the release of SQL Server 2005, 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 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. Additionally, 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 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 Server 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.
By using languages such as Visual Basic .NET and C#, you can capitalize on CLR integration to write code that has more complex logic and is more suited for computation tasks. Additionally, Visual Basic .NET and C# offer object-oriented capabilities such as encapsulation, inheritance, and polymorphism. You can easily organize related code into classes and namespaces, which means that you can more easily organize and maintain your code investments when you are working with large amounts of code. The ability to logically and physically organize code into assemblies and namespaces is a huge benefit that allows 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 have full access to thousands of prebuilt 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.
A SQL engineer admitted to me that MS never got the whole Framework working, so technical support provided the following support notes on the 27th July.
http://support.microsoft.com/?id=922672 The above link outlines the .dll (assemblies) that are supported. What it does not tell you is the classes are not supported – these are needed to develop code, eg: using System; -> Supported
using System.Data; -> Supported
using System.Data.SqlClient; -> Not supported
using System.Data.SqlTypes; -> Not supported
using Microsoft.SqlServer.Server; -> Not supported
using System.Diagnostics; -> Not supported
using System.IO; -> Not supported
using System.Transactions; -> Not supported
using System.Collections; -> Supported
Another issue is that MS are educating the developer community using MSDN articles that use unsupported functions: http://msdn2.microsoft.com/en-us/library/ms254508.aspx
Has anyone else come across this issue?

---

has it occurred to you that the whole of .Net Framework is huge do you not think that very useful applications can be built with what is supported now?
why should we wait for features that are not critical? also consider that certain things probably should not be pushed into the core engine this is not a matter of playing games
people run important applications on SQL,
only critical items should be done in the engine, silly tricks should be pushed off to an app server

---

Thank goodness the System namespace is supported!<br /><br />So it’s ok to call Environment.Exit() at the end of my CLR sprocs just to make sure all resources are cleaned up, right?<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br /><hr noshade size="1">SqlSpec – a fast, cheap, and comprehensive data dictionary generator <br />for SQL Server 2000 and 2005 and Analysis Server 2005 – www.elsasoft.org <br />

---

I think the issue the OP is point out is that MS won’t tell you which classes are/are not supported. They sell MSSQL 2005 stating it is fully integrated with the .net CLR, but there are parts which are not supported which you basically have to go and find out on your own. The .net framework is huge, but that’s not my problem. If MS can’t or won’t support the whole thing then they need to release some detailed documentation about what is supported now, what’s planned to be supported in the future and what will never be supported.

---

]]>