Trabalhando com SQL CLR: User Defined Aggregate Functions

São raros os momentos que não se pode contar com funções de agregação nativas do SQL Server (COUNT, AVG, MAX, SUM, MIN…), mas como nem sempre a ferramenta esta adequada a todos os cenários, pode ser necessário criar funções de agregação customizadas.

Para conhecermos um pouco da estrutura das funções de agregação, temos que esquecer o que aprendemos até agora sobre funções desenvolvidas em CLR, pois funções de agregação vão um pouco além.

A estrutura das funções de agregação é criada a partir de uma struct (uma estrutura, ou tipo por valor) e manipulada por meio de quatro métodos (init, accumulate, merge, terminate).

O primeiro método (Init) é responsável pela inicialização da função de agregação, isso quer dizer que se você precisa realizar alguma operação antes de serem calculados (ou acumulados) os valores, é esse o momento.

O método Accumulate é responsável por receber os valores a serem calculados. Neste método você definirá os parâmetros a serem agrupados.

O método Merge é utilizado quando temos situações de paralelismos, isso quer dizer que quando utilizar duas ou mais threads para execução de sua consulta, este método será utilizado para combinar as várias execuções deste método para formar um único resultado. Um exemplo comum é quando para calcular o valor mínimo de um campo cada thread da consulta retornou um valor, no caso 10 e 9, e neste ponto você poderá informar qual deles será utilizado para resultado final, no caso 9.

Por fim o método Terminate, que é responsável por retornar o valor final à consulta.

Abaixo, a implementação de uma função “COUNT”:

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

[Serializable]
[SqlUserDefinedAggregate(Format.Native, Name = "UDF_Terceiro")]
public struct Terceiro
{
    public Int64 Count { get; set; }

    public void Init() { }

    public void Accumulate(SqlString Value)
    {
        Count++;
    }

    public void Merge(Terceiro Group)
    {
        Count += Group.Count;
    }

    public SqlInt64 Terminate()
    {
        return Count;
    }
}

E a execução:

SELECT dbo.UDF_Terceiro(Nome) AS [Valor] FROM dbo.Clientes

Visto que estão trabalhando com estruturas serializáveis, não será possível utilizar de forma nativa propriedades do tipo por referência (ex.: textos, listas, vetores). Mas para “burlar” esta limitação, é possível customizar a serialização desta estrutura.

Já que serialização se trata de um assunto um pouco complexo para muitos desenvolvedores, vou somente mostrar um exemplo de uma função de agregação (concatenação) de texto.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Runtime.InteropServices;
using System.Collections.Generic;

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined
    , Name = "UDF_ConCat"
    , IsInvariantToNulls = true
    , IsInvariantToDuplicates = false
    , IsInvariantToOrder = false
    , MaxByteSize = 8000)]
[StructLayout(LayoutKind.Sequential)]
public struct Terceiro : IBinarySerialize
{
    public string Text { get; set; }

    public void Init() { }

    public void Accumulate(SqlString Value)
    {
        if (Text == null)
            Text = Value.ToString();
        else
            Text += ", " + Value.ToString();
    }

    public void Merge(Terceiro Group)
    {
        if (Text == null)
            Text = Group.Text;
        else if (Group.Text != null)
            Text += ", " + Group.Text;
    }

    public SqlString Terminate()
    {
        return Text;
    }

    #region IBinarySerialize Members

    public void Read(System.IO.BinaryReader stream)
    {
        Text = stream.ReadString();
    }

    public void Write(System.IO.BinaryWriter stream)
    {
        stream.Write(Text);
    }

    #endregion
}

E a execução:

SELECT dbo.UDF_ConCat(Nome) FROM dbo.Clientes

Neste momento chegamos à metade do caminho de uma pequena introdução às possibilidades do SQL CLR, infelizmente estarei sendo um pouco breve neste artigo e nos próximos, visto que se aprofundarmos muito em cada uma das possibilidades, terei que focar mais no desenvolvimento com .NET Framework do que em SQL Server.

Espero que tenham gostado, nos próximos artigos veremos gatilhos (triggers) e tipos (user defined types).

Anúncios

3 pensamentos sobre “Trabalhando com SQL CLR: User Defined Aggregate Functions

  1. Pingback: Trabalhando com SQL CLR – Resumo « SQL From Hell.com

  2. Pingback: Trabalhando com SQL CLR – Resumo « SQL From Hell.com

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s