UDA生成错误,缓冲区大小不足

对于SQL 2005,可以通过使用分隔符将一个参数转换为多个参数来解决8000字节的限制。我不需要自己去调查细节,但你可以在这里找到答案: http://www.mssqltips.com/tip.asp?tip=2043

对于SQL 2008,需要将MaxByteSize传递为-1。如果您尝试传递一个大于8000的数字,SQL将不允许您创建聚合,并抱怨有8000字节的限制。如果您传入-1,它似乎可以绕过这个问题,让您创建聚合(我也用>8000字节测试过)。

错误是:

尺寸(100000) “Class.Concatenate”不在 有效范围。大小必须为-1或 数字在1到8000之间。

下面是VB.NET在SQL 2008中支持8000字节的工作类定义。

<Serializable(), SqlUserDefinedAggregate(Format.UserDefined, 
IsInvariantToNulls:=True, 
IsInvariantToDuplicates:=False, 
IsInvariantToOrder:=False, MaxByteSize:=-1)>
<System.Runtime.InteropServices.ComVisible(False)> _
Public Class Concatenate Implements IBinarySerialize
End Class

下面的代码演示如何计算SQLAggregate中一组十进制数的媒体。它解决了实现数据字典的大小参数限制问题。这个想法取自专家SQL Express 2005。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using SafeDictionary;


[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Format.UserDefined, MaxByteSize=16)]

public struct CMedian2 : IBinarySerialize
{
    readonly static SafeDictionary<Guid , List<String>> theLists = new SafeDictionary<Guid , List<String>>();

    private List<String> theStrings;
    //Make sure to use SqlChars if you use
    //VS deployment!
    public SqlString Terminate()
    {

        List<Decimal> ld = new List<Decimal>();
        foreach(String s in theStrings){
           ld.Add(Convert.ToDecimal(s));
        }

        Decimal median;
        Decimal tmp;
        int halfIndex;
        int numberCount;


        ld.Sort();
        Decimal[] allvalues = ld.ToArray();

        numberCount = allvalues.Count();

        if ((numberCount % 2) == 0)
            {
                halfIndex = (numberCount) / 2;
                tmp = Decimal.Add(allvalues[halfIndex-1], allvalues[halfIndex]);
                median = Decimal.Divide(tmp,2);
            }
        else
            {
                halfIndex = (numberCount + 1) / 2;
                median =  allvalues[halfIndex - 1];
                tmp = 1;
            }

        return new SqlString(Convert.ToString(median));
    }

    public void Init()
        {
        theStrings = new List<String>();
        }

    public void Accumulate(SqlString Value)
        {
        if (!(Value.IsNull))
            theStrings.Add(Value.Value);
        }

    public void Merge(CMedian2 Group)
        {
        foreach (String theString in Group.theStrings)
            this.theStrings.Add(theString);
        }

    public void Write(System.IO.BinaryWriter w)
    {
    Guid g = Guid.NewGuid();
        try
        {
            //Add the local collection to the static dictionary
            theLists.Add(g, this.theStrings);
            //Persist the GUID
            w.Write(g.ToByteArray());
        }
        catch
        {
            //Try to clean up in case of exception
            if (theLists.ContainsKey(g))
            theLists.Remove(g);
        }
    }
    public void Read(System.IO.BinaryReader r)
    {
        //Get the GUID from the stream
        Guid g = new Guid(r.ReadBytes(16));
        try
        {
            //Grab the collection of strings
            this.theStrings = theLists[g];
        }
        finally
        {
            //Clean up
            theLists.Remove(g);
        }
    }
}

您还需要像Expert 2005那样实现字典:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;

namespace SafeDictionary 
{
public class SafeDictionary<K, V>
{
    private readonly Dictionary<K, V> dict = new Dictionary<K,V>();
    private readonly ReaderWriterLock theLock = new ReaderWriterLock();

    public void Add(K key, V value)
    {
        theLock.AcquireWriterLock(2000);
        try
            {
                dict.Add(key, value);
            }
        finally
            {
                theLock.ReleaseLock();
            }
   }

    public V this[K key]
    {
        get
            {
                theLock.AcquireReaderLock(2000);
                try
                    {
                        return (this.dict[key]);
                    }
                finally
                    {
                        theLock.ReleaseLock();
                    }
            }
        set
            {
                theLock.AcquireWriterLock(2000);
                try
                    {
                        dict[key] = value;
                    }
                finally
                    {
                        theLock.ReleaseLock();
                    }
           }
    }

    public bool Remove(K key)
    {
        theLock.AcquireWriterLock(2000);
        try
            {
                return (dict.Remove(key));
            }
        finally
            {
                theLock.ReleaseLock();
            }
    }

    public bool ContainsKey(K key)
    {
        theLock.AcquireReaderLock(2000);
        try
            {
                return (dict.ContainsKey(key));
            }
        finally
            {
                theLock.ReleaseLock();
            }
    }
}
}

字典必须部署在具有不安全代码授权的单独程序集中。其思想是通过保留内存中的数据结构字典来避免将所有数字序列化。我推荐专家SQL 2005章节:

第6章SQLCLR:架构和 设计考虑。

顺便说一下,这个解决方案对我不起作用。从Decimal到String和viceversa的转换太多,使得在处理数百万行时速度变慢,无论如何,我喜欢尝试它。对于其他用途来说,这是一个很好的模式。