CLR-User Definded Aggregatiion

UDF_Concat.cs

uda.sql

김종열

MS SQL Server MVP

엔트리브 소프트

user Defined aggregate(UDA)는 Mssql 2005의 새롭게 등장한 겁니다.
mssql 2000이나 그 이전 버젼에서 aggregate를 하기 위해서는 Looping이나 T-sql문으로만 했을겁니다.
특히나 문자열관련이면 더더욱 그러했을 겁니다.

요즘들어 커뮤너티에서 문자열을 합하는 과정을 많이 보게 되는데요.
이번에 만들고자 하는 것이 바로 그 문자열 관련 concat의 과정입니다. 먼저 소개하는 것은 t-sql문입니다. 
 

IF OBJECT_ID('TEST') IS NOT NULL

       DROP TABLE TEST

GO

 

CREATE TABLE TEST

(

   ID   INT       NOT NULL,

   NM    NVARCHAR(10) NOT NULL

)

GO

 

INSERT TEST VALUES(1, '홍길동')

INSERT TEST VALUES(2, '김종영')

INSERT TEST VALUES(3, '최석중')

INSERT TEST VALUES(2, '임꺽정')

INSERT TEST VALUES(3, '송헉')

INSERT TEST VALUES(3, '한대송')

GO

 

 

IF OBJECT_ID('UDF_CONCAT_SQL') IS NOT NULL

       DROP FUNCTION UDF_CONCAT_SQL

GO

 

 

CREATE FUNCTION UDF_CONCAT_SQL (

       @BOOKID INT

)

RETURNS VARCHAR(8000)

AS

BEGIN

       DECLARE @STRTEMP VARCHAR(8000)

 

       SELECT @STRTEMP = ISNULL(@STRTEMP, '') + NM + ','

       FROM

             TEST

       WHERE

             ID = @BOOKID

      

       SET @STRTEMP = LEFT(@STRTEMP, LEN(@STRTEMP)-1)

       RETURN @STRTEMP

END

GO

 

SELECT id, DBO.UDF_CONCAT_SQL(id)

FROM test

GROUP BY id

GO

물론 이렇게 만들게 됐을 때 문제가 될 만한 건 실행계획일것이고 코드의 재활용은 거의 0일 겁니다.
그래서 UDA로 가는 건 어떨까? 하는 생각을 해봅니다.  (다음의 c#소스는 msdn에서 제공하는 uda입니다.)

using System;

using System.Data;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

using System.IO;

using System.Text;

 

[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 UDF_Concat : IBinarySerialize

{

 

    private StringBuilder strTemp;

 

    public void Init()

    {

        strTemp = new StringBuilder();

    }

 

    public void Accumulate(SqlString value)

    {

        if (value.IsNull)

        {

            return;

        }

 

        strTemp.Append(value.Value).Append(',');

    }

 

    public void Merge(UDF_Concat other)

    {

        this.strTemp.Append(other.strTemp);

    }

 

    public SqlString Terminate()

    {

        string output = string.Empty;

 

        if (strTemp != null && strTemp.Length > 0)

        {

            output = strTemp.ToString(0, strTemp.Length - 1);

        }

 

        return new SqlString(output);

    }

 

    public void Read(BinaryReader r)

    {

        strTemp = new StringBuilder(r.ReadString());

    }

 

    public void Write(BinaryWriter w)

    {

        w.Write(strTemp.ToString());

    }

}

여기까지가 C#코드입니다.
컴파일 이후 쿼리를 실행하면 다음과 같습니다.

IF OBJECT_ID('TEST') IS NOT NULL

       DROP TABLE TEST

GO

 

CREATE TABLE TEST

(

   ID   INT       NOT NULL,

   NM    NVARCHAR(10) NOT NULL

)

GO

 

INSERT TEST VALUES(1, '홍길동')

INSERT TEST VALUES(2, '김종열')

INSERT TEST VALUES(3, '최석준')

INSERT TEST VALUES(2, '임꺽정')

INSERT TEST VALUES(3, '송혁')

INSERT TEST VALUES(3, '한대성')

GO

 

 

SELECT ID, DBO.UDF_CONCAT(NM)

FROM TEST

GROUP BY ID

쿼리플랜을 보게 되면 T-sql문과 사뭇 달라진 과정을 확인 할 수 있을 것이며
또한 재사용(예를 들면- 테이블명이 틀려지거나 조합하고자 하는 칼럼명이 틀려질 경우,
그룹 by의 기준이 되는 칼럼의 변경등)의 측면에서도 T-sql문과는 비교가 되지 않을겁니다.




참고)  uda를 사용하기 위해서 몇가지 필수 사항
1. 반드시 SqlUserDefinedAggregate 를 선언
2. aggregation method중 init, accumulate, merge, terminate를 가지고 있어야 함

각각의 method의 기능
1. init() : T-sql에서 uda로 들어오는 진입 포인트의 역할을 함
2. Accumulate() : 각각의 value를 검사하여 aggregated을 계산하는 역할
3. Merge() : 멀티 프로세서가 결과를 조합하거나 더할때 병렬로 처리할수 있도록 함
4. Terminate() : aggregate를 마친 UDA가 value를 리턴하는 역할

첨부 : udf_Concat.cs
        uda.sql