Degree of C# support in SQL CLR user-defined function?

Question

Hi,

This may be naive but I cannot get any confirmation of this: When I write a SQL function via the SQLCLR and as a C# SQL Server Project, can the SQL function include any method/class/namespace in the .NET BCL? Are there any restrictions on what the function can do? I have full control of the SQL Server and its hosting OS, so I can amend security settings at that level. I am more interested in the language support for C# in a SQLCLR user-defined function (SQL project).

My final deployment of code will be on SQL Server 2005 and 2008 R2 (Enterprise Edition). As 2008 R2 would be a development on 2005 in terms of features and support, 2005 is my common denominator, hence I am interested in answers on 2005.

Thanks

Answer 1

The .NET version depends on the SQL Server version:

• 2005 = .NET v2.0
• 2008/R2 = .NET v3.5

It's not full access to .NET - restrictions are based around the external_access parameter. See this link for more details: http://msdn.microsoft.com/en-us/library/ms345101.aspx

Answer 2

It can not. You can't use classes that have a [HostProtection] attribute that marks the class as unsafe. Details are in this MSDN Library article, it includes lists of classes that are off limits.

The classic example of such a class is TimeZoneInfo, it loads an unmanaged DLL that won't be unloaded if there's a query abort.

[HostProtection(SecurityAction.LinkDemand, MayLeakOnAbort=true)]
public sealed class TimeZoneInfo : IEquatable<TimeZoneInfo>, ISerializable, IDeserializationCallback
{
  // etc...
}