Getting The Most Out of SQL Server 2005 UDTs and UDAs

  • Solomon Rutzky

    SSCoach

    Points: 15964

    Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/srutzky/3208.asp

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Adam Machanic

    SSCoach

    Points: 15259

    "Unfortunately, there is not much we can do at this point since the data has already been compressed as much as possible and no matter what we do we have that 8000 byte limit."

    I hope you'll excuse my blatant self-promotion, but you can go much further than this for UDAs -- and I explain how to do it in the SQLCLR chapter of "Expert SQL Server 2005 Development" ... (which you can find here: http://www.amazon.com/dp/159059729X )

    And while I'd appreciate it if anyone who is really serious about this topic buy the book, I won't make you spend your money just for this solution. So if you're interested (reply here if so), I will post a followup in a few days. In the meantime, I think this is a good puzzle, so try to solve it yourself first. Here is a hint:

    Think about how to leverage the fact that you have a runtime environment at your disposal. Focus on garbage collection. By using this compression method, each uncompressed value will have to be garbage collected twice: once for the value passed in from SQL Server, and again for the value decompressed/deserialized from the compressed/serialized stream. Is it possible to set this up so that each value will be garbage collected only once?

    --
    Adam Machanic
    whoisactive

  • Indraneel Phadke

    Grasshopper

    Points: 23

    Hi Adam...

    Hope you post this soon!

    Regards,

    Neel:)

  • Sam Stange

    SSC Enthusiast

    Points: 123

    For those stuck at 8000 bytes setting for MaxByteSize, SQL Server 2008 has bumped this amount to 2 GB! Just set MaxByteSize = -1. Only challenge to this, is you have to deploy the CLR manually, but it's worth it.

  • Solomon Rutzky

    SSCoach

    Points: 15964

    Sam Stange (6/29/2009)


    For those stuck at 8000 bytes setting for MaxByteSize, SQL Server 2008 has bumped this amount to 2 GB! Just set MaxByteSize = -1.

    Hi Sam. Yes, in SQL Server 2008 you can use -1 to go beyond the previous 8000 byte limit (and I have been meaning to update this article to include that info). However, I still think it might be best to consider also using the compression method in conjunction with the -1 option so that one does not use up too much memory since that is still a valuable resource.

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Sam Stange

    SSC Enthusiast

    Points: 123

    I agree. Thanks again for your post, implementing this aggregate function will save myself more work than you could possibly know.

  • Solomon Rutzky

    SSCoach

    Points: 15964

    Glad you found it helpful. 🙂

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • massoud-1045248

    SSC-Addicted

    Points: 435

    Thanks for the article but it seems that the link is not working.

    CreateAssemblyAndAggregate.sql

    Also when I try to create the procedure below I get an error. I named the dll MedianSQLSERVER

    Error Message:

    Msg 6505, Level 16, State 1, Procedure Agg_Median, Line 1

    Could not find Type 'Agg_Median' in assembly 'MedianSQLServer'.

    CREATE PROCEDURE Agg_Median

    AS

    EXTERNAL NAME [MedianSqlServer].[Agg_Median].[Accumulate]

    --Assembly.type.

    Thank you

  • Solomon Rutzky

    SSCoach

    Points: 15964

    massoud-1045248 (3/14/2011)


    Thanks for the article but it seems that the link is not working.

    CreateAssemblyAndAggregate.sql

    Also when I try to create the procedure below I get an error. I named the dll MedianSQLSERVER

    Error Message:

    Msg 6505, Level 16, State 1, Procedure Agg_Median, Line 1

    Could not find Type 'Agg_Median' in assembly 'MedianSQLServer'.

    CREATE PROCEDURE Agg_Median

    AS

    EXTERNAL NAME [MedianSqlServer].[Agg_Median].[Accumulate]

    --Assembly.type.

    Hello. The link should be fixed now, thanks to the editor, Steve Jones.

    Also, you are using a 3-part name to point to the Aggregate, pointing specifically to the Accumulate method. You should not point to the method within the Aggregate, just to the Aggregate itself via a 2-part name. You are also missing the input parameter and the return type. And you call CREATE AGGREGATE instead of CREATE PROCEDURE. I assume it would be something like:

    CREATE AGGREGATE [dbo].[Agg_Median] (@value [float])

    RETURNS [float]

    EXTERNAL NAME [MedianSqlServer].[Agg_Median]

    Take care,

    Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • massoud-1045248

    SSC-Addicted

    Points: 435

    Very much appreciated!

    This article has been very useful!

    Thanks again

  • massoud-1045248

    SSC-Addicted

    Points: 435

    Hi guys, I am using the CLR and for some reason I get this error :

    A .NET Framework error occurred during execution of user-defined routine or aggregate "Agg_Median":

    System.InvalidCastException: Specified cast is not valid.

    System.InvalidCastException:

    at Agg_Median.Write(BinaryWriter ToBeSerialized)

    Any idea on how to fix this?

    Thanks

  • massoud-1045248

    SSC-Addicted

    Points: 435

    Hi everyone, not sure if anyone is still looking at this thread but recently I have been running into the following error. I inspected the data but I am not noticing anything unusual.

    Any input would be greatly appreciated.

    Thanks

    Msg 6522, Level 16, State 2, Line 2

    A .NET Framework error occurred during execution of user-defined routine or aggregate "Agg_Median":

    System.InvalidOperationException: Failed to compare two elements in the array. ---> System.ArgumentException: At least one object must implement IComparable.

    System.ArgumentException:

    at System.Collections.Comparer.Compare(Object a, Object b)

    at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)

    System.InvalidOperationException:

    at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)

    at System.Array.SorterObjectArray.QuickSort(Int32 left, Int32 right)

    at System.Array.Sort(Array keys, Array items, Int32 index, Int32 length, IComparer comparer)

    at System.Collections.ArrayList.Sort(Int32 index, Int32 count, IComparer comparer)

    at System.Collections.ArrayList.Sort()

    at Agg_Median.Write(BinaryWriter ToBeSerialized)

  • Solomon Rutzky

    SSCoach

    Points: 15964

    massoud-1045248 (1/29/2015)


    Hi everyone, not sure if anyone is still looking at this thread but recently I have been running into the following error. I inspected the data but I am not noticing anything unusual.

    Any input would be greatly appreciated.

    Thanks

    Msg 6522, Level 16, State 2, Line 2

    A .NET Framework error occurred during execution of user-defined routine or aggregate "Agg_Median":

    System.InvalidOperationException: Failed to compare two elements in the array. ---> System.ArgumentException: At least one object must implement IComparable.

    System.ArgumentException:

    at System.Collections.Comparer.Compare(Object a, Object b)

    at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)

    System.InvalidOperationException:

    at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)

    at System.Array.SorterObjectArray.QuickSort(Int32 left, Int32 right)

    at System.Array.Sort(Array keys, Array items, Int32 index, Int32 length, IComparer comparer)

    at System.Collections.ArrayList.Sort(Int32 index, Int32 count, IComparer comparer)

    at System.Collections.ArrayList.Sort()

    at Agg_Median.Write(BinaryWriter ToBeSerialized)

    Hello Massoud. Are you using the code from the article exactly as it is shown or did you make any modifications? I have no idea how it could be possible to get the error you posted above, nor the error you mentioned in the prior post (sorry, I never got a notification for that posting). Do you always or only sometimes get this error? The only problem I see, looking over the code again, is that it doesn't handle groups that have only NULL values, but that causes a different error: "Index was out of range". What version of SQL Server are you using? Can you post some sample data? How did you get past the error from last time regarding "Specified cast is not valid"?

    Take care,

    Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • massoud-1045248

    SSC-Addicted

    Points: 435

    Hello Solomon, thanks for your reply. It's been a while but I use this code to compute a median for a list that varies every day.

    so basically date(T), median(list(T)).

    meaning : 01/02/2015 Median (list), 01/03/2015 Median (list) ....

    I think my work around was to exclude some dates and move on when it wasn't working. Now when I go and compute without the time series and look at a specific date , it works .

    For example when I have the time series with the specific condition

    "valuationdate > getdate() - 235" in the where statement which gives me data up to 06/09/2014 but I get the error message :

    Msg 6522, Level 16, State 2, Line 1

    A .NET Framework error occurred during execution of user-defined routine or aggregate "Agg_Median":

    System.InvalidOperationException: Failed to compare two elements in the array. ---> System.ArgumentException: At least one object must implement IComparable.

    System.ArgumentException:

    at System.Collections.Comparer.Compare(Object a, Object b)

    at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)

    System.InvalidOperationException:

    at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)

    at System.Array.SorterObjectArray.QuickSort(Int32 left, Int32 right)

    at System.Array.Sort(Array keys, Array items, Int32 index, Int32 length, IComparer comparer)

    at System.Collections.ArrayList.Sort(Int32 index, Int32 count, IComparer comparer)

    at System.Collections.ArrayList.Sort()

    at Agg_Median.Write(BinaryWriter ToBeSerialized)

    but when I exclude via inserting the following in the where statement

    and valuationdate not in ('06/06/2014','06/07/2014','06/08/2014') it still doesn't work if I increase to

    "valuationdate > getdate() - 236"

    And when I look at 06/08/2014 specifically, it returns no error message.

    Happy to share more data if needed.

    Thanks again.

  • Solomon Rutzky

    SSCoach

    Points: 15964

    massoud-1045248 (1/29/2015)


    Hello Solomon, thanks for your reply. It's been a while but I use this code to compute a median for a list that varies every day.

    so basically date(T), median(list(T)).

    meaning : 01/02/2015 Median (list), 01/03/2015 Median (list) ....

    I think my work around was to exclude some dates and move on when it wasn't working. Now when I go and compute without the time series and look at a specific date , it works .

    Hi again. So, if I am understanding you correctly, you are doing a MEDIAN of a list of DATETIME (or just DATE) instead of FLOAT? I had never considered such a thing. Interesting. But I can see one immediate problem: what to do in the case of an even list, since DATE / DATETIME values cannot be averaged. I suppose you could just pick the higher or lower value in each case, or in SQL Server 2008 and newer, pass in a second parameter to determine which side to err on.

    If you want to continue debugging this, since it really isn't the code from the article, please post a message to the CLR Integration and Programming forum and we can take it from there.

    Take care,

    Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply