Vor nicht allzu langer Zeit … habe ich im SQL Server 2008 ein Aggregat-Funktion vermisst, die es bereits in MYSQL gibt: GROUP_CONCAT. Damit ist es bei einer Gruppierung möglich, Werte zu verketten.

Es begab ich folgendes vereinfachtes Problem: Es war Tabelle mit Salaten, die Personen zum Grillen mitbringen. Das Ziel sollte ein Liste gruppiert nach dem Salat sein. Zusätzlich zur Zählung (COUNT) sollten noch alle Personen aufgelistet werden, die den entsprechenden Salat mitbringen (CONCAT).

Google hatte für dieses Problem verschiedene Lösungen. Die einen waren zu kompliziertes SQL (un-Wartbarkeit!) und funktionierten nur für endlich Anzahl von Zeilen bzw. nicht wiederverwendbare benutzerdefinierte Funktion. Die eleganteste aber aufwändigste Lösung im Bezug auf das Deployment schien das Entwickeln einer eigenen Aggregat-Funktion “Concat”. An Ende war die eigene Aggregatfunktion das einfachste und das Deployment überschaubar (Adminsicher ).

Vorgehen

1. Erstellen der Assembly
2. SQL Server anpassen
3. Aggregatfunktion verwenden

.NET Assembly erstellen

Visual Studio 2010 unterstützt bei der Erstellung von SQL Server Assemblys über den “Neues Projekt” Wizard.

Anschließen kann über “Hinzufügen” eine neues Aggregat angelegt werden.

Das Klasse der Aggregatfunktion in der .NET Assembly ist selbsterklärend. Die Methoden werden mittels Reflection aufgerufen, daher ist keine separate Schnittstelle zu implementieren. Die Schnittstelle “IBinarySerialize” dient dazu, das in dem Klassenattribut deklarierte “Format.UserDefined” (MSDN) zu implementieren.

using System;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

namespace SwkAggregates
{
    [Serializable]
    [SqlUserDefinedAggregate(
        Format.UserDefined, //use clr serialization to serialize the intermediate result
        IsInvariantToNulls = true, //optimizer property
        IsInvariantToDuplicates = false, //optimizer property
        IsInvariantToOrder = false, //optimizer property
        MaxByteSize = 8000) //maximum size in bytes of persisted value
    ]
    public class Concat : IBinarySerialize
    {
        /// <summary>
        /// The variable that holds the intermediate result of the concatenation
        /// </summary>
        private StringBuilder _intermediateResult;

        /// <summary>
        /// Initialize the internal data structures
        /// </summary>
        public void Init()
        {
            this._intermediateResult = new StringBuilder();
        }

        /// <summary>
        /// Accumulate the next value, not if the value is null
        /// </summary>
        /// <param name="value"></param>
        public void Accumulate(SqlString value)
        {
            if (value.IsNull)
            {
                return;
            }

            this._intermediateResult.Append(value.Value).Append(", ");
        }

        /// <summary>
        /// Merge the partially computed aggregate with this aggregate.
        /// </summary>
        /// <param name="other"></param>
        public void Merge(Concat other)
        {
            this._intermediateResult.Append(other._intermediateResult);
        }

        /// <summary>
        /// Called at the end of aggregation, to return the results of the aggregation.
        /// </summary>
        /// <returns></returns>
        public SqlString Terminate()
        {
            string output = string.Empty;
            //delete the trailing comma, if any
            if (this._intermediateResult != null
                && this._intermediateResult.Length > 1)
            {
                output = this._intermediateResult.ToString(0, this._intermediateResult.Length – 2);
            }

            return new SqlString(output);
        }

        public void Read(BinaryReader r)
        {
            _intermediateResult = new StringBuilder(r.ReadString());
        }

        public void Write(BinaryWriter w)
        {
            w.Write(this._intermediateResult.ToString());
        }
    }
}

Hinweis: Die Assembly muss keinen Strong Name besitzen. Es lassen sich auch eine nicht-signierte Assemblys in den SQL Server hochladen. Ob das Bestandteil der neuen Sicherheitspolitik von Microsoft ist wage ich zu bezweifeln.

Also bleibt nur noch das Kompilieren der DLL und anschließend das Integrieren in den SQL Server.

SQL Server Integration

Die SQL Server Integration besteht aus zwei Schritten.

1. Hochladen der Assembly in den SQL Server
2. Erstellen einer Aggregatfunktion mit einem “API-Call” auf die Klasse in der DLL

Das Script enthält auch die optionalen Schritte, wenn das Ausführen von Assemblys in der Datenbank noch nicht aktiviert wurde. Das Script ist durch die Kommentierung (hoffentlich) selbsterklärend.

– Add assembly to database
CREATE ASSEMBLY SwkUtilities FROM ‘d:\SwkAggregates.dll’
WITH PERMISSION_SET=SAFE;
GO

– Create aggregate function which calls the concrete "Concat" class
CREATE AGGREGATE Concat(@input nvarchar(4000))
RETURNS nvarchar(4000)
EXTERNAL NAME [SwkUtilities].[SwkAggregates.Concat];
GO

– Enable cls code execution. It is disabled by default
sp_configure ‘clr enabled’, 1;

GO

– Deploy the "clr enabled" setting in sql server
reconfigure
GO

Zu bemerken ist, dass die Assembly im SQL Server einen symbolischen Namen “SwkUtilities” erhält. Der API-Call erfolgt über diesen symbolischen Namen. Außerdem wird die DLL wird in den SQL Server hochgeladen und ist damit Bestandteil der Datenbank. Das hat natürlich Auswirkungen auf Backup und Restore Szenarien. Bei einem Backup werden die Assemblies mitgesichert d.h. es entfällt ein FileSystem Backup. Im Gegenzug werden mit einem Restore der Datenbank auch die Assemblies wiederhergestellt.

SQL Verwendung

Ab jetzt heißt es viel Spaß mit der neuen Aggregat Funktion und der geplanten Grillfete .

SELECT Salat, Count(*), dbo.Concat(Person)
FORM Grillfest
GROUP BY Salat