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;
