F# in SQL Server

SQL Server allows for routines written in .NET. There are plenty of examples everywhere. However, virtually all are written in C# or Visual Basic. I decided to give F# a try. I created a simple aggregate in F#, that provides a concatenation of input strings.

namespace _St

    [<SqlUserDefinedAggregateAttribute(Format.UserDefined, MaxByteSize = -1)>]
    type ConcatenationAggregate() =
       
       [<DefaultValue>] val mutable private agg : StringBuilder

       member private this.Append( s : string) =
           ignore <| this.agg.Append( if( this.agg.Length > 0) then ";" else "" ).Append( s )

       member public this.Init() = 
           this.agg <- StringBuilder()
       member public this.Accumulate(s : SqlString) =
           this.Append(s.Value)
       member public this.Merge(t2:ConcatenationAggregate) =
           this.Append(t2.agg.ToString())
       member public this.Terminate() =
           SqlString(this.agg.ToString())

       interface IBinarySerialize with
           member this.Read(r : BinaryReader) =
              this.agg <- StringBuilder( r.ReadString() )
           member this.Write(w : BinaryWriter) =
              w.Write( this.agg.ToString())

However, CREATE ASSEMBLY failed, because the referenced assembly fsharp.core was not present in the current database. So I tried importing the assembly to the database:

CREATE ASSEMBLY fsharp_core FROM 'c:\Program Files\FSharp-2.0.0.0\bin\FSharp.Core.dll'

but that failed too:

CREATE ASSEMBLY failed because type 'System.LazyFailure' in safe assembly 'FSharp.Core' has a static field 'undefined'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.

The solution was to import fsharp.core as UNSAFE assembly. Indeed, there was some more security stuff required for that. Luckily, there was a guidance on MSDN blogs.

CREATE ASYMMETRIC KEY CLR_SP_Key
FROM EXECUTABLE FILE = 'c:\Program Files\FSharp-2.0.0.0\bin\FSharp.Core.dll'
GO
CREATE LOGIN CLR_SP_Login
FROM ASYMMETRIC KEY CLR_SP_Key
GO
GRANT UNSAFE ASSEMBLY TO CLR_SP_Login
GO
USE MyTestDatabase;
GO
CREATE USER CLR_SP_Login FOR LOGIN CLR_SP_Login
GO
CREATE ASSEMBLY fsharp_core 
FROM 'c:\Program Files\FSharp-2.0.0.0\bin\FSharp.Core.dll'
WITH PERMISSION_SET = UNSAFE

Then it was rather straightforward.

CREATE ASSEMBLY ConcatenationAggregate
FROM 'C:\Test\ConcatenationAggregate.dll'
GO
CREATE AGGREGATE Test.CatAgg (@s nvarchar(max))
RETURNS nvarchar(max)
EXTERNAL NAME ConcatenationAggregate.[_St.ConcatenationAggregate]
GO
SELECT Test.CatAgg(EmailAddress) FROM Test.Person;