1. Why can't there be UDA's in T-SQL? Granted, it's easy to write it in CLR, but sometimes it would be simpler (and more appropriate) to write it in SQL. It also took me a while to figure out that indeed there is such limitation...
2. UDA's must be serializable. Why? I don't know yet (still need to figure that one out), although I have some ideas, but anyway it's besides the point - it's a must and I assume there are good reasons for that. The problem is that whenever you're doing something slightly more complicated than just an average or Product, you need to accumulate all the values until you get to Terminate() (e.g. a variation on STDEV). This means that this list you've just accumulated could grow significantly. Now to the pitfall - when you use user-defined serialization (which you would have to in this case), you must specific the maximum size that the UDA structure could grow to. This maximum size is limited to 8000 bytes (*sounds familiar...). So in my case, I'm using a UDA over double values, and thus I'm limited to aggregating a little below 1000 records. IMHO this reduces the practical usage of UDA's to about 50%...
3. I tried to write a UDA for decimal data. No matter what I did, it constantly produced a function defined to return decimal(18,0). In other words - no decimal numbers to the right of the dot. In the end I didn't have the time to find out the KB article talking about it, but I suppose there is - I pretty much tried everything. In my particular case using double values was an acceptable compromise - it won't always be that way...
1 comment:
I have been banging my head against the desk because I am trying to use the decimal type. This is only one of the problems I have run into with it. My aggregate code works great if I reference the dll and call it from a C# app, but it fails miserably in SQL Server 2005.
Post a Comment